Post reply

Find where all two columns exist in the database

  • December 19, 2007 at 8:29 am

    #93137

    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

    #761455

    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. Selburg

  • December 19, 2007 at 9:44 am

    #761456

    select *

    from information_schema.columns.

    Look at the result set, add your filter

  • December 19, 2007 at 10:02 am

    #761468

    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

    #761483

    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

    #761500

    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