SQL Server: Searching data in all tables in multiple databases

Recently, I had to deal with a weird issue which was related to possible invalid references stored in a database. I did not know much about this database so in order to remove these instances I wanted to search through all the tables in all the databases for a specific fairly long and definitely unique string value.

Then I came across this article which suggests a great solution for searching within a single database, however I had a fair amount of databases to look through so instead I wanted to see if with a little bit of tweaking I could make it work for multiple databases. It turned out I can and I thought I'd share the result.

So below is a stored procedure that you need somewhere on the server (db not important) that will search through all the tables in each of the databases you supply as a comma delimited string. You could also rework this to be a simple script rather than a stored procedure if that suits you better.

I hope it does come useful ;)

 /****** Object: StoredProcedure [dbo].[SearchAllTables]  Script Date: 06/18/2010 11:17:56 ******/  
 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchAllTablesMultipleDbs]') AND type in (N'P', N'PC'))  
 DROP PROCEDURE [dbo].[SearchAllTablesMultipleDbs]  
 GO  
 CREATE PROC SearchAllTablesMultipleDbs  
 (  
      @SearchStr nvarchar(100),  
      @DBListCSV nvarchar(1000)  
 )  
 AS  
 BEGIN  
      -- Copyright © 2010 Plamen Balkanski. All rights reserved.  
      -- Uses SearchAllTables procedure written by Narayana as a basis for searching data in multiple dbs & tables .  
      -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.  
      -- This procedure uses the modified SearchAllTables to allow searching through a list of DBs  
      -- Note: there is almost no validation so if you use it then ensure you pass existing DB names otherwise it will fail.  
      SET NOCOUNT ON  
   DECLARE @idx int      
   DECLARE @slice nvarchar(1000)      
   DECLARE @ResultsTable varchar(100)  
   DECLARE @SqlExec nvarchar(2000)  
   DECLARE @Delimiter char(1)  
   -- You can parameterise this one if you want.  
   SET @Delimiter = ','  
   SET @ResultsTable = '##MultipleDBSearchResultsTable'  
      -- drop/recreate the global temp table - note: must be global as otherwise it is not available in the EXECs  
      EXEC( 'IF (OBJECT_ID(''tempdb..' + @ResultsTable +''') IS NOT NULL) DROP TABLE ['+ @ResultsTable + ']')  
      EXEC ('CREATE TABLE ' + @ResultsTable + ' (DbName nvarchar(370),ColumnName nvarchar(370), ColumnValue nvarchar(3630))')  
    -- Split the database names and Run SearchAllTables for each  
   SELECT @idx = 1      
   IF len(@DBListCSV)>1 AND @DBListCSV is NOT null       
   BEGIN   
           WHILE @idx!= 0      
           BEGIN      
                SET @idx = charindex(@Delimiter,@DBListCSV)      
                IF @idx!=0      
                     SET @slice = left(@DBListCSV,@idx - 1)      
                ELSE      
                     SET @slice = @DBListCSV      
                IF(len(@slice)>0)   
                BEGIN  
                     SELECT @SqlExec =  
                     ' USE ' + @slice +   
                     '  
                     DECLARE @SearchStr nvarchar(256)  
                     SET @SearchStr = ''' + @SearchStr + '''  
                     DECLARE @TableName nvarchar(256)  
                     DECLARE @SearchStr2 nvarchar(110)  
                     DECLARE   
                          @ColumnName nvarchar(128),   
                          @DbName nvarchar(256),  
                          @SqlExec2 nvarchar(1000)  
                     SET @TableName = ''''  
                     SET @SearchStr2 =''%'' + @SearchStr + ''%''  
                     WHILE @TableName IS NOT NULL  
                     BEGIN  
                               SET @ColumnName = ''''  
                               -- In here make sure we reference each database  
                               SELECT DISTINCT @TableName = MIN(QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME)),  
                                         @DbName = MIN(QUOTENAME(TABLE_CATALOG))  
                               FROM      ' + @slice +'.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      ' + @slice +'.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  
                                         SELECT @SqlExec2 =  
                                              ''INSERT INTO ' + @ResultsTable + ' SELECT '''''' + @DbName + '''''','''''' + @TableName + ''.'' + @ColumnName + '''''', LEFT('' + @ColumnName + '', 3630)   
                                              FROM '' + @TableName + '' (NOLOCK) ' +  
                                              ' WHERE '' + @ColumnName + '' LIKE '''''' + @SearchStr2 + ''''''''  
                                         EXEC(@SqlExec2)  
                                    END  
                               END       
                          END'  
                          EXEC(@SqlExec)  
                     END  
                SET @DBListCSV = right(@DBListCSV,len(@DBListCSV) - @idx)      
                IF len(@DBListCSV) = 0 BREAK      
           END    
      END  
      -- show results  
      EXEC (' SELECT DISTINCT DbName, ColumnName, ColumnValue FROM ' + @ResultsTable)  
 END  
 /*  
 EXEC SearchAllTablesMultipleDbs 'SearchString', 'DbName1,DbName2'  
 GO   
 */