Return tables that have certain column names

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Return tables that have certain column names

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-28 : 10:56:56

Hi all,

From the INFORMATION_SCHEMA.TABLES view I want to return the TABLE_NAME of tables that have columns say, named Email and EmailStatusId. Is it possible to do this with a single select statement or would I have to use two selects for this?

Please advise.

Thanks in advance.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-28 : 11:00:16

Do you want to select table names having both columns or either of them?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-28 : 11:03:03

quote:
Originally posted by harsh_athalye

Do you want to select table names having both columns or either of them?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Hi Harsh,

I want both. Either of them would be easy.

This is what I've come up with so far :


select distinct TABLE_NAME 
from INFORMATION_SCHEMA.COLUMNS as a
where exists (select 1
		from INFORMATION_SCHEMA.COLUMNS as b
		where a.TABLE_NAME = b.TABLE_NAME
		and COLUMN_NAME = 'PersonId')
and exists (select 1
		from INFORMATION_SCHEMA.COLUMNS as b
		where a.TABLE_NAME = b.TABLE_NAME
		and COLUMN_NAME = 'DatabaseId')

Is there a better more elegant method to achieve the same?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 11:06:03

[code]SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('PersonID', 'DatabaseID')
GROUP BY TABLE_NAME
HAVING COUNT(DISTINCT COLUMN_NAME) = 2[/code]

E 12°55'05.25"
N 56°04'39.16"

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-28 : 11:09:01

Peso you son of a gun! You never fail me. Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 11:35:02

You can take the DISTINCT thingy out. A table can't have two equal column names.

SELECT		TABLE_NAME
FROM		INFORMATION_SCHEMA.COLUMNS
WHERE		COLUMN_NAME IN ('PersonID', 'DatabaseID')
GROUP BY	TABLE_NAME
HAVING		COUNT(*) = 2

E 12°55'05.25"
N 56°04'39.16"

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-28 : 11:38:02

quote:
Originally posted by Peso

You can take the DISTINCT thingy out. A table can't have two equal column names.
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('PersonID', 'DatabaseID')
GROUP BY TABLE_NAME
HAVING COUNT(*) = 2

E 12°55'05.25"
N 56°04'39.16"

LOL.. I ntocied that but I didn't want to complain

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-28 : 13:01:58

you might want to also include table_schema column in there

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources