SQL Server 2008: find all tables containing columns with specified name

Question

I know it is possible to select the tables which contain a specific column name with a query like:

SELECT t.name,
       c.name 
FROM sys.tables t 
INNER JOIN sys.columns c 
   ON c.object_id=t.object_id 
WHERE c.name like '%colname%'

Is there a way to find the tables which contain 2 or more given columns?

To give an example, what I want to find is all tables that have the specific columns: student and teacher.

Answer 1

Group by the content that is unique and count the group content in the having clause

select t.name
from sys.tables t 
inner join sys.columns c on c.object_id=t.object_id 
where c.name in ('student','teacher')
group by t.name
having count(distinct c.name) = 2