mssql 检索包含指定字符串的数据表及字段名称

Create  proc   [dbo].[sp__search]   
@str   Nvarchar(1000)   --要搜索的字符串   
as   
if   @str=''   return   

declare   @s   Nvarchar(4000)   
create   table   #t(表名   sysname,字段名   sysname)   

declare   tb   cursor   local   for   
select   s='if   exists(select   1   from   ['+replace(b.name,']',']]')+']   where    ['+a.name+']   like   N''%'+@str+'%'')   
print   ''所在的表及字段:   ['+b.name+'].['+a.name+']'''   
from   syscolumns   a   join   sysobjects   b   on   a.id=b.id   
where   b.xtype='U'   and   a.status>=0   
and   a.xtype   in(175,239,99,35,231,167)   
open   tb   
fetch   next   from   tb   into   @s   
while   @@fetch_status=0   
begin   
exec(@s)   
fetch   next   from   tb   into   @s   
end   
close   tb   
deallocate   tb  

希望对大家有用！

转载于:https://www.cnblogs.com/xtzhilv/archive/2011/12/24/2300324.html