List All Columns in Database or Server

To get a list of all columns within a database, you can use the ANSI compliant INFORMATION_SCHEMA.COLUMNS system view.

[cc lang=”sql”]
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
[/cc]

In order to get all the columns in all the databases however, you need to loop through the databases. To do this, you can use the undocumented sp_MSForEachDB procedure that Microsoft ships.

[cc lang=”sql”]
sp_MSForEachDB @command1=’USE ?;

SELECT
Table_Catalog
,Table_Schema
,Table_Name
,Column_Name
,Data_Type
,Character_Maximum_Length
FROM INFORMATION_SCHEMA.COLUMNS’
[/cc]