Search every table and every CHAR columns for a keyword!

I needed to search for a specific keyword and didn't have the chance to know what's the table's name or even what's the column's name!!

Just replace the KEYWORD in the below query to your needs.

DECLARE @Schema varchar(3)
DECLARE @Table Nvarchar(max)
DECLARE @Column varchar(30)
DECLARE @Keyword Nvarchar(30)

SELECT @Keyword = 'BMAC2120-01'

DECLARE Curs CURSOR FAST_FORWARD FOR

SELECT  s.name As [Schema], o.Name AS [Table],c.Name AS [Column]
FROM sys.columns c JOIN sys.objects o ON o.object_id = c.object_id
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE o.type = 'U' and c.collation_name IS NOT null
ORDER BY o.Name,c.Name

OPEN Curs 

FETCH NEXT FROM Curs  INTO @Schema,@Table, @Column
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Table = 'SELECT top 5 [' + @Column + '],''' + @Table + ''' as [Table Name] from ' + +@Schema +'.' + @Table + ' where [' + @Column + '] LIKE ''%' +@Keyword +'%'''
print @Table

FETCH NEXT FROM Curs  INTO @Schema,@Table, @Column
END

CLOSE Curs
DEALLOCATE Curs