CODEWITHSUNDEEP
sqlsql-server
EglCode
EglCode

Reputation: 149

SQL statement to print table names and their column names

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?

Upvotes: 8

Views: 27817

Answers (3)

linkonabe
linkonabe

Reputation: 846

With Mssql 2008 version try the following query. This itemise column name and the tables they belong to.

SELECT  c.name  AS 'ColumnName', t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name = c.name
ORDER BY    TableName, ColumnName;

Upvotes: 1

Mukund
Mukund

Reputation: 1689

In Sqlserver 2005 INFORMATION_SCHEMA views was introduced first.

These views are mainly created to get the metadata like table name, column name, datatype of columns etc. about tables, columns, views, domains etc.

Each and every database contains these views. If you want to check what's going on behind the scene you can check the logic of these views by doing just sp_helptext. Like

sp_helptext INFORMATION_SCHEMA.COLUMNS

By using above views you can get your desired result. Please check below query.

SELECT T.TABLE_NAME,C.COLUMN_NAME,C.DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS C
    INNER JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME
        AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
    WHERE T.TABLE_TYPE = 'BASE TABLE'

Upvotes: 2

Dave C
Dave C

Reputation: 7402

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

Upvotes: 8

Related Questions