在服务器上的所有数据库中的所有表中搜索字符串 [英] Search all tables in all databases on server for a string

问题描述

这个问题被标记为重复，但事实并非如此.SO 上的其他答案显示了如何在单个数据库中搜索所有表，我需要在给定服务器上的每个数据库中搜索所有表.

This question was flagged as a duplicate, but it is not. The other answers on SO show how to search all tables in a single database, I need to search all tables in EVERY database on a given server.

我需要搜索服务器上所有数据库的所有表以获取搜索字符串.我的电子邮件地址散落在将要更改域的表格中，我需要准备一份报告，显示这些电子邮件地址的位置.我将无法向所有数据库添加存储过程，因此我需要一个查询来执行此操作，而不涉及重复执行 sp.我从网络中提取了这段代码，并用它来搜索所有表格，但我没有能够弄清楚如何在所有数据库上运行它.

I need to search all tables for all databases on a server for a search string. I've got email address littered throughout tables that are going to have a change of domain and I need to prepare a report that shows where these email addresses are located. I am not going to be able to add a stored procedure to all the databases so I need a query to do this that's doesn't involve exec-ing a sp repeatedly. I pulled this code off the net and was using it to search all tables but I haven't been able to figure out how to run it on all databases.

drop table #Results
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @SearchStr nvarchar(100), @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @SearchStr = '@domaintobereplaced.com'
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 top 10 ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM #Results

推荐答案

这个问题在这一点上有点老了，大多数答案似乎在搜索所有服务器和每台服务器上的所有数据库方面都没有达到目标.原来我的经理只是想给我一项不可能且耗时的任务，也就是繁忙的工作来拖延我，因为我们的团队在重组中解散，我们都被派往公司的不同部门.

This question is a bit old at this point and most of the answers seem to miss the mark in terms of searching ALL servers and ALL databases on each server. Turned out my manager was just trying to give me an impossible and time consuming task aka busy work to stall me because our team was getting disbanded in a re-org and we were all being sent to different parts in the company.

因此，为了写出全面的答案，我会说我现在的方法是创建一个控制台应用程序，该应用程序接受以逗号分隔的服务器名称列表.它将遍历所有服务器名称并一一连接到它们.一旦进入服务器，我将查询并遍历所有用户创建的数据库，并对每个数据库运行搜索所有表查询.如果找到结果，我会将它们写入存储在具有 [ServerName]_[DatabaseName]_Results 标题的目录中的文件中，然后使用输出的文件列表来查看和制定进一步查询/修复的行动计划.从上面的答案看来，SQL Server 中没有办法做到这一点.

So, for the sake of writing a comprehensive answer I will say my approach now would be to create a console application that accepts a comma separated list of server names. It would loop through all the server names and connect to them one by one. Once in a server I would query for and loop through all the user created databases and run the search all tables query on each one. If results were found I would write them to a file stored in a directory with a [ServerName]_[DatabaseName]_Results title and then use the outputted list of files to review and develop and action plan for further querying/remediation. It doesn't appear from the answers above that there is a way to do this in SQL Server.

这篇关于在服务器上的所有数据库中的所有表中搜索字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！