我们有时候会需要查询数据库中包含某字段的所有的表，去进行update，这时就可以用下面的SQL来实现：
select object_name(id) objName,Name as colName
from syscolumns
where (name like'%此次写需要查询的字段名称%')
and id in(select id from sysobjects where xtype='u')
order by objname
;
当然也可以使用游标，把查询出来的Table串接起来，如下：
1 DECLARE @COLNAMELIKE NVARCHAR(100) 2 DECLARE @OBJNAME NVARCHAR(100) 3 DECLARE @COLNAME NVARCHAR(100) 4 DECLARE @VALUE NVARCHAR(100) 5 DECLARE @SQL NVARCHAR(MAX) 6 7 SET @COLNAMELIKE='%POLICYNUMBER%' 8 SET @VALUE='MSH-CCIC-GEP-Plan1-13530' 9 10 DECLARE CUR CURSOR FOR 11 select object_name(id) objName,Name as colName from syscolumns 12 where (name like @COLNAMELIKE) 13 and id in(select id from sysobjects where xtype='u') 14 order by objname 15 16 OPEN CUR 17 FETCH NEXT FROM CUR INTO @OBJNAME,@COLNAME 18 WHILE @@fetch_status = 0 19 BEGIN 20 SET @SQL=' SELECT * FROM + ' @OBJNAME + ' WHERE ' + @COLNAME + '=''' + @VALUE + ''' ' 21 exec @SQL 22 FETCH NEXT FROM CUR INTO @OBJNAME,@COLNAME 23 END 24 CLOSE CUR 25 DEALLOCATE CUR
如果您看了本篇博客,觉得对您有所收获，请点击右下角的 [推荐]
如果您想转载本博客，请注明出处
如果您对本文有意见或者建议，欢迎留言
感谢您的阅读，请关注我的后续博客