Home How can we find all field names exist in several tables, and field names that exist in one table, but not in other tables?

Questions

How can we find all field names exist in several tables, and field names that exist in one table, but not in other tables?

byMR

June 14, 2022

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.

>Solution :

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‘.