SQL Server: Search a string in all the varchar columns of a single/multiple tables

To search a given string in all the varchar, char, nchar, nvarchar columns in a specified table, I found a technique which is explained below.
1) Execute below script by passing your string  between %%.
2) Copy and paste the result-set in to query window and execute to find which column contains your string.

SELECT
'select distinct ''' + tab.name + '.' + col.name
+ '''  from [' + tab.name
+ '] where [' + col.name + '] like ''%string_to_search%'' union '
from sys.tables tab
join sys.columns col on (tab.object_id = col.object_id)
join sys.types types on (col.system_type_id = types.system_type_id)
where tab.type_desc ='USER_TABLE'
and types.name IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR') and tab.name = 'your_table_name' ;

You can search all the tables in your database by using below script and follow same method as explained above.

SELECT
'select distinct ''' + tab.name + '.' + col.name
+ '''  from [' + tab.name
+ '] where [' + col.name + '] like ''%string_to_search%'' union '
from sys.tables tab
join sys.columns col on (tab.object_id = col.object_id)
join sys.types types on (col.system_type_id = types.system_type_id)
where tab.type_desc ='USER_TABLE'
and types.name IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR');