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?

`SHOW TABLES` and `SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]` — User, Oct 24 '14 at 17:53
+1 why do you downvote this guy, he got interesting question, and showed what he tried ! — meda, Oct 24 '14 at 18:08
possible duplicate of [Get columns of a table SQL SERVER](http://stackoverflow.com/questions/1054984/get-columns-of-a-table-sql-server) — Tab Alleman, Oct 24 '14 at 18:12

score 8 · Accepted Answer · edited Oct 24 '14 at 18:01

8

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

edited Oct 24 '14 at 18:01

meda

45,103
14
92
122

answered Oct 24 '14 at 17:56

Dave C

7,272
1
19
30

Mukund · Answer 2 · 2014-10-25T09:56:54.053

2

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'

edited Oct 25 '14 at 09:56

answered Oct 24 '14 at 19:10

Mukund

1,679
1
11
20

@rene didn't get you. – Mukund Oct 25 '14 at 05:33
hey @rene thanks for guiding. :) updated answer. I hope it will help someone else in future. – Mukund Oct 25 '14 at 09:57

linkonabe · Answer 3 · 2020-07-21T23:36:42.110

1

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;

edited Jul 21 '20 at 23:36

answered Jul 06 '18 at 17:08

linkonabe

661
7
23

SQL statement to print table names and their column names

3 Answers3