/* 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