Dynamic query to search column value in all referencing tables in SQL SERVER

In this post I’ll show you how to search a column value in all the referencing tables. Well, you can navigate to the tables and easily query them for the particular value. But what if you have large no. of tables in the database and you are not familiar with the schema how many tables are referencing that column.

So I came across this problem and rather than searching the tables manually. I just wrote a simple SQL query that’ll do that automatically for you.

use[<YOUR DB_NAME>]

DECLARE @sql nvarchar(255)

DECLARE @tablename nvarchar(255)

DECLARE @var <your column type>

DECLARE @columnname nvarchar(100)

--Supply the column name and value here

SET @columnname= <column name>

SET @var=<column value>

DECLARE TableCol CURSOR FOR

SELECT t.name          

      FROM [<YOUR DB_NAME>].sys.columns AS c

            INNER JOIN

             [<YOUR DB_NAME>].sys.tables AS t

            ON t.[object_id] = c.[object_id]

        WHERE     UPPER(c.name) = @columnname order by t.name

       

       

OPEN TableCol

FETCH TableCol INTO @tablename

-- start the main processing loop.

WHILE @@Fetch_Status = 0

BEGIN

-- In this query 'Table_Name' is dummy column that'll display the current tablename as column header

SELECT @sql='select ''TABLE_NAME'' as '''+@tablename+''', * from '+@tablename+' where '+@columnname+'='+@var

EXEC(@sql)

--print @tablename

FETCH TableCol INTO @tablename

END

CLOSE TableCol

DEALLOCATE TableCol

RETURN

GO

 See the output below:

Note: The underlined with orange marker are table names and with green marker is your column name.

Hope you’ll enjoy this custom search.