在所有数据库，所有列和所有表中搜索字符串（SQL Server 2008 R2）

We suffered some kind of invasion in our SQL Server.

我们的SQL Server遇到了某种入侵。

I'm trying to find in every database, in every table, every column the word abortion and cheat.

我试图在每个数据库，每个表格，每列中找到堕胎和欺骗这个词。

I can do this with this query, but in a single database.

我可以使用此查询执行此操作，但在单个数据库中。

-- Store results in a local temp table so that.  I'm using a
-- local temp table so that I can access it in SP_EXECUTESQL.
create table #tmp 
(
    db varchar(max),
    tbl nvarchar(max),
    col nvarchar(max),
    val nvarchar(max),
);

declare @db nvarchar(max);
declare @tbl nvarchar(max);
declare @col nvarchar(max);
declare @q nvarchar(max);

declare @search nvarchar(max) = 'abortion';

-- Create a cursor on all columns in the database
declare c cursor for
    SELECT 
        DB_NAME(DB_ID()) as DBName, tbls.TABLE_NAME, cols.COLUMN_NAME  
    FROM INFORMATION_SCHEMA.TABLES AS tbls
    JOIN INFORMATION_SCHEMA.COLUMNS AS cols ON tbls.TABLE_NAME = cols.TABLE_NAME

-- For each table and column pair, see if the search value exists.
open c

fetch next from c into @db, @tbl, @col

while @@FETCH_STATUS = 0
begin
    -- Look for the search key in current table column and if found add it to the results.
    SET @q = 'INSERT INTO #tmp SELECT ''' +@db+''',''' + @tbl + ''', ''' + @col + ''', ' + @col + ' FROM ' + @tbl + ' WHERE ' + @col + ' LIKE ''%' + @search + '%'''
    EXEC SP_EXECUTESQL @q
    fetch next from c into @db, @tbl, @col
end
close c
deallocate c

-- Get results
select distinct db,tbl,col  from #tmp

-- Remove local temp table.
drop table #tmp

How can I find these strings? The result set should be:

我怎样才能找到这些字符串？结果集应该是：

DATABASE | TABLE | COLUMN

I don't need the result ( text field ), and I need to select distinct for tables and columns, because it will be a lot of abortion in the same table/column.

我不需要结果（文本字段），我需要为表和列选择不同的，因为它会在同一个表/列中进行大量的流产。

1 个解决方案

#1

While the use of the undocumented sp_msforeachdb is generally not encouraged, my instinct would be to send your existing code to this procedure like this:

虽然通常不鼓励使用未记录的sp_msforeachdb，但我的直觉是将现有代码发送到此过程，如下所示：

exec sp_MSforeachdb 'USE [?]; 
-- Store results in a local temp table so that.  I'm using a
-- local temp table so that I can access it in SP_EXECUTESQL.
create table #tmp (
 db varchar(max)   ,
    tbl nvarchar(max),
    col nvarchar(max),
    val nvarchar(max),

);

declare @db nvarchar(max);
declare @tbl nvarchar(max);
declare @col nvarchar(max);
declare @q nvarchar(max);

--------------------------------------------------------------------------------------------
declare @search nvarchar(max) = ''abortion'';
--------------------------------------------------------------------------------------------


-- Create a cursor on all columns in the database
declare c cursor for
SELECT DB_NAME(DB_ID()) as DBName,tbls.TABLE_NAME, cols.COLUMN_NAME  FROM INFORMATION_SCHEMA.TABLES AS tbls
JOIN INFORMATION_SCHEMA.COLUMNS AS cols
ON tbls.TABLE_NAME = cols.TABLE_NAME

-- For each table and column pair, see if the search value exists.
open c
fetch next from c into @db, @tbl, @col
while @@FETCH_STATUS = 0
begin
    -- Look for the search key in current table column and if found add it to the results.
    SET @q = ''INSERT INTO #tmp SELECT '''''' +@db+'''''','''''' + @tbl + '''''', '''''' + @col + '''''', '' + @col + '' FROM '' + @tbl + '' WHERE '' + @col + '' LIKE ''''%'' + @search + ''%''''''
    EXEC SP_EXECUTESQL @q
    fetch next from c into @db, @tbl, @col
end
close c
deallocate c;'

The only added code here is the first line, for the rest of the code just make sure to replace ' with ''. The ? in USE [?] is a special character meaning the currently active database in the loop sp_MSforeachdb executes.

这里唯一添加的代码是第一行，对于其余的代码，只需确保替换'with''。的？ in USE [？]是一个特殊字符，表示循环sp_MSforeachdb中当前活动的数据库执行。