Find a specific column entry in an unknown table in a database

DECLARE
   @TotalRows   int,
   @Counter     int,
   @TableName   varchar(50),
   @ColumnName  varchar(50),
   @FieldValue  varchar(250),
   @SQLCommand  nvarchar(1000),
   @ValueToFind varchar(100)


-->
SET @ValueToFind = 'Test case'
-->


DECLARE @MyTable table
   (  RowID      int IDENTITY,
      TableName  varchar(50),
      ColumnName varchar(50)
   )


CREATE TABLE #FoundTable
   (  RowID      int IDENTITY,
      Tablename  varchar(50)
   )



INSERT INTO @MyTable
   SELECT
      TABLE_NAME,
      COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ( 'char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext' )


SELECT
   @TotalRows = @@ROWCOUNT,
   @Counter   = 1


WHILE ( @Counter <= @TotalRows )

 
   BEGIN
  SELECT
         @TableName = TableName,
         @ColumnName = ColumnName
      FROM @MyTable
      WHERE RowID = @Counter

     
      SET @SQLCommand = 'IF EXISTS ( ' +
                        'SELECT 1 FROM [' + @TableName + '] WHERE [' + @ColumnName + '] LIKE ''%' + @ValueToFind + '%'' ' +
                        ' )' +
                        'INSERT INTO #FoundTable ' +
                        '   SELECT ''' + @TableName + '(' + @ColumnName + ')'''
EXECUTE sp_executesql @SQLCommand
     
      SET @Counter = ( @Counter + 1 )

 
   END


SELECT * FROM #FoundTable


DROP TABLE #FoundTable