欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-11-24 09:27:34
复制代码 代码如下:use [数据库名称]; --1.定义需要查找的关键字。在搜索中,使用模糊搜索:like '%@key_find%' declare...
复制代码 代码如下:

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;