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