Have you ever wished the "Ctrl+F" command in management studio?
Here it is! :)



CREATE TABLE #table_list
(
id numeric(10) identity,
tab varchar(300),
col varchar(300)
)

CREATE TABLE #search_list
(
Tab    varchar(100),
Col    varchar(100),
Quantity  numeric(10)
)


INSERT INTO #table_list (Tab, Col)
SELECT DISTINCT
TABLE_NAME,
COLUMN_NAME
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME is not null


declare @sql varchar(4000)
declare @tab varchar(200)
declare @col varchar(200)

declare @id numeric(10)
declare @max numeric(10)

set @id=1
set @max=(select max(ID) from #table_list)

WHILE @id <= @max
BEGIN
SET @tab=(SELECT tab FROM #table_list where ID=@id)
SET @col=(SELECT col FROM #table_list where ID=@id)
SET @id=@id+1
SET @sql= '
    INSERT INTO #search_list
    SELECT
     ''' + @tab + ''' as Tab,
     ''' + @col + ''' as Col,
     count(*) as Quantity
    FROM ' + @tab + ' WHERE ' + @col + '=''X'' '
EXEC(@sql)
END

SELECT * FROM #Search_List WHERE Quantity>0