Microsoft SQL query to search across all databases and identify all tables with columns matching a string.

mssql-column-search-across-all-databases.sql

-- 2024-11-20
-- Searches across all databases on a MSSQL server to identify all tables with columns matching a string. 
-- modified from https://thedbahub.com/searching-for-a-specific-table-column-across-all-databases-in-sql-server/

DECLARE @DatabaseName NVARCHAR(255)
DECLARE @Query NVARCHAR(MAX)
DECLARE @ColumnName NVARCHAR(255) = 'IPAddress' -- Column name you're looking for

-- Table to store results
CREATE TABLE #Results (DatabaseName NVARCHAR(255), SchemaName NVARCHAR(255), TableName NVARCHAR(255), ColumnName NVARCHAR(255), DataType VARCHAR(255), ColumnLength INT)

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM sys.databases 
WHERE state = 0 -- Only select databases that are online
AND database_id > 4 -- Skip system databases
ORDER BY name

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DatabaseName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    SET @Query = '
        USE [' + @DatabaseName + '];
        INSERT INTO #Results (DatabaseName, SchemaName, TableName, ColumnName, DataType, ColumnLength)
        SELECT ''' + @DatabaseName + ''' AS DatabaseName,
            TABLE_SCHEMA AS ''SchemaName'',
            TABLE_NAME AS ''TableName'',
            COLUMN_NAME AS ''ColumnName'',
            DATA_TYPE AS ''DataType'',
            CHARACTER_MAXIMUM_LENGTH AS ''ColumnLength''
        FROM INFORMATION_SCHEMA.Columns
        WHERE COLUMN_NAME LIKE ''%' + @ColumnName + '%''
        ORDER BY TABLE_NAME, COLUMN_NAME;'

    EXEC sp_executesql @Query

    FETCH NEXT FROM db_cursor INTO @DatabaseName
END 

CLOSE db_cursor
DEALLOCATE db_cursor

-- Display results
SELECT * FROM #Results
DROP TABLE #Results