CODEWITHSUNDEEP
sql-servert-sql
Ronald McDonald
Ronald McDonald

Reputation: 2911

Find SQL Server Tables that have two specified column names

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.

Upvotes: 8

Views: 12058

Answers (3)

JotaBe
JotaBe

Reputation: 39025

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.

Upvotes: 6

Chriseyre2000
Chriseyre2000

Reputation: 2053

Try something like:

select syscolumns.id, sysobjects.name from syscolumns 
  join sysobjects so on sysobjects.id = syscolumns.id
 where exists (select 1 from syscolumns sc where sc.id = syscolumns.id and name = 'columnA') 
   and exists (select 1 from syscolumns sc2 where sc2.id = syscolumns.id and name = 'columnB')

Upvotes: 2

Frank Schmitt
Frank Schmitt

Reputation: 30775

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.

Upvotes: 15

Related Questions