Dynamic Query to Search by Column Value in All Referencing Tables in SQL SERVER



In this article 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 a large numbre of tables in the database and you are not familiar with the schema and 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 snapshot for output:

DynaSerSql.gif

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

Hope you'll enjoy this custom search.

Up Next
    Ebook Download
    View all
    Learn
    View all