To find all tables that contain a column with a specified name in a SQL Server database, you can use the INFORMATION_SCHEMA.COLUMNS view. This view contains information about each column in the database, including the table name and the column name. Here’s a SQL query that searches for all tables containing a column named YourColumnName:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'YourColumnName'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

Replace 'YourColumnName' with the actual name of the column you’re looking for. This query returns a list of tables (along with their schema names) that have a column with the specified name.

This approach is useful because it works without needing to know the specific tables in advance, and it leverages the SQL Server’s built-in metadata views to provide a flexible and powerful way to introspect the database schema.

To search for a column name across all databases on a SQL Server instance, you will need to dynamically query each database using a cursor or a similar iterative mechanism since SQL Server does not provide a built-in, cross-database query capability for schema metadata by default. The following script demonstrates how to do this by using a cursor to iterate through all databases, dynamically constructing and executing a SQL query for each:

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

-- Table to store results
CREATE TABLE #Results (DatabaseName NVARCHAR(255), SchemaName NVARCHAR(255), TableName NVARCHAR(255))

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

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)
        SELECT ''' + @DatabaseName + ''' AS DatabaseName, TABLE_SCHEMA, TABLE_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE COLUMN_NAME = ''' + @ColumnName + '''
        ORDER BY TABLE_SCHEMA, TABLE_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

This script does the following:

1. Declares a cursor to iterate through all online, non-system databases on the SQL Server instance.
2. For each database, it dynamically constructs a SQL query string that searches for the specified column name within that database’s INFORMATION_SCHEMA.COLUMNS view.
3. Executes the constructed query using sp_executesql, inserting the results (database name, schema name, and table name) into a temporary table.
4. After iterating through all databases, it selects the contents of the temporary table, displaying all instances of the specified column across all databases.
5. Finally, it cleans up by dropping the temporary table.

Remember to replace 'YourColumnName' with the actual name of the column you are searching for. This script provides a comprehensive search across all user databases on a SQL Server instance, helping you locate a specific column wherever it exists.