Few days before i have a requirement to find out the table name which are having a specific column with a specific value throughout the database.

for that i build a query which takes  <COLUMN NAME> and <VALUE> as parameter 

For example : A column <Col1> occurs in multiple tables in a database and we have to find out  those tables name which are having <Col1>

with a perticular value (say <Val1>)  than this procedure will help.

Set ansi_nulls on

go

set quoted_identifier on

go

create Procedure [dbo].[TableIdentifierForSpecificValue]

                                                                                                (

                                                                                                  @ColumnName Varchar(100)

                                                                                                , @Value varchar(100) 

                                                                                                )

as

begin

DECLARE  @Query varchar(max)

       , @Count int = 1

IF OBJECT_ID('Tempdb..#Imd') is not null

drop table #Imd

IF OBJECT_ID('Tempdb..#OutPut') is not null

drop table #OutPut

CREATE TABLE #IMD (ID INT, QUERY VARCHAR(MAX),TableName varchar(100), TableSchema varchar(100))

CREATE TABLE #OutPut (TableName Varchar(100))

INSERT INTO #IMD (ID,QUERY,TableName,TableSchema)

SELECT  ROW_NUMBER() OVER (ORDER BY CLM.TABLE_CATALOG)

       , 'SELECT ' + CLM.COLUMN_NAME + ' FROM ' + TBL.TABLE_SCHEMA + '.' + CLM.TABLE_NAME + ' WHERE Convert(Varchar(100),' + CLM.COLUMN_NAME + ') = ''' + @Value + ''''

       , CLM.TABLE_NAME , TBL.TABLE_SCHEMA

FROM  Information_Schema.COLUMNS CLM

INNER JOIN INFORMATION_SCHEMA.TABLES TBL ON CLM.COLUMN_NAME = @ColumnName

                                        AND TBL.TABLE_TYPE = 'BASE TABLE'

                                        AND CLM.TABLE_NAME = TBL.TABLE_NAME

WHILE @Count <= (SELECT COUNT(1) FROM #IMD)

BEGIN

set @Query = '

IF EXISTS ( ' + (SELECT QUERY FROM #IMD WHERE ID = @COUNT )+ ')

 SELECT ''' + (SELECT TableSchema + '.' + TableName FROM #IMD WHERE ID = @COUNT ) + ''''

INSERT INTO #OutPut (TableName)

EXEC(@query)

SET @Count = @Count + 1

END

SELECT * FROM #OutPut

IF OBJECT_ID('Tempdb..#Imd') is not null

drop table #Imd

IF OBJECT_ID('Tempdb..#OutPut') is not null

drop table #OutPut

end