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
See the snapshot for the output
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.