Clever Geek Handbook

SQL 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 db table names. I want to print the table name and column names in each table. Can I do it by pasting instruction.

This code runs on 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?

+3


source to share


2 answers


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
+6


source


Sqlserver 2005 introduced the INFORMATION_SCHEMA views first .

These views are mainly created to retrieve metadata like table name, column name, column type, etc. about tables, columns, views, domains, etc.

Each database contains these views. If you want to check what's going on behind the scenes, you can test the logic of these views by running only sp_helptext . how



sp_helptext INFORMATION_SCHEMA.COLUMNS

Using the views above you can get the desired result. Please check below request.

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'
+2


source






More articles:


All Articles