Post reply

How to find all column dependencies including triggers?

  • March 7, 2013 at 6:03 pm

    #272082

    Hi,

    There was a situation when a column was removed from a table via some external web application, and after that a bunch of "invalid column name" sql errors generated in app's log.

    The reason was that due to apparently app's bug, it did not update table's trigger that was still using the deleted column name in its code... What would be a best way to see ALL column dependencies?

    Thanks!

  • March 7, 2013 at 6:21 pm

    #1594763

    Search for the column name in syscomments

  • March 7, 2013 at 9:01 pm

    #1594777

    arnipetursson (3/7/2013)

    Search for the column name in syscomments

    syscomments is there for backward compatibility only.

    Use sys.sql_modules in SQL 2005 and newer. The definition column contains the T-SQL code for the module.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 7, 2013 at 11:47 pm

    #1594832

    syscomments is there for backward compatibility only.

    Use sys.sql_modules in SQL 2005 and newer. The definition column contains the T-SQL code for the module.

    here is the code to find the dependencies..

    select OBJECT_NAME(object_id) from sys.sql_modules where definition like '%column_name%'

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • March 8, 2013 at 11:20 am

    #1595075

    This is exactly it,

    Thanks so much guys

  • March 8, 2013 at 11:28 am

    #1595079

    Bear in mind that it's not infallible. If I use select * from mytable inside my procedure you won't find the column you're looking for. You might want to check any procedures that contain the tablename which contains your column as well.

  • March 8, 2013 at 11:37 am

    #1595080

    from my snippets on finding dependencies for a specific column name;

    SELECT

    depz.referenced_schema_name,

    depz.referenced_entity_name,

    objz.type_desc,

    colz.name AS ColumnName

    FROM sys.sql_expression_dependencies depz

    INNER JOIN sys.objects objz ON depz.referenced_id=objz.object_id

    LEFT OUTER JOIN sys.columns colz ON objz.object_id = colz.object_id

    AND colz.column_id = referencing_minor_id

    --WHERE referencing_id = OBJECT_ID(N'MyTable');

    WHERE colz.name = 'EmployeeNumber'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 8, 2013 at 3:18 pm

    #1595142

    The last one I could not get working for some reason, but i'll take another look

    Thanks again

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

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