SQL  

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

Introduction

In this article, I'll show you how to search for a column value in all the referencing tables in SQL Server. Well, you can navigate to the tables and easily query them for a particular value.

But what if you have many 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 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
SQL

See the snapshot for the output

DynaSerSql.gif

Note. Underlined with the orange marker are table names, and with the green marker is your column name.

I hope you'll enjoy this custom search.

Membership not found