Reader Level:
ARTICLE

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

Posted by Amit Choudhary Articles | SQL July 12, 2011
In this article I'll show you how to search a column value in all the referencing tables.
  • 0
  • 0
  • 7944


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.

COMMENT USING

Trending up