Search Specific data in the SQL database

 DECLARE @search VARCHAR(100), @table SYSNAME, @column SYSNAME

DECLARE curTabCol CURSOR FOR

    SELECT c.TABLE_SCHEMA + '.' + c.TABLE_NAME, c.COLUMN_NAME

    FROM INFORMATION_SCHEMA.COLUMNS c

    JOIN INFORMATION_SCHEMA.TABLES t 

      ON t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_TYPE='BASE TABLE' -- avoid views

    WHERE c.DATA_TYPE IN ('varchar','nvarchar') -- searching only in these column types

    --AND c.COLUMN_NAME IN ('NAME','DESCRIPTION') -- searching only in these column names

SET @search='C990909'

OPEN curTabCol

FETCH NEXT FROM curTabCol INTO @table, @column

WHILE (@@FETCH_STATUS = 0)

BEGIN

    EXECUTE('IF EXISTS 

             (SELECT * FROM ' + @table + ' WHERE ' + @column + ' = ''' + @search + ''') 

             PRINT ''' + @table + '.' + @column + '''')

    FETCH NEXT FROM curTabCol INTO @table, @column

END

CLOSE curTabCol

DEALLOCATE curTabCol

------------------------------------------------OR----------------------------------------------------------

BEGIN

SELECT sch.name SchemaName, tbl.name TableName, col.name ColumnName
, Query = 'SELECT ''['+ sch.name +'].['+ tbl.name +']'' TableName, ''['+ col.name +']'' ColumnName, ['+ col.name +'] ColumnValue FROM ['+ sch.name +'].['+ tbl.name +'] WHERE ['+ col.name +'] LIKE ''%CEN%'' '
FROM sys.tables tbl
INNER JOIN sys.schemas sch ON tbl.schema_id = sch.schema_id
INNER JOIN sys.columns col ON tbl.object_id = col.object_id
WHERE col.system_type_id IN (167,175,231,239);

END