Search a value in all database

Didiiolu 96 Reputation points
2020-12-23T00:08:26.103+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP
    2020-12-23T23:26:23.337+00:00

    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
    
    1 person found this answer helpful.
    0 comments No comments

  2. EchoLiu-MSFT 14,571 Reputation points
    2020-12-25T08:47:32.18+00:00

    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


  3. SQLZealots 276 Reputation points
    2020-12-25T13:35:16.777+00:00

    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

    0 comments No comments

  4. Joerg 62 116 Reputation points
    2020-12-26T10:50:12.253+00:00

    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  
    

  5. Joerg 62 116 Reputation points
    2020-12-26T10:50:15.523+00:00

    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  
    
    0 comments No comments