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:
Note: The underlined with orange marker are table names and with green marker is your column name.
Hope you'll enjoy this custom search.