News

Copyright © 2008-2019 Paula DiTallo

Tag Cloud



How do I list every table and every column in the table in SQL Server?

 

The sys.tables and sys.columns objects will return this information for you. The following SQL statement will bring bring back all the colunns in all the tables ordered by the table name then the column name.

 

SELECT

tbl.name AS table_name,

SCHEMA_NAME

(schema_id) AS schema_name,

col

.name AS column_name

FROM

sys.tables AS tbl

INNER

JOIN sys.columns col ON tbl.OBJECT_ID = col.OBJECT_ID

ORDER

BY schema_name, table_name;

 

 

 

 

 

 

Sunday, October 10, 2010 10:32 PM

Feedback

# re: How do I list every table and every column in the table in SQL Server?

You can also use the information_Schema and not have to worry about the join and the details in the sys tables:

select table_schema as schema_name, table_name, column_name, data_type from information_schema.columns order by 1,2, 3 10/11/2010 1:47 PM | Nick

# ANSI equivalent

SELECT TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_SCHEMA, TABLE_NAME
10/11/2010 2:19 PM | Eddie

Post A Comment
Title:
Name:
Email:
Comment:
Verification: