Post reply

Filtering by column

  • December 20, 2010 at 3:04 pm

    #232552

    Hi.

    I am using SQL Server 2005

    I need to alter the certain query and to do this, I need to find other tables where certain column is used. I just joined the company and do not know the database structure yet, so I would like to see the tables where the column that I am interested in is used .. Basically I need to view all the tables where the column with certain name is present. I see filters of tables of certain name, etc.. however can't find how to filter by column name.

    I know I used something like in Toad for Oracle ...

    Can anyone help, please ...

  • December 20, 2010 at 3:28 pm

    #1264833

    SELECT OBJECT_NAME(object_id)

    FROM sys.columns

    WHERE name = 'column name here'

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • December 20, 2010 at 3:30 pm

    #1264835

    I've enjoyed using Red Gate's free Search ad-in tool for questions like that. You can get it here:

    http://www.red-gate.com/products/sql-development/sql-search/

    The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • December 20, 2010 at 3:40 pm

    #1264836

    Thanks ...

  • December 20, 2010 at 3:57 pm

    #1264840

    Hi,

    You can query from information schema view too, which provides the sql server metadata.

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS

    WHERE column_name =''

    This view provides both column name and tables names.

    Thanks

    John

  • December 20, 2010 at 5:11 pm

    #1264849

    This will allow you to view specific columns by name, or by data type along with their associated table name.

    SELECT so.Name AS 'Table',sc.Name AS 'Column'

    FROM sys.sysobjects so

    LEFT OUTER JOIN sys.syscolumns sc

    ON sc.id = so.id

    WHERE so.type = 'u'AND sc.name = 'EmployeeId' --using column name

    --or use AND sc.xtype = 40 for all columns with a data type of Date

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • December 20, 2010 at 9:19 pm

    #1264874

    bitbucket-25253 (12/20/2010)

    This will allow you to view specific columns by name, or by data type along with their associated table name.

    SELECT so.Name AS 'Table',sc.Name AS 'Column'

    FROM sys.sysobjects so

    LEFT OUTER JOIN sys.syscolumns sc

    ON sc.id = so.id

    WHERE so.type = 'u'AND sc.name = 'EmployeeId' --using column name

    --or use AND sc.xtype = 40 for all columns with a data type of Date

    Ron,

    Eliminate the join... take a look at the OBJECT_NAME() function. It's a bit scope sensitive but so are both sys.objects and sys.sysobjects.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 21, 2010 at 12:08 am

    #1264909

    bitbucket-25253 (12/20/2010)

    This will allow you to view specific columns by name, or by data type along with their associated table name.

    SELECT so.Name AS 'Table',sc.Name AS 'Column'

    FROM sys.sysobjects so

    LEFT OUTER JOIN sys.syscolumns sc

    ON sc.id = so.id

    WHERE so.type = 'u'AND sc.name = 'EmployeeId' --using column name

    --or use AND sc.xtype = 40 for all columns with a data type of Date

    Both syscolumns and sysobjects are deprecated, should not be used in new development and will be removed in a future version of SQL server. They're there only for backward compat with SQL 2000. The replacements are sys.objects and sys.columns.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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