sql server 2012 数据库所有表里查找某字符串的方法

use [数据库名称];

 --1.定义需要查找的关键字。在搜索中，使用模糊搜索：like '%@key_find%'
 declare @key_find nvarchar(max) = '123';--假设是找字符串"123"

 --2.用游标cursor_table，遍历所有表
 declare cursor_table cursor for
     select name from sysobjects where xtype = 'u' and name <> 'dtproperties';
 open cursor_table;
 declare @tablename nvarchar(max);
 fetch next from cursor_table into @tablename;
 while @@fetch_status = 0
 begin
     declare @tempsqltext nvarchar(max) = '';

     --3.在表中，用游标columncursor，遍历所有字段。注意，只遍历字符串类型的字段（列）
     declare columncursor cursor for
         select name from syscolumns where id = object_id( @tablename ) and
                                                                             (
                                                                                 xtype = 35 or --text
                                                                                 xtype = 99 or --ntext
                                                                                 xtype = 167 or --varchar
                                                                                 xtype = 175 or --char
                                                                                 xtype = 231 or --nvarchar
                                                                                 xtype = 239 or --nchar
                                                                                 xtype = 241 --xml
                                                                             )
     open columncursor;
     declare @columnname nvarchar(max);
     fetch next from columncursor into @columnname;
     while @@fetch_status = 0
     begin

         --4.在表的字段中，对每一行进行模糊搜索，并输出找到的信息。
         declare @dynamicsqltext nvarchar(max) = 'if ( exists ( select * from [' + @tablename + '] where [' + @columnname + '] like ''%' + @key_find + '%'' ) ) begin declare @currenttablecount bigint = ( select count(*) from [' + @tablename + '] ); print ''find : table [' + @tablename + '], column [' + @columnname + '], row count:'' + cast( @currenttablecount as nvarchar(max) ) + ''.'';  end';
         exec( @dynamicsqltext );
         fetch next from columncursor into @columnname
     end
     exec(@tempsqltext);
     close columncursor;
     deallocate columncursor;
     fetch next from cursor_table into @tablename;
 end
 close cursor_table;
 deallocate cursor_table;