Best way to determine tables which contain a list of specific columns

Question

Post reply

Best way to determine tables which contain a list of specific columns

Xedni

SSCertifiable

Points: 6951
More actions
November 7, 2013 at 10:57 am

#395789

I’ve got an issue which has come up with enough regularity that I wanted to see if you knew of a cleaner solution than what I’ve been doing.
Here’s the scenario: I need to make some modification to a bunch of tables so I’m deriving a list in order to loop over it and perform some dynamic sql on them, but I only want the tables which have all of a given set of columns.
For example, I want all tables which contain InstrumentID, AssetClass and InstrumentType. I’ve been deriving that list like this:
use tempdb
go
--Set up two fake tables.
--#T1 should NOT show up in the list.
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1
(
InstrumentID int,
AssetClass varchar(100)
)
--#T2 SHOULD show up in the list
if object_id('tempdb.dbo.#T2') is not null drop table #T2
create table #T2
(
InstrumentID int,
AssetClass varchar(100),
InstrumentType varchar(100)
)
--Derive list of tables which contain all three column
select t.name
from sys.tables t
inner join (select [object_id]
from sys.columns
where name in ('InstrumentID', 'AssetClass', 'InstrumentType')
group by [object_id]
having count(1) = 3) x
on t.[object_id] = x.[object_id]
where name like '#T[0-9]%'--This clause is just to filter out tempdb noise for the purposes of this example. It's not pertinent to the question
That works fine, but it just feels somewhat roundabout. Other than joining to sys.columns n times, is there a cleaner way to do this?
Executive Junior Cowboy Developer, Esq.[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

What about using INFORMATION_SCHEMA.COLUMNS view? it will give you the names of columns and tables.

Another option would be to use OBJECT_NAME() instead of querying sys.tables. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Xedni SSCertifiable Points: 6951 More actions · Answer 2

I don't need to just visibly see it. sys.columns and information_schema.columns would effectively do the same thing here. What I need is a programmatic way of determining the tables which contain all three columns at run time.

Executive Junior Cowboy Developer, Esq.[/url]

Xedni SSCertifiable Points: 6951 More actions · Answer 3

Oh I think I see what you mean, the join isn't really necessary here, it could just be

select object_name([object_id])

from sys.columns

where name in ('InstrumentID', 'AssetClass', 'InstrumentType')

group by [object_id]

having count(1) = 3

Executive Junior Cowboy Developer, Esq.[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Now I'm lost, I thought you just wanted to improve the query that ccame after "--Derive list of tables which contain all three column". Now I'm not sure what you're looking for.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Xedni SSCertifiable Points: 6951 More actions · Answer 5

Nevermind, I think this was one of those things that after I posted it, I sort of answered my own question. Thanks for the quick replies anyway.

Executive Junior Cowboy Developer, Esq.[/url]