SQL Query for Mutile Table Search

See:

https://social.technet.microsoft.com/wiki/contents/articles/24169.sql-server-searching-all-columns-in-a-table-for-a-string.aspx

Hi @Musa Dan-Hamidu
Please check this query, referencing this bolg: Search every table and field in a SQL Server Database Updated

DECLARE @SearchStr nvarchar(100)  
SET @SearchStr = 'xxxx'  
   CREATE TABLE #Results (TableName nvarchar(50),  
   ColumnName nvarchar(50), ColumnValue nvarchar(300))  
   
    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 + ''', ' + @ColumnName + ' FROM ' + @TableName + ' (NOLOCK) ' +  
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2  
                )  
            END  
        END     
    END  
   
    SELECT TableName,ColumnName, ColumnValue FROM #Results  
   
DROP TABLE #Results  

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.