Sql server 查询数据库中包含某字段的所有的表

我们有时候会需要查询数据库中包含某字段的所有的表，去进行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串接起来，如下：

 DECLARE @COLNAMELIKE NVARCHAR()

 DECLARE @OBJNAME NVARCHAR()

 DECLARE @COLNAME NVARCHAR()

 DECLARE @VALUE NVARCHAR()

 DECLARE @SQL NVARCHAR(MAX)

 SET @COLNAMELIKE='%POLICYNUMBER%'

 SET @VALUE='MSH-CCIC-GEP-Plan1-13530'

 DECLARE CUR CURSOR FOR

 select object_name(id) objName,Name as colName from syscolumns

 where (name like @COLNAMELIKE)

 and id in(select id from sysobjects where xtype='u')

 order by objname

 OPEN CUR

 FETCH NEXT FROM CUR INTO @OBJNAME,@COLNAME

 WHILE @@fetch_status =

 BEGIN

     SET @SQL=' SELECT * FROM + ' @OBJNAME + ' WHERE ' + @COLNAME + '=''' + @VALUE + ''' '

     exec @SQL

     FETCH NEXT FROM CUR INTO @OBJNAME,@COLNAME

 END

 CLOSE  CUR

 DEALLOCATE CUR