如果执行有某个字段的表的查询？

[rocket204]

在所有的用户表中，有‘userID’和‘sys_id’这2个字段的表，我要执行其查询，请问如何写SQL语句？
谢谢！

[ACMAIN_CHM]

我要执行其查询

是指什么？得到一个包含这两个字段的表名的列表？

[东那个升]

没说清楚。。。。

[liangCK]

SELECT * FROM sysobjects AS o
WHERE xtype = 'U' 
    AND EXISTS(SELECT id FROM syscolumns
               WHERE o.id= id AND name IN('userID','sys_id')
               GROUP BY id HAVING COUNT(*) >= 2)

[rocket204]

例如用户表中有
TABLE1 (A,B,C)
TBALE2 (USERID ,SYS_ID ,C,F)
TABLE3 (USERID,SYS_ID,HH,KK)

TABLE2和TABLE3者2个表都有USERID 和SYS_ID字段，我就要查执行
select * from TBALE2
select * from TABLE3

3L朋友的语句查出了又那个表同事有这2个字段，谢谢！

[ACMAIN_CHM]

交给 P梁 了

[yhtapmys]

[Quote=引用 5 楼 acmain_chm 的回复:]
交给 P梁 了
[/Quote]

UP

[liangCK]

SELECT * FROM sysobjects AS o 
WHERE xtype = 'U' AND EXISTS(SELECT id FROM syscolumns 
                               WHERE o.id= id AND name IN('userID','sys_id') 
                            GROUP BY id HAVING COUNT(*) >= 2)

EXEC sp_MsForeachtable @command1=N'SELECT * FROM ?',
                       @whereand=N' AND EXISTS(SELECT id FROM syscolumns 
                                        WHERE OBJECT_ID(o.name)= id AND name IN(''userID'',''sys_id'') GROUP BY id HAVING COUNT(*) >= 2)'

[--小F--]

[Quote=引用 7 楼 liangck 的回复:]
SQL code
SELECT * FROM sysobjects AS o
WHERE xtype = 'U' AND EXISTS(SELECT id FROM syscolumns
WHERE o.id= id AND name IN('userID','sys_id')
……
[/Quote]

学习

[rocket204]

[Quote=引用 6 楼 yhtapmys 的回复:]
引用 5 楼 acmain_chm 的回复:
交给 P梁 了


UP
[/Quote]


P梁 是谁？

[liaosunny]

[Quote=引用 7 楼 liangck 的回复:]
SQL code
SELECT * FROM sysobjects AS o
WHERE xtype = 'U' AND EXISTS(SELECT id FROM syscolumns
WHERE o.id= id AND name IN('userID','sys_id')
……
[/Quote]

这个