SQL to find table containing all specified columns

Question

I have the below code to list all tables which have columns DataAreaId and CountryRegionId. This works, but requires me to change the code in two places if I amend the column list (i.e. both the name in ('DataAreaId','CountryRegionId') code to list the required column names, and also the having COUNT(1) = 2 to match the number of specified columns.

select * 
from sys.tables t 
where object_id in 
(
    select object_id
    from sys.columns c
    where name in ('DataAreaId','CountryRegionId')
    group by object_id
    having COUNT(1) = 2
)
order by Name

I can tweak it to make things more dynamic (i.e. so I only have to define the list of columns; and not have to remember to amend count(1) = 2 to match the number of values as so:

declare @cols table(name sysname) 
insert @cols values('DataAreaId'),('CountryRegionId')
select * 
from sys.tables t 
where object_id in 
(
    select object_id
    from sys.columns c
    where name in (select name from @cols)
    group by object_id
    having COUNT(1) = (select COUNT(distinct name) from @cols)
)
order by Name

But that has a bad smell to it / doesn't look elegant.

Any thoughts on how this could be improved, or is this just one of those scenarios where elegance isn't possible? I'm thinking the seldom used ALL keyword may help somehow; though not sure how.

Answer 1

In this case, I would consider using a CTE to contain the columns you are interested in. This saves having to declare the variables, and also saves the inserts, etc. The basic concept is the same as your second query though...

with FindColumns as (

    select 'DataAreaId' as Seek
  UNION
    select 'CountryRegionId' as Seek

), MyTables as (

    select object_id as Tab,
           count(*) as ColCount
    from sys.columns inner join FindColumns on name = Seek
    group by object_id

)

select *
from sys.tables
inner join MyTables on object_id = Tab
where ColCount = (select count(*) from FindColumns)

Note how this is really just a re-expression of your second query using Common Table Expressions

I put that query together on the Stack Exchange data explorer as an example using a couple of different column names.... you can see it working there.

Answer 2

Revised code based on @rolfl's suggestion of using CTE instead of Table Variable:

• Changed from union to union all; since this improves performance.
• Changed from union all to values since this gives same performance as union all but gives cleaner entry (i.e. less boilerplate code between each value)
• Changed from a second CTE to a subquery since this reduced code length / looks cleaner (to me; personal opinion).
• Changed from inner join to where object_id in to keep results to original query (i.e. only table columns returned; could have done this by select t.*, but this felt cleaner.

;with cols as (select * from (values ('DataAreaId'),('CountryRegionId')) x(name))
select * 
from sys.tables t 
where t.object_id in
(
    select object_id
    from sys.columns c
    inner join cols x
        on x.name = c.name
    group by object_id
    having count(1) = (select count(distinct name) from cols)
) 
order by Name