A Comprehensive Guide to Querying All Column Names Across All Databases in SQL Server

Introduction

In database management and maintenance tasks, there is often a need to retrieve column name information from all tables within databases. This requirement is particularly important in scenarios such as database migration, documentation generation, and data dictionary maintenance. SQL Server provides multiple system views and functions to access metadata information, enabling programmatic retrieval of this information.

Basic Query Methods

The most fundamental approach involves using system catalog views sys.columns and sys.objects to obtain column information from the current database. Here is a basic query example:

SELECT 
o.name AS [Table], 
c.name AS [Column]
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
ORDER BY o.name, c.name

This query returns all column names from all tables in the current database, sorted by table name and column name. The sys.objects view contains information about all objects in the database, while the sys.columns view contains detailed information about all columns.

Detailed Column Information Query

To obtain more detailed column information, including data types, nullability, identity properties, etc., the query can be extended to include additional system views:

SELECT
    s.name AS ColumnName,
    sh.name + '.' + o.name AS ObjectName,
    o.type_desc AS ObjectType,
    CASE
        WHEN t.name IN ('char', 'varchar') THEN t.name + '(' + CASE WHEN s.max_length < 0 THEN 'MAX' ELSE CONVERT(varchar(10), s.max_length) END + ')'
        WHEN t.name IN ('nvarchar', 'nchar') THEN t.name + '(' + CASE WHEN s.max_length < 0 THEN 'MAX' ELSE CONVERT(varchar(10), s.max_length/2) END + ')'
        WHEN t.name IN ('numeric') THEN t.name + '(' + CONVERT(varchar(10), s.precision) + ',' + CONVERT(varchar(10), s.scale) + ')'
        ELSE t.name
    END AS DataType,
    CASE
        WHEN s.is_nullable = 1 THEN 'NULL'
        ELSE 'NOT NULL'
    END AS Nullable,
    CASE
        WHEN ic.column_id IS NULL THEN ''
        ELSE ' identity(' + ISNULL(CONVERT(varchar(10), ic.seed_value), '') + ',' + ISNULL(CONVERT(varchar(10), ic.increment_value), '') + ')=' + ISNULL(CONVERT(varchar(10), ic.last_value), 'null')
    END
    + CASE
        WHEN sc.column_id IS NULL THEN ''
        ELSE ' computed(' + ISNULL(sc.definition, '') + ')'
    END
    + CASE
        WHEN cc.object_id IS NULL THEN ''
        ELSE ' check(' + ISNULL(cc.definition, '') + ')'
    END AS MiscInfo
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id = t.user_type_id AND t.is_user_defined = 0
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas sh ON o.schema_id = sh.schema_id
LEFT OUTER JOIN sys.identity_columns ic ON s.object_id = ic.object_id AND s.column_id = ic.column_id
LEFT OUTER JOIN sys.computed_columns sc ON s.object_id = sc.object_id AND s.column_id = sc.column_id
LEFT OUTER JOIN sys.check_constraints cc ON s.object_id = cc.parent_object_id AND s.column_id = cc.parent_column_id
ORDER BY sh.name + '.' + o.name, s.column_id

This complex query provides complete technical specifications for columns, including:

• Column names and their parent object names
• Object type descriptions
• Precise data type definitions
• Nullability constraints
• Identity column properties (seed value, increment value, last value)
• Computed column definitions
• Check constraint definitions

Cross-Database Query Implementation

To query column information across all databases, dynamic SQL techniques are required. Here is an example using the sys.databases view to construct cross-database queries:

DECLARE @SQL varchar(max)
SET @SQL = ''
SELECT @SQL = @SQL + 'UNION
SELECT 
''' + d.name + '.' + sh.name + '.' + o.name + ''', c.name, c.column_id
FROM ' + d.name + '.sys.columns c
    INNER JOIN ' + d.name + '.sys.objects o ON c.object_id = o.object_id
    INNER JOIN ' + d.name + '.sys.schemas sh ON o.schema_id = sh.schema_id
'
FROM sys.databases d
SELECT @SQL = RIGHT(@SQL, LEN(@SQL) - 5) + 'ORDER BY 1, 3'
EXEC (@SQL)

This solution works by:

1. Iterating through the sys.databases view to obtain all database names
2. Constructing corresponding query fragments for each database
3. Merging all query results using UNION operators
4. Executing the generated dynamic SQL statement

Using sp_MSforeachdb Stored Procedure

Another approach for cross-database queries involves using the undocumented stored procedure sp_MSforeachdb:

EXEC sp_MSforeachdb 'SELECT 
    ''?'' AS DatabaseName, 
    o.name AS TableName, 
    c.name AS ColumnName
FROM sys.columns c
    INNER JOIN ?.sys.objects o ON c.object_id = o.object_id
ORDER BY o.name, c.column_id'

This method is more concise, but it's important to note that sp_MSforeachdb is an undocumented feature and may be removed in future SQL Server versions.

INFORMATION_SCHEMA Views Method

In addition to system catalog views, the standard INFORMATION_SCHEMA.COLUMNS view can also be used:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

This approach adheres to SQL standards and offers better cross-database compatibility. To query specific databases, use:

SELECT * FROM DBNAME.INFORMATION_SCHEMA.COLUMNS

Specific Column Search

In practical work, there is often a need to search for column names containing specific strings. The following query pattern can be used:

SELECT 
    t.name AS table_name,
    SCHEMA_NAME(schema_id) AS schema_name,
    c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name

This pattern is particularly useful in database maintenance and refactoring work, enabling quick identification of columns containing specific business logic.

Technical Comparison and Best Practices

Different methods have their respective advantages and disadvantages:

• System Catalog Views: Provide the most detailed information but with more complex syntax
• INFORMATION_SCHEMA Views: Higher standardization but relatively limited information
• Dynamic SQL: High flexibility but requires handling string concatenation
• sp_MSforeachdb: Simple to use but relies on undocumented functionality

When choosing a method, consider specific requirements: if only basic column information is needed, INFORMATION_SCHEMA.COLUMNS is the simplest choice; if complete column technical specifications are required, system catalog views are better; for production environments, it's advisable to avoid relying on undocumented features.

Performance Considerations

Cross-database queries may impact system performance, especially in systems with numerous databases and tables. Recommendations include:

• Executing such queries during periods of low system load
• Considering caching results in temporary tables for subsequent use
• Processing databases in batches for large systems
• Using caution in production environments to avoid affecting normal business operations

Conclusion

SQL Server provides multiple powerful tools for accessing database metadata information. By appropriately selecting and using techniques such as system views and dynamic SQL, it's possible to effectively retrieve column name information from all tables across all databases. These techniques are not only applicable to daily database management tasks but also provide foundational support for advanced applications such as automated script development and database documentation generation.