查看存儲過程相關性

• 2020 年 4 月 9 日
• 筆記

系統函數： sys.dm_sql_referencing_entities

此函數用於顯示依賴於過程的對象。

1.第一個示例創建 uspVendorAllInfo 過程，該過程返回 Adventure Works Cycles 資料庫中所有供應商的名稱、所提供的產品、信用等級以及可用性。

IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL      DROP PROCEDURE Purchasing.uspVendorAllInfo;  GO  CREATE PROCEDURE Purchasing.uspVendorAllInfo  WITH EXECUTE AS CALLER  AS      SET NOCOUNT ON;      SELECT v.Name AS Vendor, p.Name AS 'Product name',        v.CreditRating AS 'Rating',        v.ActiveFlag AS Availability      FROM Purchasing.Vendor v      INNER JOIN Purchasing.ProductVendor pv        ON v.BusinessEntityID = pv.BusinessEntityID      INNER JOIN Production.Product p        ON pv.ProductID = p.ProductID      ORDER BY v.Name ASC;  GO

2.創建該過程後，第二個示例使用 sys.dm_sql_referencing_entities 函數來顯示依賴於該過程的對象。

USE AdventureWorks2012;  GO  SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent  FROM sys.dm_sql_referencing_entities ('Purchasing.uspVendorAllInfo', 'OBJECT');  GO

此函數用於顯示過程所依賴的對象。

1.創建存儲過程，該過程返回 Adventure Works Cycles 資料庫中所有供應商的名稱、所提供的產品、信用等級以及可用性。

USE AdventureWorks2008R2;  GO  IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL      DROP PROCEDURE Purchasing.uspVendorAllInfo;  GO  CREATE PROCEDURE Purchasing.uspVendorAllInfo  WITH EXECUTE AS CALLER  AS      SET NOCOUNT ON;      SELECT v.Name AS Vendor, p.Name AS 'Product name',        v.CreditRating AS 'Rating',        v.ActiveFlag AS Availability      FROM Purchasing.Vendor v      INNER JOIN Purchasing.ProductVendor pv        ON v.BusinessEntityID = pv.BusinessEntityID      INNER JOIN Production.Product p        ON pv.ProductID = p.ProductID      ORDER BY v.Name ASC;  GO

2.使用 sys.dm_sql_referenced_entities 函數來顯示該過程依賴的對象

USE AdventureWorks2012;  GO  SELECT referenced_schema_name, referenced_entity_name,  referenced_minor_name,referenced_minor_id, referenced_class_desc,  is_caller_dependent, is_ambiguous  FROM sys.dm_sql_referenced_entities ('Purchasing.uspVendorAllInfo', 'OBJECT');  GO

對象目錄視圖： sys.sql_expression_dependencies

顯示依賴於過程的對象。

1.創建存儲過程，

IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL      DROP PROCEDURE Purchasing.uspVendorAllInfo;  GO  CREATE PROCEDURE Purchasing.uspVendorAllInfo  WITH EXECUTE AS CALLER  AS      SET NOCOUNT ON;      SELECT v.Name AS Vendor, p.Name AS 'Product name',        v.CreditRating AS 'Rating',        v.ActiveFlag AS Availability      FROM Purchasing.Vendor v      INNER JOIN Purchasing.ProductVendor pv        ON v.BusinessEntityID = pv.BusinessEntityID      INNER JOIN Production.Product p        ON pv.ProductID = p.ProductID      ORDER BY v.Name ASC;  GO

2.使用 sys.sql_expression_dependencies 視圖來顯示依賴於該過程的對象。

USE AdventureWorks2012;  GO  SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,      OBJECT_NAME(referencing_id) AS referencing_entity_name,      o.type_desc AS referencing_desciption,      COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,      referencing_class_desc, referenced_class_desc,      referenced_server_name, referenced_database_name, referenced_schema_name,      referenced_entity_name,      COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,      is_caller_dependent, is_ambiguous  FROM sys.sql_expression_dependencies AS sed  INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  WHERE referenced_id = OBJECT_ID(N'Purchasing.uspVendorAllInfo')  GO

顯示過程所依賴的對象。

1.創建存儲過程

IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL      DROP PROCEDURE Purchasing.uspVendorAllInfo;  GO  CREATE PROCEDURE Purchasing.uspVendorAllInfo  WITH EXECUTE AS CALLER  AS      SET NOCOUNT ON;      SELECT v.Name AS Vendor, p.Name AS 'Product name',        v.CreditRating AS 'Rating',        v.ActiveFlag AS Availability      FROM Purchasing.Vendor v      INNER JOIN Purchasing.ProductVendor pv        ON v.BusinessEntityID = pv.BusinessEntityID      INNER JOIN Production.Product p        ON pv.ProductID = p.ProductID      ORDER BY v.Name ASC;  GO

2.使用 sys.sql_expression_dependencies 視圖來顯示該過程依賴的對象

USE AdventureWorks2012;  GO  SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,      o.type_desc AS referencing_desciption,      COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,      referencing_class_desc, referenced_class_desc,      referenced_server_name, referenced_database_name, referenced_schema_name,      referenced_entity_name,      COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,      is_caller_dependent, is_ambiguous  FROM sys.sql_expression_dependencies AS sed  INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  WHERE referencing_id = OBJECT_ID(N'Purchasing.uspVendorAllInfo');  GO