Which columns are in what tables

thing (someone@microsoft.com) writes:
> I have been asked to provide a list of all columns in all tables in a
> database.
> I have the output from "select * from sys.tables" and "select * from
> sys.columns", but how do I know which columns are in what tables?

SELECT s.name AS [Schema], o.name AS [Table], c.name AS [Column]
FROM sys.schemas s
JOIN sys.objects o ON s.schema_id = o.schema_id
JOIN sys.columns c ON o.object_id = c.object_id
WHERE o.type = 'U'
ORDER BY s.name, o.name, c.column_id



--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Which columns are in what tables 

Thank-you. You've pointed my in the right direction
"Plamen Ratchev" wrote in message

> This should do it:
>
> SELECT TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME
> FROM INFORMATION_SCHEMA.COLUMNS;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
 >> Stay informed about: Which columns are in what tables 

Thank-you. You've pointed my in the right direction
"Erland Sommarskog" wrote in message

> thing (someone@microsoft.com) writes:
>> I have been asked to provide a list of all columns in all tables in a
>> database.
>> I have the output from "select * from sys.tables" and "select * from
>> sys.columns", but how do I know which columns are in what tables?
>
> SELECT s.name AS [Schema], o.name AS [Table], c.name AS [Column]
> FROM sys.schemas s
> JOIN sys.objects o ON s.schema_id = o.schema_id
> JOIN sys.columns c ON o.object_id = c.object_id
> WHERE o.type = 'U'
> ORDER BY s.name, o.name, c.column_id
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
 >> Stay informed about: Which columns are in what tables