December 19, 2007 at 8:29 am
I am trying to write a script in SQL Server to give me the table names where two particular columns exists. Can any one help... I am sure this is simple I am just not sure how to do it.
Thanks in Advance.
December 19, 2007 at 9:44 am
SELECT TABLE_NAME FROM information_schema.columns WHERE COLUMN_NAME = '.....'
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 19, 2007 at 9:44 am
select *
from information_schema.columns.
Look at the result set, add your filter
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 19, 2007 at 10:02 am
This helps, but the next step is I am needing to find the tables that have two columns in the table.
ie.
I am trying to find all tables that have BOTH columns "x1" and "z1" existing in the same table.
December 19, 2007 at 10:50 am
kipp (12/19/2007)
This helps, but the next step is I am needing to find the tables that have two columns in the table.ie.
I am trying to find all tables that have BOTH columns "x1" and "z1" existing in the same table.
Off the cuff you can use:
select distinct table_name
from information_schema.columns a
where column_name = 'x1'
and exists
(select *
from information_schema.columns b
where a.table_name = b.table_name
and b.column_name = 'z1')
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 19, 2007 at 11:24 am
That did the trick...
Thanks so much for your help!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply