Searches a specific string inside all tables of a DB

search_string.sql

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '48a95b8a-5636-e811-95a5-30e37a693c68'

CREATE TABLE #Results (
    ColumnName nvarchar(370),
    ColumnValue nvarchar(3630)
)

SET NOCOUNT ON

DECLARE @TableName nvarchar(256),
        @ColumnName nvarchar(128),
        @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')

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', 'int', 'decimal')
        AND QUOTENAME(COLUMN_NAME) > @ColumnName)
        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO #Results
            EXEC
            (
            'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
            ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END
END
SELECT
    ColumnName,
    ColumnValue
FROM #Results
DROP TABLE #Results