SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'sp_FindString') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE sp_FindString
GO
/*
gzt-202409
这里是sqlserver数据库
查询方式：exec sp_FindString '%张三%'
查询字符串在哪些表那些列存在
快速根据页面上显示的文字字符串定位到数据库的哪些关联表中
*/
CREATE PROCEDURE [dbo].[sp_FindString]
(
@old_string varchar(50)
)
as
begin
declare @tbname varchar(50)
declare tbroy cursor for
select name from sysobjects where xtype= 'U ' --第一个游标遍历所有的表
open tbroy
fetch next from tbroy into @tbname
while @@fetch_status=0
begin
declare @colname varchar(500)
declare colroy cursor for select name from syscolumns where id=object_id(@tbname) and xtype in
(select xtype from systypes where name in ( 'varchar', 'nvarchar', 'char', 'nchar', 'text', 'int'
, 'decimal', 'float', 'int', 'money')) --第二个游标是第一个游标的嵌套游标，遍历某个表的所有字段
open colroy
fetch next from colroy into @colname
while @@fetch_status=0
begin
if(@colname=@old_string)
begin
print '包含有列的表'+ @colname +'包含有列的表'+@tbname
end
declare @sql nvarchar(1000),@j int
select @sql= 'select @i=count(1) from ' + '[' + @tbname + ']' + ' where '+ '[' + @colname + ']'+ ' like '+ ''''+ @old_string + ''''
--select @sql
exec dbo.sp_executesql @sql,N'@i int output', @i = @j output --输出满足条件表的记录数
if @j> 0
begin
select 包含字串的表名 = @tbname ,包含字串的列名 = @colname
select @sql = 'select ' + @colname + ',* from ' + @tbname + ' where '+ @colname + ' like '+ '''' + @old_string + ''''
print @sql
end
fetch next from colroy into @colname
end
close colroy
deallocate colroy
fetch next from tbroy into @tbname
end
close tbroy
deallocate tbroy
end