select a.name as 表名,b.name as 字段名 from sys.objects a,sys.columns b where a.object_id=b.object_id and a.type='U' and b.name='某字段'
select a.name as 表名,b.name as 字段名 from sys.objects a,sys.columns b where a.object_id=b.object_id and a.type='U' and a.name in ([表1],[表2],[表3]....[表10]) and b.name='字段名'
sqlserver2000报错:对象名 'sys.objects' 无效。
select a.name as 字段名,b.name as 所在表名 FROM syscolumns A LEFT JOIN sysobjects B ON A.id=B.id WHERE B.xtype='u' and a.name='要查询的字段名'
参考sql 2000的。 --例如查询sql server 2000的pubs库中有哪些表有au_id字段. select a.name dbname , b.name colname from sysobjects a , syscolumns b where a.id=b.id and a.xtype = 'u' and b.name = 'au_id' order by a.name /* dbname colname -------------- ----------- authors au_id titleauthor au_id(所影响的行数为 2 行) */ select object_name(id) as tabname from syscolumns where name='au_id' order by tabname/* tabname --------------------- authors titleauthor(所影响的行数为 2 行) */
select a.name as 表名,b.name as 字段名 from sys.objects a left outer join sys.columns b on b.object_id=a.object_id where a.type='U' and a.name in ('表1','表2', ...) and b.name='要查的字段'
from sys.objects a,sys.columns b
where a.object_id=b.object_id
and a.type='U'
and b.name='某字段'
from sys.objects a,sys.columns b
where a.object_id=b.object_id
and a.type='U' and a.name in
([表1],[表2],[表3]....[表10])
and b.name='字段名'
FROM syscolumns A LEFT JOIN sysobjects B ON A.id=B.id
WHERE B.xtype='u' and a.name='要查询的字段名'
--例如查询sql server 2000的pubs库中有哪些表有au_id字段. select a.name dbname , b.name colname from sysobjects a , syscolumns b where a.id=b.id and a.xtype = 'u' and b.name = 'au_id' order by a.name /*
dbname colname
-------------- -----------
authors au_id
titleauthor au_id(所影响的行数为 2 行)
*/
select object_name(id) as tabname from syscolumns where name='au_id' order by tabname/*
tabname
---------------------
authors
titleauthor(所影响的行数为 2 行)
*/
from sys.objects a
left outer join sys.columns b on b.object_id=a.object_id
where a.type='U' and a.name in ('表1','表2', ...)
and b.name='要查的字段'