求一SQL语句……查出表中相同的字段名

[linfe369]

两张表，相同的字段名，请问有没有SQL语句来查...谢谢

[Zoezs]

select a.name,count(1) from sys.columns a
inner join sys.indexes b
on a.object_id=b.object_id
group by a.name
having count(1)>1

[htl258_Tony]

有：

--05
SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB1')
INTERSECT
SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB2')

[Zoezs]

只能查当前库的。

[htl258_Tony]

--2K

SELECT NAME
FROM (
  SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB1')
  UNION ALL
  SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TB2')
) T
GROUP BY NAME
HAVING COUNT(1)=2

[v1ctory1216]

select a.name
from a,b
where a.name=b.name