List All Columns in Database or Server

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.

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS

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.

sp_MSForEachDB @command1='USE ?;

SELECT
    Table_Catalog
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
FROM INFORMATION_SCHEMA.COLUMNS'