SQL to find table containing all specified columns 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.
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.