SQL query to find all references of a particular column in a database?

Question

I need to write some SQL to find all references of a particular column in a database. The column that I'm trying to find references to exists in a different databases. I've found a few examples of finding references of a column that exist in the same database:

In SQL Server, how can I find everywhere a column is referenced?

But I'm having problems figuring out how to do this for a column that exists in a different database. Can you provide the SQL for this? For example purposes, let's refer to the external column I'm trying to find as:

MyExternalDB.MyExternalSchema.MyExternalTable.MyExternalColumn

Essentially you can't. There is no external dependency created. — Sean Lange, Nov 20 '17 at 20:25
starting point perhaps: https://stackoverflow.com/q/12051216/125981 — Mark Schultheiss, Nov 20 '17 at 20:34

score 0 · Answer 1 · answered Nov 21 '17 at 07:39

Ok, just run this and make sure you set your ColumnName variable

USE [master];
GO

IF OBJECT_ID('tempdb..#columns') IS NOT NULL
    DROP TABLE #columns;
GO


CREATE TABLE #columns
    ( databaseName nvarchar(MAX),
      columnid     int,
      columnName   nvarchar(MAX),
      objectid     int,
      objectName   nvarchar(MAX));

DECLARE @databaseName sysname;
DECLARE @columnName nvarchar(MAX) = 'ColumnName';

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC FOR
    SELECT [name]
    FROM [sys].[databases]
    WHERE [state] = 0
      AND [name] NOT IN ( 'tempdb', 'master', 'msdb', 'model' );
OPEN cur;
FETCH NEXT FROM cur
INTO @databaseName;
WHILE ( @@FETCH_STATUS != -1 )
    BEGIN;
        IF ( @@FETCH_STATUS != -2 )
            BEGIN;

                DECLARE @statement nvarchar(MAX);

                SET @statement =N'Use '+ @databaseName +
                N';
                if EXISTS (SELECT name FROM sys.[columns] WHERE name = ''' + @columnName + ''')
                BEGIN;
                INSERT [#columns] ( [databaseName], [columnid], [columnName], [objectid], [objectName] )
                       SELECT ''' + @databaseName + N''',
                              c.[column_id],
                              c.[name],
                              o.[object_id],
                              o.[name]
                       FROM sys.[columns] c
                       INNER JOIN sys.[objects] o
                          ON [o].[object_id] = [c].[object_id]
                       WHERE c.[name] = ''' + @columnName + '''; 
                       END;';

                EXEC [sys].[sp_executesql] @stmt = @statement;


            END;
        FETCH NEXT FROM cur
        INTO @databaseName;
    END;
CLOSE cur;
DEALLOCATE cur;

SELECT * FROM [#columns];

SQL query to find all references of a particular column in a database?

1 Answers1