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

时间:2020-12-28 13:49:03

我们有时候会需要查询数据库中包含某字段的所有的表,去进行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