Funzione per ricercare un valore testuale specifico in un DataBase

--diabilito output conteggio righe
use database
Set NoCount On

--stringa da cercare ed altre variabili
Declare @strToFind varchar(max) = 'StringaDaCercare'
Declare @stringSchema sysname = ''
Declare @stringTab sysname = ''
Declare @stringListaCampi varchar(Max) = ''
Declare @stringListaCampiDove varchar(Max) = ''
Declare @stringSQL varchar(max) = ''

--elenco tabelle e campi
Declare @Tabs table(
TABLE_SCHEMA sysname
,TABLE_NAME sysname
)

Insert into @Tabs
Select Distinct TABLE_SCHEMA,TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
Where DATA_TYPE in ('char','nchar','varchar','nvarchar')
Order by TABLE_SCHEMA,TABLE_NAME

--ciclo le righe
While 1=1
Begin

         Set @stringListaCampi = ''
         Set @stringListaCampiDove = ''

         Select Top 1 @stringSchema = TABLE_SCHEMA, @stringTab=TABLE_NAME from
@Tabs
         Order by TABLE_SCHEMA,TABLE_NAME;

         --esco su rowcount 0
         IF @@ROWCOUNT=0
         Break;

         --formatto la stringa per la ricerca
         With Fields
         AS
                 (
                 Select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
                 Where DATA_TYPE in ('char','nchar','varchar','nvarchar','text','ntext')
                 And TABLE_SCHEMA = @stringSchema
                 And TABLE_NAME = @stringTab
                 )

         Select @stringListaCampi = @stringListaCampi + '' +
QUOTENAME(COLUMN_NAME) + ' AS ' + QUOTENAME(COLUMN_NAME) + ',',
         @stringListaCampiDove = @stringListaCampiDove + QUOTENAME(COLUMN_NAME) +
' Like ''%' + @strToFind + '%'' OR '
         From Fields


         Set @stringSQL = 'Select ''' + QUOTENAME(@stringSchema) + ''' As
Table_Schema,''' +    QUOTENAME(@stringTab) + ''' As Table_Name, ' +
Substring(@stringListaCampi,0,LEN(@stringListaCampi))
         + ' from ' + QUOTENAME(@stringSchema) + '.' + QUOTENAME(@stringTab)
         + ' Where ' +
Substring(@stringListaCampiDove,0,LEN(@stringListaCampiDove)-2)

         --eseguo SQL
         Exec(@stringSQL)

         -- elimino la riga dalla tabella temporanea
         Delete From @Tabs
         Where TABLE_SCHEMA = @stringSchema
         And TABLE_NAME = @stringTab

end