-- retrieving the views where an object was used SELECT SCHEMA_NAME(obj.SCHEMA_ID) + '.'+ obj.name AS referencing_object , obj.type_desc referencing_object_type , sed.referenced_schema_name + '.'+ sed.referenced_entity_name AS referenced_object , obr.type_desc referenced_object_type FROM sys.all_objects obj JOIN sys.sql_expression_dependencies sed ON obj.OBJECT_ID = sed.REFERENCING_ID JOIN sys.all_objects obr ON sed.referenced_id = obr.object_id WHERE sed.referenced_entity_name = 'PurchTable' AND obj.Type = 'V' AND obj.name LIKE 'Purch%' ORDER BY referencing_object
ReferencingObject | type_desc | ReferencedObject | ReferencedObjectType |
dbo.PURCHASEORDERCUBE | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHASEORDERRESPONSEHEADERSUMMARY | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHLINEBACKORDER | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHLINEBACKORDERWORKSPACE | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHLINEEXPANDED | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHORDERAPPROVEDVIEW | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHPREPAYOPENBASE | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERCHARGEENTITY | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERENTITY | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHPURCHASEORDERLINECDSENTITY | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHTABLENOTARCHIVEDVERSIONS | VIEW | dbo.PURCHTABLE | USER_TABLE |
-- retrieving the objects referenced in a view SELECT SCHEMA_NAME(obj.SCHEMA_ID) + '.'+ obj.name AS referencing_object , obj.type_desc referncing_object_type , sed.referenced_schema_name + '.'+ sed.referenced_entity_name AS referenced_object , obr.type_desc referenced_object_type FROM sys.all_objects obj JOIN sys.sql_expression_dependencies sed ON obj.OBJECT_ID = sed.referencing_id JOIN sys.all_objects obr ON sed.referenced_id = obr.object_id WHERE obj.name = 'PURCHPURCHASEORDERHEADERV2ENTITY' ORDER BY referenced_object
ReferencingObject | ReferncingObjectType | ReferencedObject | ReferencedObjectType |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.ACCOUNTINGDISTRIBUTIONTEMPLATE | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.CONFIRMINGPO | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.DIMENSIONSETENTITY | VIEW |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.GetValidFromInContextInfo | SQL_SCALAR_FUNCTION |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.GetValidToInContextInfo | SQL_SCALAR_FUNCTION |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.HCMWORKER | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.LOGISTICSPOSTALADDRESS | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.LOGISTICSPOSTALADDRESSBASEENTITY | VIEW |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.PURCHTABLE_W | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.REASONTABLEREF | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.TMSPURCHTABLE | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.TRANSPORTATIONDOCUMENT | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.VENDINVOICEDECLARATION_IS | USER_TABLE |
-- cleaning after --DROP VIEW admin.vobject_dependencies -- view definition CREATE OR ALTER VIEW admin.vobject_dependencies -- retrieving the dependencies between objects AS SELECT SCHEMA_NAME(obj.SCHEMA_ID) + '.'+ obj.name AS referencing_object , SCHEMA_NAME(obj.SCHEMA_ID) referencing_objecT_schema , obj.name AS referencing_object_name , obj.type_desc referencing_object_type , sed.referenced_schema_name + '.'+ sed.referenced_entity_name AS referenced_object , sed.referenced_schema_name AS referenced_object_schema , sed.referenced_entity_name AS referenced_object_name , obr.type_desc referenced_object_type FROM sys.all_objects obj JOIN sys.sql_expression_dependencies sed ON obj.OBJECT_ID = sed.REFERENCING_ID JOIN sys.all_objects obr ON sed.referenced_id = obr.object_id -- call based do referenced object SELECT * FROM admin.vobject_dependencies WHERE referenced_object_name = 'PurchTable' ORDER BY referencing_object_name -- call based on referencing object SELECT * FROM admin.vobject_dependencies WHERE referencing_object_name = 'PURCHPURCHASEORDERHEADERV2ENTITY' ORDER BY referenced_object_name
Previous Post <<||>> Next Post
[1] Microsoft Dynamics 365 (2020) Data entities overview [source]