Wildcard search the entire Database

Below is the www.Muppix.co code to search an entire database for any text in SQL Server for those tables updated in the last Nth days. Change 'mytext' and number of days.
​All tables are searched, ignoring case. A wildcard search is performed even on numeric columns. So it will find '17' in any field even in the integer field with :  19170916.  

---

DECLARE @SearchStr Varchar(50) ='mytext'   -- enter your text to search
DECLARE @DaysOld                 int  =  2             -- enter number of days the table was last updated
​
DECLARE @Results TABLE(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 t2
                      JOIN sys.tables t1
                      ON t2.TABLE_NAME = t1.name 
                WHERE  TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND  t1.Modify_date >= DATEADD(day, -1 * @DaysOld,GETDATE())
                    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 @Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue,* FROM @Results

Typical output :

​ColumnName                                               ColumnValue
[dbo].[mynewtable].[mycolumn]                 mytext
[dbo].[mynewtable].[mycolumn]                 mytext again
[dbo].[mynewtable].[mycolumn]                 mytext before mysecondtext
[dbo].[mynewtable].[mycolumn]                 so mytext and mytext in column
[dbo].[mynewtable].[mysecondcolumn]    end is mytext
[dbo].[mynewtable].[mysecondcolumn]    mytext found in beginning
[dbo].[mysecondtable].[mycolumn]           end is mytext
[dbo].[mysecondtable].[mycolumn]           mytext found in beginning
[dbo].[mytable].[mycolumn]                        mytext,again
[dbo].[mytable].[mycolumn]                        end,is,mytext
[dbo].[mytables].[mylastcolumn]                Mytext

View More