SQL查询数据库中包含指定字符串的相关表和相关记录记录⼀下，以后备⽤；查询结果第⼀张表为包含字符串的相关表数据，第⼆章为表名，以此类推
1/*
2查询数据库中包含指定字符串的数据表名及相应记录
3*/
4USE[数据库]
5GO
6
7DECLARE@key VARCHAR(30)
8SET@key='查的内容'--替换为要查的字符串
9DECLARE@tabName VARCHAR(40),@colName VARCHAR(40)
10DECLARE@sql VARCHAR(2000)
11DECLARE@tsql VARCHAR(8000)
12
13DECLARE tabCursor CURSOR FOR
14SELECT name FROM sysobjects WHERE xtype ='u'AND name <>'dtproperties'
15OPEN tabCursor
16FETCH NEXT FROM tabCursor INTO@tabName
17WHILE@@fetch_status=0
18BEGIN
19SET@tsql=''
20DECLARE colCursor CURSOR FOR
21SELECT Name FROM SysColumns WHERE id=Object_Id(@tabName) and xtype=167
22OPEN colCursor
23FETCH NEXT FROM colCursor INTO@colName
24WHILE@@fetch_status=0
25BEGIN
26SET@sql='if(exists(select * from '+@tabName+' where '
27SET@sql=@sql+@colName+' like ''%'+@key+'%'')) begin  select * from '
28SET@sql=@sql+@tabName+' where '+@colName+' like ''%'+@key+'%'';select '''
29+@tabName+''' as TableName  end'
30SET@tsql=@tsql+@sql+';'
31
32FETCH NEXT FROM colCursor INTO@colName
字符串截取 sql
33END
34EXEC(@tsql)
35CLOSE colCursor
36DEALLOCATE colCursor
37
38FETCH NEXT FROM tabCursor INTO@tabName
39END
40CLOSE tabCursor
41DEALLOCATE tabCursor

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。