Find SQL Server Tables that have two specified column names

Question

I would like to search tables in sql server for a table that has two specific column names ex (columnA and columnB). I have a query that searches on one column name

SELECT name FROM sysobjects WHERE id IN 
( SELECT id FROM syscolumns WHERE name = 'columnA' )

I don't know how to modify this to search for two columns.

Answer 1

SELECT name FROM sysobjects WHERE id IN 
( SELECT id FROM syscolumns WHERE name = 'columnA' )
and id in 
( SELECT id FROM syscolumns WHERE name = 'columnB' )

should do the trick.

Answer 2

This is the right way to do it:

select so.name 
from sysobjects so 
where so.type = 'U' -- it's a user's table
and exists (select * from syscolumns sc where sc.id = so.id and sc.name='columnA')
and exists (select * from syscolumns sc where sc.id = so.id and sc.name='columnB')

It's important to check that it's a user table. On the contrary you could find views, table valued functions and so on.