Which columns are in what tables

Prev: Set up trace
Next: Send Mail from SQL server 2000

From: thing on 8 Feb 2010 22:55

---

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?

Thanks

Bob

From: Plamen Ratchev on 8 Feb 2010 23:02

---

This should do it:

SELECT TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS;

--
Plamen Ratchev
http://www.SQLStudio.com

From: Erland Sommarskog on 9 Feb 2010 17:48

---

thing (someone(a)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(a)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

From: thing on 16 Feb 2010 21:02

---

Thank-you. You've pointed my in the right direction
"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:joSdnRtdldfRQ-3WnZ2dnUVZ_s5i4p2d(a)speakeasy.net...
> This should do it:
>
> SELECT TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME
> FROM INFORMATION_SCHEMA.COLUMNS;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com

From: thing on 16 Feb 2010 21:03

---

Thank-you. You've pointed my in the right direction
"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D1AF22DB491Yazorman(a)127.0.0.1...
> thing (someone(a)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(a)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
>

 | 
Pages: 1
Prev: Set up trace
Next: Send Mail from SQL server 2000