Wednesday, December 18, 2013

Find all referencing columns, or dependencies

What happens if you are changing a column (or columns) in a table that is referenced elsewhere within the database?  How do you find the associated dependencies, such that they can be modified as well?

/* Using INFORMATION_SCHEMA CONSTRAINT_COLUMN_USAGE and REFERENCTIAL_CONSTRAINTS. */

   SELECT
      r.TABLE_NAME [Table],
      r.COLUMN_NAME [Column]
   FROM
      INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u INNER JOIN         
         INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fk
        ON u.CONSTRAINT_CATALOG = fk.UNIQUE_CONSTRAINT_CATALOG
        AND u.CONSTRAINT_SCHEMA = fk.UNIQUE_CONSTRAINT_SCHEMA
        AND u.CONSTRAINT_NAME = fk.UNIQUE_CONSTRAINT_NAME INNER JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE r
      ON fk.CONSTRAINT_CATALOG = r.CONSTRAINT_CATALOG
      AND fk.CONSTRAINT_SCHEMA = r.CONSTRAINT_SCHEMA
      AND fk.CONSTRAINT_NAME = r.CONSTRAINT_NAME
   WHERE 
     u.TABLE_NAME = 'YourTable'
     AND u.COLUMN_NAME = 'YourColumn'


Or, maybe you're changing a procedure, and you need to find all of the underlying dependencies (aka, tables) that are referenced by that stored procedure?   Try this:

   SELECT
       referenced_schema_name,
       referenced_entity_name,
       referenced_minor_name
   FROM
       sys.dm_sql_referenced_entities ('dbo.YourProcedureName','OBJECT')

No comments:

Post a Comment