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 %张三%
查询字符串在哪些表那些列存在
快速根据页面上显示的文字字符串定位到数据库的哪些关联表中 sqlserver 查看proc 内部代码的方法 sp_helptext procNameorFuncName 例如 sp_helptext sp_FindString 或者 sp_helptext 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
© 版权声明
文章版权归作者所有，未经允许请勿转载。
相关文章
暂无评论...
