Finding All Tables with specific Column Names in SQL Server

Recently I got a request from our test department to find all tables that did not contain a specific column name. For example if there are three tables defined with the following fields and you only want to return the name of the table without the field PKint in this case table2.

Create table table1
(pkint int,
Charfield varchar(10))

Create table table2
(fkint int.
Charfiedl varchar(10))

Create table table3
(pkint int,
Charfield2 varchar(10))


The following query leverages DMV’s introduced in 2005 to return the results.

select t.name from sys.tables t
where t.name not in(
select t.name from sys.tables t
inner join sys.columns c
on
c.object_id = t.object_id
where c.name = 'pkint')


If you want to go in the reverse direction that is finding all tables with the name pkint you simply change the “not in” to “in”

select t.name from sys.tables t
where t.name in(
select t.name from sys.tables t
inner join sys.columns c
on
c.object_id = t.object_id
where c.name = 'pkint')