在所有数据库的所有数据中搜索关键字

　　本例脚本旨在所有数据库的所有数据中搜索关键字，可将以下脚本直接拷贝使用，注意留意注解。本例的核心思路是用游标遍历所有数据库中所有表的所有列，用列名对关键字进行匹配，对匹配结果进行判断，然后只输出有关键字的信息。

　　在本例中，关掉了影响行数的信息，在查询结果的massage栏中，会出现一些Level 16的warning信息，大部分都是因为搜索到某些特殊数据类型的字段（XML，sql_variant，image等），不能用convert函数转换为varchar类型，此报警信息可以忽略~

　　如：Implicit conversion from data type xml to varchar is not allowed. Use the CONVERT function to run this query.

　　或者是出现了排序规则不一致的信息，比如在英文环境下，某些字段名是中文等　：

　　如：Invalid column name '?????'.

　　在使用此脚本的过程中，可以留意到脚本中有print函数被注释了，如果需要观察SQL动态语句最后的真实形态，可以重新启用print函数来比对。

drop table #Retrieve
set nocount on
Declare @UserDB varchar(200)
declare @str varchar(200)
set @str='AmazonHostReservat' --your string to be retrieved将@str的值换成要查的字符串！
create table #Retrieve(Retrieve_Info varchar(200))

Declare DBName cursor
            for
select name from sys.databases where name not in
                                                          (N'master', N'model', N'msdb', N'tempdb', N'distribution', N'DWDiagnostics', N'DWConfiguration', N'DWQueue', N'resource',N'ReportServer',N'ReportServerTempDB')


--Note：若可以明确所查关键字在哪个数据库（比如A）中，可以将上一行where name not in
--(N'master', N'model', N'msdb', N'tempdb', N'distribution', N'DWDiagnostics', N'DWConfiguration', N'DWQueue', --N'resource',N'ReportServer',N'ReportServerTempDB')改为where name in ('A')


open DBName;
fetch next from DBName into @UserDB;
while @@FETCH_STATUS=0
begin
    Declare @column_sql varchar(1000)
    set @column_sql=
    'DECLARE columnCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
     table_name + '']'' as tableName,''['' + table_catalog + ''].['' + table_schema + ''].['' +
     table_name + ''].['' + column_name + '']'' as columnName FROM [' + @UserDB + '].INFORMATION_SCHEMA.columns a
     where a.table_NAME in (select name from '+quotename(@UserDB,'[]')+'.sys.tables) and a.table_NAME <>'+'''sysdiagrams'''
    --print(@column_sql)
    exec(@column_sql)
    declare @Column_name varchar(500)
    declare @tablename varchar(500)
    open columnCursor
    fetch next from columnCursor into @tablename,@Column_name;
    while @@FETCH_STATUS=0
        begin
        declare @retrieve varchar(1000)
        declare @retrieve2 varchar(1000)
        declare @count int
        set @retrieve='declare @rowcount int;
        select '+@Column_name+' from '+@tablename+' where quotename(convert(varchar(200),'+@Column_name+'),'''') like '+QUOTENAME('%'+@str+'%','''')
        --print (@retrieve)
        insert into #Retrieve exec (@retrieve)
        select @count=count(1) from #Retrieve
        if @count>0
        begin
            set @retrieve2='select '+quotename(@tablename,'''')+' as Tablename , * from '+@tablename+' where quotename(convert(varchar(200),'+@Column_name+'),'''') like '+QUOTENAME('%'+@str+'%','''')
            --print (@retrieve2)
            exec (@retrieve2)
            delete from #Retrieve  
            fetch next from columnCursor into @tablename,@Column_name;
        end        
        else            
        fetch next from columnCursor into @tablename,@Column_name;
        end
        close columnCursor
        DEALLOCATE columnCursor
    fetch next from DBName into @UserDB;
end
        close DBName
        DEALLOCATE DBName
set nocount off   

 

转载于:https://www.cnblogs.com/LC0507/p/11456003.html