How to find column names for all tables in all databases in SQL Server

sql

database-management

sql-server

information-schema

byAnton Shumikhin·Nov 30, 2024

To query column names in all tables and databases, one can utilize the INFORMATION_SCHEMA.COLUMNS system view:

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

This lists columns along with corresponding database and table names. To retrieve server-wide results, execute the script individually for each database or use the sp_MSforeachdb stored procedure or dynamic SQL.

Detailed exploration

Multidatabase querying using sp_MSforeachdb

Besides running the script on each database individually, there is a more straightforward method - an undocumented stored procedure named sp_MSforeachdb, which efficiently executes SQL statements on each database.

EXEC sp_MSforeachdb 'USE [?]; SELECT * FROM INFORMATION_SCHEMA.COLUMNS'

Note: Use it with caution as it’s not officially documented by Microsoft, and there is no guarantee that it will exist in future versions of SQL Server.

Standard schema views for column details

Make use of SQL Server's schema views to get additional information, such as data type, nullability, and size of columns.

SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    IS_NULLABLE, 
    CHARACTER_MAXIMUM_LENGTH 
FROM 
    INFORMATION_SCHEMA.COLUMNS;

Handling schema variations

When dealing with multiple databases, you need to take into account different schema configurations as not all tables contain the same columns. Therefore, the below dynamic SQL can be used, which adapts the query according to the context switching.

DECLARE @DBName nvarchar(1000);
SET @DBName = N'MyDatabase'; --need to iterate over databases
EXEC ('USE ' + @DBName + '; SELECT * FROM INFORMATION_SCHEMA.COLUMNS');

Tip: Humour prescribed by Doctor Stack Overflow - a Null doctor spends most of his time at null conferences, discussing nothing.

Extending scope to handle different SQL environments

Using sp_MSforeachdb for retrieving columns across all databases

Without specifying each database, the sp_MSforeachdb procedure enables querying across all bases. This query offers database-wide column name retrieval whilst not overlooking critical details like nullability and data type.

EXEC sp_MSforeachdb '
USE [?]; 
SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE ''%your_column_pattern%''

Joke brought to you by Reddit: SQL statements are like good wines: they should be well-structured, delicious and full of characters.

Dynamic SQL for database-switching within queries

In cases sp_MSforeachdb is not available, dynamic SQL is an excellent alternative. It caters to the ability to switch databases within queries, hence ensuring flexible and accurate data retrieval.

DECLARE @sqlCommand NVARCHAR(MAX)
SET @sqlCommand = N'USE [?]; SELECT * FROM INFORMATION_SCHEMA.COLUMNS'
-- Execute the command, and also print
EXEC sp_executesql @sqlCommand