Search all databases for value

Question

Consider have a table like named : People

| Id    | Name    | Code   |
| 1     | John    | 857    |
| 2     | Mike    | 893    |
| 3     | Sara    | 935    |

This table is in PeopleDb Database

Now I want to find 'Mike' keyword. Situation :

I don't know to search in which database, tables.
I need a query that searches in all databases and tables and shows me this :

| Id  | DatabaseName  | TableName  | ColumnName  | Pk  | SearchValue
| 1   | 'PeopleDb'    | 'People'   | 'Name'      | 2   | 'Mike'

I don't know how to write the query to search in all databases and tables.

Any help will be greatly appreciated.

Edit :

Speed is not an issue here and I need to do this.

I tried this query, I want the same but that searches all databases.

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi.All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

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
            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
            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
END

Update :

I need a working T-Sql that searches all databases, tables, columns, all type of variables. Current answers will not work in some situations like Connection String in nvarchar field.

I just want this as a t-sql query to run it myself in sql server. — Mohammad Reza Shahrestani, Apr 13 '17 at 07:02
Try to find your answer here: http://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database — Evgeny, Apr 13 '17 at 07:06
I think that knowledge of schema is basic think that you need to know about you DB. That kind of universal query can have really negative impacts on your enviroment... Consider Db with hunderds of tables an bilions of rows... In this case you need search trough all string columns ... — Matej Hlavaj, Apr 13 '17 at 07:20
You are right but in this case results are more important than speed of query. We need to find the value in all databases and it's a very important situation. — Mohammad Reza Shahrestani, Apr 13 '17 at 07:22

score 2 · Accepted Answer · answered Apr 13 '17 at 11:03

I have updated your Logic as per below and it works fine, please have a look:

DECLARE @SearchStr VARCHAR(50)='Surat'

DECLARE @Results TABLE(DatabaseName NVARCHAR(500),  TableName nvarchar(370),ColumnName nvarchar(370), ColumnValue nvarchar(3630),PrimaryKey nvarchar(200), PrimaryKeyValue nvarchar(4000))

SET NOCOUNT ON

DECLARE @TableList AS Table
(
    TableName VARCHAR(500),
    RowNo INT
)

DECLARE @ColumnList AS Table
(
    ColumnName VARCHAR(500),
    RowNo INT
)

DECLARE @PrimaryKeyList AS Table
(
    PrimaryKeyName VARCHAR(500)
)

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @PrimaryKey nvarchar(200), @CurrentTableName nvarchar(256)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')

DECLARE @DatabaseCount INT=0, @Index INT=0, @DatabaseName NVARCHAR(500), @TotalColumnCount INT, @ColumnIndex INT=0, @TotalTableCount INT, @TableIndex INT=0

SELECT 
    *,
    ROW_NUMBER() OVER (ORDER BY name) AS RowNo 
INTO #tblDatabases 
FROM Sys.Databases
WHERE name NOT IN ('master','model','msdb','tempdb')

SELECT @DatabaseCount=COUNT (*) FROM #tblDatabases
WHILE @Index<@DatabaseCount
BEGIN
    SET @Index=@Index+1
    SELECT @DatabaseName='',@TableIndex=0,@ColumnIndex=0,@TableName='',@ColumnName=''
    SELECT @DatabaseName=name FROM #tblDatabases WHERE RowNo=@Index     

    DELETE FROM @TableList
    INSERT INTO @TableList
    EXEC('
            SELECT QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME),
                ROW_NUMBER() OVER (ORDER BY TABLE_NAME)
            FROM ['+@DatabaseName+'].INFORMATION_SCHEMA.TABLES
            WHERE TABLE_TYPE = ''BASE TABLE''
        ')

    SELECT @TotalTableCount=COUNT(*) FROM @TableList

    WHILE @TableIndex<@TotalTableCount 
    BEGIN   

        SET @TableIndex=@TableIndex+1
        SELECT @ColumnName = '',@ColumnIndex=0

        SELECT @TableName=TableName FROM @TableList  WHERE RowNo=@TableIndex            
        SET @CurrentTableName=REPLACE(REPLACE(REPLACE(@TableName,'[dbo].',''),'[',''),']','')

        DELETE FROM @ColumnList
        INSERT INTO @ColumnList
        EXEC('SELECT
                COLUMN_NAME,ROW_NUMBER() OVER (ORDER BY COLUMN_NAME)
                    FROM ['+@DatabaseName+'].INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = PARSENAME('''+@TableName+''', 2)
                    AND TABLE_NAME = PARSENAME('''+@TableName+''', 1)
                    AND DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')


        ')

        SELECT @TotalColumnCount=COUNT(*) FROM @ColumnList

        WHILE @ColumnIndex<@TotalColumnCount
        BEGIN
            SET @ColumnIndex=@ColumnIndex+1
            SET @ColumnName=''
            SELECT @ColumnName=ColumnName FROM @ColumnList WHERE RowNo=@ColumnIndex         

            DELETE FROM @PrimaryKeyList
            INSERT INTO @PrimaryKeyList
            EXEC('
                    SELECT Col.Column_Name from 
                        ['+@DatabaseName+'].INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
                        ['+@DatabaseName+'].INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
                    WHERE 
                        Col.Constraint_Name = Tab.Constraint_Name
                        AND Col.Table_Name = Tab.Table_Name
                        AND Constraint_Type = ''PRIMARY KEY''
                        AND Col.Table_Name= '''+@CurrentTableName+'''           
                ')

            SELECT @PrimaryKey=''
            SELECT @PrimaryKey=PrimaryKeyName FROM @PrimaryKeyList
            SET @PrimaryKey=ISNULL(@PrimaryKey,'')

            IF @ColumnName IS NOT NULL AND @PrimaryKey<>''
            BEGIN   
                INSERT INTO @Results
                EXEC
                (
                    'SELECT '''+@DatabaseName+''','''+@CurrentTableName+''',''' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) , '''+@PrimaryKey+''', [' + @PrimaryKey + '] 
                    FROM ['+@DatabaseName+'].' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END
    END
END


SELECT ROW_NUMBER() OVER (ORDER BY DatabaseName) AS Id,DatabaseName,TableName,ColumnName, ColumnValue AS SearchValue,PrimaryKeyValue AS Pk, PrimaryKey  FROM @Results

DROP TABLE #tblDatabases

This one works fine but does not work in some situations : I have a column with a connection string value : Server=localhost;DataBase=Propanel;UID=sa;pwd=silver;multipleactiveresultsets=True. When I search 'sil', 'silver', 'pwd' or something like that query can not find this one for me ? — Mohammad Reza Shahrestani, Apr 13 '17 at 12:12
I have use your value in one column and search by 'sil' and it work for me — Sandip - Frontend Developer, Apr 13 '17 at 12:21
Ok bro thank you <3. I will choose ur answer as accepted best answer until now. — Mohammad Reza Shahrestani, Apr 13 '17 at 15:07

Cai Yongji · Answer 2 · 2017-04-13T09:06:31.180

1

SELECT * FROM all_objects where object_name = 'XXXX';

edited Apr 13 '17 at 09:06

answered Apr 13 '17 at 07:08

Cai Yongji

349
1
8

Not working for me, what am I missing ? Invalid object name 'all_objects'. – Mohammad Reza Shahrestani Sep 26 '19 at 06:14

score 1 · Answer 3 · answered Apr 26 '17 at 09:32

you can use this Store Procedure

    CREATE PROCEDURE dbo.SearchAllDatabases
  @SearchTerm NVARCHAR(255) = NULL
AS
BEGIN
  SET NOCOUNT ON;
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  IF @SearchTerm IS NULL OR @SearchTerm NOT LIKE N'%[^%^_]%'
  BEGIN
    RAISERROR(N'Please enter a valid search term.', 11, 1);
    RETURN;
  END

  CREATE TABLE #results
  (
    [database]   SYSNAME,
    [schema]     SYSNAME,
    [table]      SYSNAME, 
    [column]     SYSNAME,
    ExampleValue NVARCHAR(1000)
  );

  DECLARE
    @DatabaseCommands  NVARCHAR(MAX) = N'', 
    @ColumnCommands    NVARCHAR(MAX) = N'';

  SELECT @DatabaseCommands = @DatabaseCommands + N'
    EXEC ' + QUOTENAME(name) + '.sys.sp_executesql 
        @ColumnCommands, N''@SearchTerm NVARCHAR(MAX)'', @SearchTerm;'
    FROM sys.databases 
    WHERE database_id  > 4  -- non-system databases  
      AND [state]      = 0  -- online 
      AND user_access  = 0; -- multi-user

    SET @ColumnCommands = N'DECLARE @q NCHAR(1),
          @SearchCommands NVARCHAR(MAX);

    SELECT @q = NCHAR(39), 
      @SearchCommands = N''DECLARE @VSearchTerm VARCHAR(255) = @SearchTerm;'';

    SELECT @SearchCommands = @SearchCommands + CHAR(10) + N''

      SELECT TOP (1)
        [db]     = DB_NAME(),
        [schema] = N'' + @q + s.name + @q + '', 
        [table]  = N'' + @q + t.name + @q + '',
        [column] = N'' + @q + c.name + @q + '',
        ExampleValue = LEFT('' + QUOTENAME(c.name) + '', 1000) 
      FROM '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
      WHERE '' + QUOTENAME(c.name) + N'' LIKE @'' + CASE 
        WHEN c.system_type_id IN (35, 167, 175) THEN ''V'' 
        ELSE '''' END + ''SearchTerm;'' 

    FROM sys.schemas AS s
    INNER JOIN sys.tables AS t
    ON s.[schema_id] = t.[schema_id]
    INNER JOIN sys.columns AS c
    ON t.[object_id] = c.[object_id]
    WHERE c.system_type_id IN (35, 99, 167, 175, 231, 239)
      AND c.max_length >= LEN(@SearchTerm);

    PRINT @SearchCommands;
    EXEC sys.sp_executesql @SearchCommands, 
      N''@SearchTerm NVARCHAR(255)'', @SearchTerm;';

  INSERT #Results
  (
    [database],
    [schema],
    [table],
    [column],
    ExampleValue
  )
  EXEC [master].sys.sp_executesql @DatabaseCommands, 
    N'@ColumnCommands NVARCHAR(MAX), @SearchTerm NVARCHAR(255)', 
    @ColumnCommands, @SearchTerm;

  SELECT [Searched for] = @SearchTerm;

  SELECT [database],[schema],[table],[column],ExampleValue 
    FROM #Results 
    ORDER BY [database],[schema],[table],[column];
END
GO

Thank you this works fine :). But when u need to search for a substring you should use % sign before and after it. It's cool btw. — Mohammad Reza Shahrestani, Apr 26 '17 at 10:31

score 0 · Answer 4 · answered Apr 13 '17 at 07:22

This seems like a crazy thing to do - the search could completely kill your server. That said, if this is a one-off/occasional use manual run kind of thing then you can create a script to generate the SQL commands you need and then execute the result sets. I've done this sort of thing before to refactor columns in a database. Your script would need to first run a query to get the names of all the DBs on your sql-server instance

  SELECT * FROM sys.databases

Then create dynamic sql including the USE command to specify the DB and use the information_schema.tables and .columns to get the names of all the tables and columns you want to search - up to you how you decided to select the columns to be searched (is it just columns like N'%name%' for example.

Then use this information in a for loop to create individual table/column searches. For efficiency (and to help you debug if it's not working) I suggest utilise @table variables.

Finally select the contents of any @tables holding the table queries you've created.

This output can then be run on the server - providing you've included the use commands or prefixed table names with [dbname].[schema].

I think you made this too complicated. – Mohammad Reza Shahrestani Sep 26 '19 at 07:29 — Mohammad Reza Shahrestani, Sep 26 '19 at 07:29

Search all databases for value

4 Answers4

Linked