全库搜索某个内容的sql
DECLARE @what varchar(800) SET @what='联系' --要搜索的字符串 DECLARE @sql varchar(8000) DECLARE TableCursor CURSOR LOCAL FOR SELECT sql='IF EXISTS ( SELECT 1 FROM ['+o.name+'] WHERE ['+c.name+'] LIKE ''%'+@what+'%'' ) PRINT ''所在的表及字段:['+o.name+'].['+c.name+']''' FROM syscolumns c JOIN sysobjects o ON c.id=o.id -- 175=char 56=int 可以查 select * from sys.types WHERE o.xtype='U' AND c.status>=0 AND c.xusertype IN (175, 239, 231, 167 ) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @sql WHILE @@FETCH_STATUS=0 BEGIN EXEC( @sql ) FETCH NEXT FROM TableCursor INTO @sql END CLOSE TableCursor -- 删除游标引用 DEALLOCATE TableCursor
作者:沐雪
文章均系作者原创或翻译,如有错误不妥之处,欢迎各位批评指正。本文版权归作者和博客园共有,如需转载恳请注明。
如果您觉得阅读这篇博客让你有所收获,请点击右下方【推荐】
为之网-热爱软件编程 http://www.weizhi.cc/