复制代码 代码如下:
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;