SQL Stored Procedure to search a complete SQL DB for a string/keyword

Create below SearchSQLDB Stored Procedure by connecting to the target SQL DB in your SQL MGMT studio.

 
CREATE PROC SeachSQlDB
(
    @SearchPattern nvarchar(100)
)
AS
BEGIN
    CREATE TABLE #TempResults (ColumnName nvarchar(300), ColumnValue nvarchar(3000))
    SET NOCOUNT ON
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @TempSrchStr nvarchar(110)
    SET @TableName   = ''
    SET @TempSrchStr = QUOTENAME('%' + @SearchPattern + '%','''')
    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName  =  ( 
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_TYPE = 'BASE TABLE' AND
                QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND
                OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
            )
        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName = (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA    = PARSENAME(@TableName, 2) AND
                    TABLE_NAME    = PARSENAME(@TableName, 1) AND
                    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND
                    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #TempResults
                EXEC (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3000) 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @TempSrchStr
                )
            END
        END    
    END
    SELECT ColumnName, ColumnValue FROM #TempResults
END 

Then run the stored procedure as shown below:

 
EXEC SeachSQlDB 'SearchString'
GO

.