SQL Server：在所有数据库中搜索字符串，并列出所有数据库，表和对应的列 [英] SQL Server : Search for a string across all the databases and list all the databases, tables and corresponding columns

问题描述

我想知道是否有一种方法可以查看SQL Server 2012中存在特定字符串的所有数据库和对应表。

I am wondering if there is a way I can view all the databases and the corresponding tables where a specific string is present in SQL Server 2012.

我能够使用存储过程在特定数据库中的所有表中搜索字符串，我在下面找到了该存储过程。但是我想在所有数据库中搜索它。

I was able to search for a string in all the tables in a particular database by using a stored procedure below which I found online. But I would like to search it across all the databases.

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

    CREATE TABLE #Results (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', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL

            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + 'WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results
END

当我执行以下查询时，它将列出所有表

And when I execute the below query, it lists all the tables along with the column names.

exec SearchAllTables "B2"

输出：

     ColumnName              ColumnValue
[dbo].[msm_temp].[molecule_nm]  B2
[dbo].[msm_temp1].[molecule_nm] B2
[dbo].[msm_temp2].[molecule_nm] B2
[dbo].[msm_temp3].[molecule_nm] B2
[dbo].[msm_temp4].[molecule_nm] B2

如何解决问题？

推荐答案

您可以使用此存储过程搜索值如下：

You can use this stored procedure with value for search like this :

exec SearchAllDatabases @SearchTerm = '%B2%'

这可以帮助您在任何地方搜索，但是如果数据库中的数据过多，则需要花费一些时间来执行。

This can help you to search everywhere but if you have too much data in your databases this will take time to execute.

存储过程：

  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

希望对您有所帮助。

这篇关于SQL Server：在所有数据库中搜索字符串，并列出所有数据库，表和对应的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！