Monday, 11 July 2011

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.