How can we find all field names exist in several tables, and field names that exist in one table, bu-CodePudding

I have some basic DDL here.

--Drop Table tableA
--Drop Table tableB
--Drop Table tableC

Create table dbo.tableA
(
Id int, 
Name varchar(100),
Code varchar(5),
Address varchar(100),
RegDate datetime,
AddedBy varchar(50)
)

Create table dbo.tableB
(
Id int, 
Name varchar(100),
KeyCode varchar(5),
Address varchar(100),
RegDate datetime,
AddedBy varchar(50)
)

Create table dbo.tableC
(
Id int, 
FName varchar(100),
LName varchar(100),
Address varchar(100),
)

select * from tableA
select * from tableB
select * from tableC

Now, this should tell me what field names are common to all three tables.

select
   syscolumns.name as [Column]
from 
   sysobjects 
inner join 
   syscolumns on sysobjects.id = syscolumns.id
where sysobjects.xtype = 'u'
and   sysobjects.name = 'tableA' 
INTERSECT
select
   syscolumns.name as [Column]
from 
   sysobjects 
inner join 
   syscolumns on sysobjects.id = syscolumns.id
where sysobjects.xtype = 'u'
and   sysobjects.name = 'tableB' 
INTERSECT
select
   syscolumns.name as [Column]
from 
   sysobjects 
inner join 
   syscolumns on sysobjects.id = syscolumns.id
where sysobjects.xtype = 'u'
and   sysobjects.name = 'tableC'

Number 1) Is there an easier way to do this, with less code. It would be hard to scale out to multiple tables.

Number 2) How can I tell what field name is in one table, but not in the others? I tried using the EXCEPT clause and the results look, well, very inaccurate.

CodePudding user response：

Do a GROUP BY, use the HAVING clause to decide 1 or 3 table columns.

select
   syscolumns.name as [Column]
from 
   sysobjects 
inner join 
   syscolumns on sysobjects.id = syscolumns.id
where sysobjects.xtype = 'u'
and   sysobjects.name IN ('tableA', 'tableB', 'tableC')
GROUP BY syscolumns.name
HAVING COUNT(*) = 1;  -- 1 for unique column names,
                      -- 3 for 3 table column names

Note: Aaron Bertrand says 'you should use the newer catalog views, e.g. sys.columns, sys.objects, etc'.