Search a string like '%ontario%' in ALL the tables of database

Thanks so much for your help

I suggest to play around with Cursors.

DECLARE @SearchStr NVARCHAR(100) = '%Ontario%'
DECLARE @SchemaName NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(MAX)
DECLARE @ColumnName NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

DECLARE search_cursor CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')

OPEN search_cursor
FETCH NEXT FROM search_cursor INTO @SchemaName, @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Construct the dynamic SQL query properly
    SET @SQL = 'IF EXISTS (SELECT 1 FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + ' LIKE @SearchPattern) 
                PRINT ''Match found in: ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName) + ''''

    -- Execute the dynamic SQL with parameterized query
    EXEC sp_executesql @SQL, N'@SearchPattern NVARCHAR(100)', @SearchStr

    FETCH NEXT FROM search_cursor INTO @SchemaName, @TableName, @ColumnName
END

CLOSE search_cursor
DEALLOCATE search_cursor

keep in mind, that if you'll execute that on production server, you'll have some problems

In some TV shows there is a saying: don't try this at home :) 

Don't try the script below on production, as you may hang your server. Also make sure, that user, which will execute this cript has enough permissions to deal with Information_Schema tables

Try the script below:

DECLARE @SearchStr NVARCHAR(100) = '%Ontario%'
DECLARE @SchemaName NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(MAX)
DECLARE @ColumnName NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

DECLARE search_cursor CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')

OPEN search_cursor
FETCH NEXT FROM search_cursor INTO @SchemaName, @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Construct the dynamic SQL query properly
    SET @SQL = 'IF EXISTS (SELECT 1 FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + ' LIKE @SearchPattern) 
                PRINT ''Match found in: ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName) + ''''

    -- Execute the dynamic SQL with parameterized query
    EXEC sp_executesql @SQL, N'@SearchPattern NVARCHAR(100)', @SearchStr

    FETCH NEXT FROM search_cursor INTO @SchemaName, @TableName, @ColumnName
END

CLOSE search_cursor
DEALLOCATE search_cursor