Search a value in all database

Question

Hello everyone !
I would like to search in which database i could find the value "game_panel_skill_skilltree_asura_master_spectre". But i have so many database so it's very long to search this one by one. Is there a way to search all of all databases?

N.B: i don't know in which column the value in insert too !

Thanks a lot, have a nice day, Didii

Answer 1

Here is a script that I hope solve your needs. Beware if you are databases are of some size, it take some time for it to complete. You get one result set for every table where is at least a matching row.

Maybe I should try to explain the script, but I let if suffice with that this is a quite an advanced exercise in dynamic SQL, and it took me quite a while to get it right.

I like to call out this line in @dbquery:

   --  AND   c.column_encryption_key_id IS NULL

If you have columns encrypted with Always Encrypted, you need to uncomment this line. I left it commented as I found less likely that you have such columns, and the line would not work on SQL 2014 and earlier.

DECLARE @db            sysname,
        @sp_executesql nvarchar(500),
        @query         nvarchar(MAX),
        @dbstmts       nvarchar(MAX),
        @value         nvarchar(50) = 'Reggio nell''Emilia'

SELECT @query =
    'DECLARE @sq char(1) = char(39)
     SELECT @dbstmts =
           (SELECT  ''IF EXISTS (SELECT *
                                  FROM '' + quotename(s.name) + ''.'' + quotename(t.name) + ''
                                  WHERE '' + quotename(c.name) + '' = @value)
                    SELECT db_name() AS DatabaseName, '' + 
                             quotename(s.name, @sq) + '' AS SchemaName, '' +
                             quotename(t.name, @sq) + '' AS TableName, '' + 
                             quotename(c.name, @sq) + '' AS ColumnName, * 
                    FROM '' + quotename(s.name) + ''.'' + quotename(t.name) + ''
                    WHERE '' + quotename(c.name) + '' = @value'' +
                    char(13) + char(10)
            FROM    sys.tables t
            JOIN    sys.schemas s ON s.schema_id = t.schema_id
            JOIN    sys.columns c ON c.object_id = t.object_id
            WHERE   type_name(c.system_type_id) LIKE ''%char%''
              AND   (c.max_length = -1 OR c.max_length >= len(@value))
            --  AND   c.column_encryption_key_id IS NULL
            FOR XML PATH(''''), TYPE).value(''.'', ''nvarchar(MAX)'')'

DECLARE dbcur CURSOR STATIC LOCAL FOR
   SELECT quotename(name) FROM sys.databases
   WHERE  database_id > 4
     AND  state = 0         -- Only online databases
     AND  is_read_only = 0
   ORDER BY name

OPEN dbcur

WHILE 1 = 1
BEGIN
   FETCH dbcur INTO @db
   IF @@fetch_status <> 0
      BREAK

   SELECT @sp_executesql = @db + '.sys.sp_executesql'

   EXEC @sp_executesql @query, N'@dbstmts nvarchar(MAX) OUTPUT, @value nvarchar(50)', 
                       @dbstmts OUTPUT, @value
   PRINT @db
   IF @dbstmts IS NOT NULL
   BEGIN
      --PRINT @dbstmts
      EXEC @sp_executesql @dbstmts, N'@value nvarchar(50)', @value
   END
END

DEALLOCATE dbcur

Answer 2

Hi @Didiiolu ,

Sorry for the late reply，this does take some time to solve, please also refer to the following stored procedure：

CREATE  PROCEDURE  [dbo].[SP_FindValueInDB]   
(   
     @value  VARCHAR (1024)   
)    
AS   
BEGIN   
     SET  NOCOUNT  ON ;   
     DECLARE  @sql  VARCHAR (1024)    
     DECLARE  @table  VARCHAR(64)    
     DECLARE  @column  VARCHAR(64)    
     CREATE  TABLE  #t (    
         tablename  VARCHAR (64),    
         columnname  VARCHAR (64)    
     )    
     DECLARE  TABLES  CURSOR  FOR    
     SELECT  o. name , c. name  FROM  syscolumns c    
     INNER  JOIN  sysobjects o  ON  c.id = o.id    
     WHERE  o.type =  'U'  AND  c.xtype  IN  (167, 175, 231, 239)    
     ORDER  BY  o. name , c. name    
     OPEN  TABLES    
         FETCH  NEXT  FROM  TABLES    
         INTO  @table , @column    
         WHILE @@FETCH_STATUS = 0    
         BEGIN    
             SET  @sql =  'IF EXISTS(SELECT NULL FROM ['  + @table  +  '] '    
             SET  @sql = @sql +  'WHERE RTRIM(LTRIM(['  + @column  +  '])) LIKE''%'  + @value +  '%'')'    
             SET  @sql = @sql +  'INSERT INTO #t VALUES ('''  + @table  +  ''','''    
             SET  @sql = @sql + @column  +  ''')'    
             EXEC (@sql)    
             FETCH  NEXT  FROM  TABLES    
             INTO  @table , @column    
         END    
     CLOSE  TABLES    
     DEALLOCATE  TABLES    
     SELECT  *  FROM  #t    
     DROP  TABLE  #t    
End  
  
exec  [SP_FindValueInDB]   'game_panel_skill_skilltree_asura_master_spectre'  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

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.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 3

Here is another way I used sometimes back if that helps you.

https://sqlzealots.com/2020/03/29/search-a-value-in-your-database-in-sql-server/

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
Blog
LinkedIn

Answer 4

Hi Erland,

thanks very much for your solution. I add for me two little details in your query;

Display the SearchText in result
Option to choose a LIKE search

Regards Jörg

-- Erland Sommarskog  
-- https://learn.microsoft.com/en-us/answers/questions/208017/search-a-value-in-all-database.html  
  
DECLARE  @db            sysname,  
         @sp_executesql nvarchar(500),  
         @query         nvarchar(MAX),  
         @dbstmts       nvarchar(MAX),  
         @searchmode    bit,  
         @value         nvarchar(50) = '%Sun%'   /* LIKE Search (slower); '%%' for all columns */  
   
SET      @searchmode = (SELECT CASE WHEN CHARINDEX('%', @value) > 0 THEN 1 ELSE 0 END)    /* 1 = LIKE; 0 = EQUAL */     
  
  
  
 IF 1 = @searchmode  
 BEGIN  
 SELECT @query =  
     'DECLARE @sq char(1) = char(39)  
      SELECT @dbstmts =  
            (SELECT  ''IF EXISTS (SELECT *  
                                   FROM '' + quotename(s.name) + ''.'' + quotename(t.name) + ''  
                                   WHERE '' + quotename(c.name) + '' LIKE @value)  
                     SELECT db_name() AS DatabaseName, '' +   
                              quotename(s.name, @sq) + '' AS SchemaName, '' +  
                              quotename(t.name, @sq) + '' AS TableName, '' +   
                              quotename(c.name, @sq) + '' AS ColumnName, '' +   
                              quotename(@value, @sq) + '' AS SearchText, *   
                     FROM '' + quotename(s.name) + ''.'' + quotename(t.name) + ''  
                     WHERE '' + quotename(c.name) + '' LIKE @value'' +  
                     char(13) + char(10)  
             FROM    sys.tables t  
             JOIN    sys.schemas s ON s.schema_id = t.schema_id  
             JOIN    sys.columns c ON c.object_id = t.object_id  
             WHERE   type_name(c.system_type_id) LIKE ''%char%''  
               AND   (c.max_length = -1 OR c.max_length >= len(@value))  
             --  AND   c.column_encryption_key_id IS NULL  
             FOR XML PATH(''''), TYPE).value(''.'', ''nvarchar(MAX)'')'  
END   
ELSE IF 0 = @searchmode  
BEGIN  
SELECT @query =  
     'DECLARE @sq char(1) = char(39)  
      SELECT @dbstmts =  
            (SELECT  ''IF EXISTS (SELECT *  
                                   FROM '' + quotename(s.name) + ''.'' + quotename(t.name) + ''  
                                   WHERE '' + quotename(c.name) + '' = @value)  
                     SELECT db_name() AS DatabaseName, '' +   
                              quotename(s.name, @sq) + '' AS SchemaName, '' +  
                              quotename(t.name, @sq) + '' AS TableName, '' +   
                              quotename(c.name, @sq) + '' AS ColumnName, '' +   
                              quotename(@value, @sq) + '' AS SearchText, *   
                     FROM '' + quotename(s.name) + ''.'' + quotename(t.name) + ''  
                     WHERE '' + quotename(c.name) + '' = @value'' +  
                     char(13) + char(10)  
             FROM    sys.tables t  
             JOIN    sys.schemas s ON s.schema_id = t.schema_id  
             JOIN    sys.columns c ON c.object_id = t.object_id  
             WHERE   type_name(c.system_type_id) LIKE ''%char%''  
               AND   (c.max_length = -1 OR c.max_length >= len(@value))  
             --  AND   c.column_encryption_key_id IS NULL  
             FOR XML PATH(''''), TYPE).value(''.'', ''nvarchar(MAX)'')'  
END  
  
DECLARE dbcur CURSOR STATIC LOCAL FOR  
    SELECT quotename(name) FROM sys.databases  
    WHERE  database_id > 4  
      AND  state = 0         -- Only online databases  
      AND  is_read_only = 0  
    ORDER BY name  
      
 OPEN dbcur  
      
 WHILE 1 = 1  
 BEGIN  
    FETCH dbcur INTO @db  
    IF @@fetch_status <> 0  
       BREAK  
      
    SELECT @sp_executesql = @db + '.sys.sp_executesql'  
      
    EXEC @sp_executesql @query, N'@dbstmts nvarchar(MAX) OUTPUT, @value nvarchar(50)',   
                        @dbstmts OUTPUT, @value  
    PRINT @db  
    IF @dbstmts IS NOT NULL  
    BEGIN  
       PRINT @dbstmts  
       EXEC @sp_executesql @dbstmts, N'@value nvarchar(50)', @value  
    END  
 END  
      
 DEALLOCATE dbcur  

Answer 5

Hi Erland,

thanks very much for your solution. I add for me two little details in your query;

Display the SearchText in result
Option to choose a LIKE search

Regards Jörg

-- Erland Sommarskog  
-- https://learn.microsoft.com/en-us/answers/questions/208017/search-a-value-in-all-database.html  
  
DECLARE  @db            sysname,  
         @sp_executesql nvarchar(500),  
         @query         nvarchar(MAX),  
         @dbstmts       nvarchar(MAX),  
         @searchmode    bit,  
         @value         nvarchar(50) = '%Sun%'   /* LIKE Search (slower); '%%' for all columns */  
   
SET      @searchmode = (SELECT CASE WHEN CHARINDEX('%', @value) > 0 THEN 1 ELSE 0 END)    /* 1 = LIKE; 0 = EQUAL */     
  
  
  
 IF 1 = @searchmode  
 BEGIN  
 SELECT @query =  
     'DECLARE @sq char(1) = char(39)  
      SELECT @dbstmts =  
            (SELECT  ''IF EXISTS (SELECT *  
                                   FROM '' + quotename(s.name) + ''.'' + quotename(t.name) + ''  
                                   WHERE '' + quotename(c.name) + '' LIKE @value)  
                     SELECT db_name() AS DatabaseName, '' +   
                              quotename(s.name, @sq) + '' AS SchemaName, '' +  
                              quotename(t.name, @sq) + '' AS TableName, '' +   
                              quotename(c.name, @sq) + '' AS ColumnName, '' +   
                              quotename(@value, @sq) + '' AS SearchText, *   
                     FROM '' + quotename(s.name) + ''.'' + quotename(t.name) + ''  
                     WHERE '' + quotename(c.name) + '' LIKE @value'' +  
                     char(13) + char(10)  
             FROM    sys.tables t  
             JOIN    sys.schemas s ON s.schema_id = t.schema_id  
             JOIN    sys.columns c ON c.object_id = t.object_id  
             WHERE   type_name(c.system_type_id) LIKE ''%char%''  
               AND   (c.max_length = -1 OR c.max_length >= len(@value))  
             --  AND   c.column_encryption_key_id IS NULL  
             FOR XML PATH(''''), TYPE).value(''.'', ''nvarchar(MAX)'')'  
END   
ELSE IF 0 = @searchmode  
BEGIN  
SELECT @query =  
     'DECLARE @sq char(1) = char(39)  
      SELECT @dbstmts =  
            (SELECT  ''IF EXISTS (SELECT *  
                                   FROM '' + quotename(s.name) + ''.'' + quotename(t.name) + ''  
                                   WHERE '' + quotename(c.name) + '' = @value)  
                     SELECT db_name() AS DatabaseName, '' +   
                              quotename(s.name, @sq) + '' AS SchemaName, '' +  
                              quotename(t.name, @sq) + '' AS TableName, '' +   
                              quotename(c.name, @sq) + '' AS ColumnName, '' +   
                              quotename(@value, @sq) + '' AS SearchText, *   
                     FROM '' + quotename(s.name) + ''.'' + quotename(t.name) + ''  
                     WHERE '' + quotename(c.name) + '' = @value'' +  
                     char(13) + char(10)  
             FROM    sys.tables t  
             JOIN    sys.schemas s ON s.schema_id = t.schema_id  
             JOIN    sys.columns c ON c.object_id = t.object_id  
             WHERE   type_name(c.system_type_id) LIKE ''%char%''  
               AND   (c.max_length = -1 OR c.max_length >= len(@value))  
             --  AND   c.column_encryption_key_id IS NULL  
             FOR XML PATH(''''), TYPE).value(''.'', ''nvarchar(MAX)'')'  
END  
  
DECLARE dbcur CURSOR STATIC LOCAL FOR  
    SELECT quotename(name) FROM sys.databases  
    WHERE  database_id > 4  
      AND  state = 0         -- Only online databases  
      AND  is_read_only = 0  
    ORDER BY name  
      
 OPEN dbcur  
      
 WHILE 1 = 1  
 BEGIN  
    FETCH dbcur INTO @db  
    IF @@fetch_status <> 0  
       BREAK  
      
    SELECT @sp_executesql = @db + '.sys.sp_executesql'  
      
    EXEC @sp_executesql @query, N'@dbstmts nvarchar(MAX) OUTPUT, @value nvarchar(50)',   
                        @dbstmts OUTPUT, @value  
    PRINT @db  
    IF @dbstmts IS NOT NULL  
    BEGIN  
       PRINT @dbstmts  
       EXEC @sp_executesql @dbstmts, N'@value nvarchar(50)', @value  
    END  
 END  
      
 DEALLOCATE dbcur