SQL Server Performance

How to find all references to the column?

Discussion in 'General Developer Questions' started by kasper, Nov 21, 2006.

  1. kasper New Member

    Hello,
    Is there a way to find all database objects that have references to specified column of the table?

    Thanks.
  2. MohammedU New Member

    I don't think there any better way other than searching syscomments table in the database.

    Mohammed
  3. dineshasanka Moderator

    if you have defiend relationships, just create a database diagram. it will do this for you.

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  4. kasper New Member

    Thanks,
    I mean not only relationships, but also the column usage within the stored procedures, views, etc.
  5. MohammedU New Member

    No way other than syscomments query...

    select distinct object_name(id) from <dbname>.dbo.syscomments (nolock) where text like '%<column name>%'

    Note: Even your column name is in comments it will showup in the results...

    Mohammed.
  6. Roji. P. Thomas New Member

  7. jezemine New Member

    there is a better way: download sqlspec, generate a data dictionary, and search that. link to it is in my sig below - I use it for this kind of thing all the time.

    syscomments has a problem because if the sproc is longer than 4000 chars, it's broken into multiple rows. if you just search each row, you could miss your column if the column name is split between two rows.

    SqlSpec - a fast, cheap, and comprehensive data dictionary generator
    for SQL Server 2000 and 2005 and Analysis Server 2005 - www.elsasoft.org
  8. dineshasanka Moderator

  9. kasper New Member

    Thanks for a great help!

Share This Page