在所有数据库，所有列和所有表中搜索字符串（SQL Server 2008 R2） [英] Search for a string in all databases, all columns, and all tables (SQL Server 2008 R2)

问题描述

我们在SQL Server中遭受了一些入侵。

我试图在每个数据库中，在每个表中，每列都找到 abortion c $ c> cheat 。

我可以用这个查询，但在一个单一的数据库。

   - 将结果存储在本地临时表中。我使用
  - 本地临时表，以便我可以在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'; 
 
  - 在数据库中的所有列上创建一个游标
为
声明c游标SELECT 
 DB_NAME（DB_ID（））为DBName，tbls.TABLE_NAME，cols .COLUMN_NAME 
 FROM INFORMATION_SCHEMA.TABLES AS tbls 
 JOIN INFORMATION_SCHEMA.COLUMNS作为cols ON tbls.TABLE_NAME = cols.TABLE_NAME 
 
  - 对于每个表和列对，查看搜索值存在。 
 open c 
 
从c获取下一个到@db，@tbl，@col 
 
 while @@ FETCH_STATUS = 0 
 begin 
 - 在当前表列中查找搜索键，如果找到，则将其添加到结果中。 
 SET @q ='INSERT INTO #tmp SELECT'''+ @ db +'''，'''+ @tbl +'''，'''+ @col +'''，'+ @col +'FROM'+ @tbl +'WHERE'+ @col +'LIKE''％'+ @search +'％'''
 EXEC SP_EXECUTESQL @q 
从c获取下一个到@db， @tbl，@col 
 end 
 close c 
 deallocate c 
 
  - 获取结果
 select distinct db，tbl，col from #tmp 
 
  - 删除本地临时表。 
 drop table #tmp 
  

如何找到这些字符串？结果集应为：

  DATABASE | TABLE | COLUMN 
  

我不需要结果（文本字段），我需要 select distinct 用于表和列，因为在同一个表/列中会有很多 abortion 。

解决方案

使用未记录的 sp_msforeachdb 一般不鼓励，我的直觉是将您现有的代码发送到此过程，如下所示：

  exec sp_MSforeachdb'USE [？]; 
  - 将结果存储在本地临时表中。我使用
  - 本地临时表，以便我可以在SP_EXECUTESQL中访问它。 
 create table #tmp（
 db varchar（max），
 tbl nvarchar（max），
 col nvarchar（max），
 val nvarchar b 
 $ b）; 
 
 declare @db nvarchar（max）; 
 declare @tbl nvarchar（max）; 
 declare @col nvarchar（max）; 
 declare @q nvarchar（max）; 
 
 ------------------------------------------ -------------------------------------------------- 
 declare @search nvarchar（max）=''abortion''; 
 ---------------------------------------------- ---------------------------------------------- 
 
 
  - 在数据库中的所有列上创建一个游标
为
声明c游标选择DB_NAME（DB_ID（））作为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 
 
  - 对于每个表和列对，查看是否存在搜索值。 
 open c 
从c中获取下一个到@db，@tbl，@col 
 while @@ FETCH_STATUS = 0 
 begin 
  - 查找搜索键当前表列，如果找到，则将其添加到结果中。 
 SET @q ='INSERT INTO #tmp SELECT''''''+ @ db +''''''，'''''''+ @tbl +'''''' ''''+ @col +'''''''，''+ @col +''FROM''+ @tbl +''WHERE''+ @col +''LIKE''''％'' @search +''％''''''
 EXEC SP_EXECUTESQL @q 
从c访存到@db，@tbl，@col 
 end 
 close c 
 deallocate c;'
  

这里唯一添加的代码是第一行，代码只是确保用''替换'。在 USE [？] 中的？是一个特殊字符，表示sp_MSforeachdb执行循环中当前活动的数据库。 p>

We suffered some kind of invasion in our 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.

解决方案

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:

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.

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