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?

---

这应该做到这一点：

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

---

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'

链接地址: http://www.djcxy.com/p/93842.html

上一篇: 仅从SELECT语句返回列名称

下一篇: SQL语句来打印表名和它们的列名