Post reply

returning tables that contain a specific field

  • September 18, 2003 at 2:57 am

    #84438

    Hi,

    Is there any way that I can query all my user tables to find in which table a certain fieldName exists?

    Eg, I have several tables that contain the field “storeId”, is there any way that I can return the names of all my user tables that contain the field “storeId” ?

    Eg

    SELECT userTableName

    FROM userTablesCollection

    Where fieldname = “storeId”

    I ask this because it would save time from manually searching thru all my user tables.

    Any help greatly appreciated.

    Cheers,

    Yogi.

  • September 18, 2003 at 3:07 am

    #474823

    Hi yogi,

    quote:

    Hi,

    Is there any way that I can query all my user tables to find in which table a certain fieldName exists?

    several ways to do this

    SELECT

    table_schema, table_name

    FROM

    information_schema.columns

    WHERE

    column_name = 'MsgDateReceived'

    OR

    SELECT

    s2.name

    FROM

    dbo.syscolumns s1

    JOIN

    dbo.sysobjects s2

    ON

    s1.id = s2.id

    WHERE

    s1.NAME='MsgDateReceived'

    Or (according to Execution Plan the most efficient one)

    SELECT

    table_schema, table_name

    FROM

    information_schema.columns

    WHERE

    column_name

    like 'MsgDateReceived'

    AND

    objectproperty(object_id(table_name), 'IsUserTable') = 1

    ORDER BY

    column_name

    HTH

    Frank

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • September 18, 2003 at 3:45 am

    #474829

    Morning Frank,

    This has opened up quite a few avenues for me, thanks man.

    btw are you a stuttgart or a bayern fan?

    yogiberr(glasgow)

  • September 18, 2003 at 3:57 am

    #474832

    quote:

    btw are you a stuttgart or a bayern fan?

    neither. I am (originally) from Gelsenkirchen. While I am not much interested in soccer and nobody knows Gelsenkirchen, soccer fan will maybe know Schalke 04?

    Frank

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • September 18, 2003 at 8:41 am

    #474881

    quote:

    Hi,

    Is there any way that I can query all my user tables to find in which table a certain fieldName exists?

    Eg, I have several tables that contain the field “storeId”, is there any way that I can return the names of all my user tables that contain the field “storeId” ?

    Eg

    SELECT userTableName

    FROM userTablesCollection

    Where fieldname = “storeId”

    I ask this because it would save time from manually searching thru all my user tables.

    Any help greatly appreciated.

    Cheers,

    Yogi.

    SELECT Table_Name

    FROM Information_Schema.Columns

    WHERE Column_Name = 'StoreID'

    --Jonathan



    --Jonathan

  • September 18, 2003 at 2:07 pm

    #474938

    thanks Jonathan.

    yogi

  • September 19, 2003 at 2:38 am

    #474991

    hi yogiberr!

    the thing you're talking about is named "database dictionary" (take a look at BOL). for a glance of what information is "hidden" there, look at

    http://qa.sqlservercentral.com/scripts/contributions/246.asp

    regards,

    chris.

  • September 19, 2003 at 5:05 am

    #475007

    thanks Chris.

    looks like "database dictionary" will save me a good bit of time.

    Magic.

    yogiberr

Viewing 8 posts - 1 through 7 (of 7 total)

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