OBJECT related SQL Server Dynamic Management Views (DMVs)

from  http://www.sqlserverspecialists.com/2013/03/object-related-sql-server-dynamic.html

The DMVs in this section help you keep track of objects that are referenced by other objects in their definition, such as triggers, stored procedures, and functions.

 

Sys.dm_sql_referenced_entities

Returns a row for each user-defined entity that is referenced by a server-level DDL trigger, a database-level DDL trigger, or a specific object in the current database context. (You can get server level information only when in the master database.) No dependency information is kept for rules, defaults, system objects and temporary tables and procedures.

 

Sys.dm_sql_referencing_entities

Returns a row for each entity that references by name an object you specify, whether that object is a partition_function, an XML_schema_collection, a type, or a specific object in the current database context. (You can get server level information only when in the master database.) No dependency information is kept for rules, defaults, system objects and temporary tables and procedures.

 

sys.dm_db_stats_properties

Returns properties of statistics for the specified database object (table or indexed view) in the current SQL Server database.

 

For example, to find the entities that reference the alias type dbo.my_type:

 

USE AdventureWorks;

GO

SELECT referencing_schema_name, referencing_entity_name

FROM sys.dm_sql_referencing_entities (‘dbo.my_type’, ‘TYPE’);

GO

 

If, on the other hand, we’d like to see the schema and entity (both tables and columns) that are referenced by a database-level DDL trigger called ddl_db_trigger_log, then we might use this query:

 

USE AdventureWorks;

GO

SELECT referenced_schema_name, referenced_entity_name

FROM sys.dm_sql_referenced_entities (‘dd_db_trigger_log’, ‘DATABASE_DDL_TRIGGER’);

GO