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;
上一篇: SQL 级联删除与级联更新的方法
下一篇: sql 截取域名的问题