SQL statement to print table names and their column names

Question

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' 
ORDER BY TABLE_NAME ASC

I am using this code to print the table names of a db. What I want to do is print the table name and the col names in each table. Can I do this by nesting a statement.

This code is being run on a SQL Server in a query window.

I tried this

SELECT COL_NAME 
FROM 
    (SELECT TABLE_NAME 
     FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_TYPE = 'BASE TABLE' 
     ORDER BY TABLE_NAME ASC)

Any ideas?

Answer 1

This should do it:

SELECT C.TABLE_NAME, C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES T 
              WHERE T.TABLE_TYPE='BASE TABLE' AND C.TABLE_NAME=T.TABLE_NAME)
ORDER BY C.TABLE_NAME, C.COLUMN_NAME