SQL Server如何找出視圖依賴的物件和視圖嵌套層數

場景：在SQL Server資料庫的SQL優化中，我們有時候會需要找出某個視圖的依賴物件，簡單的視圖倒是很容易一眼就找出依賴物件，一旦遇到一些復雜的視圖，如果我們手動整理的話，就相當麻煩了，因為你要一個物件一個物件的捋一遍，相當的耗時耗力，其實這種機械、重復、繁雜的事情就應該讓機器（SQL）去處理，我們應該將精力和時間用在關鍵的地方，所謂好鋼要用在刀刃上，所以最好能用一個SQL將視圖依賴的物件全部查詢出來，下面是我寫的一個腳本，

/*-*************************************************************************************************************
    --腳本名稱  ：      get_view_referenced_objects.sql
    --腳本作者  :       瀟湘隱者
    --創建日期  :       2018-06-28
***************************************************************************************************************
    腳本功能    ：       查看View參考/依賴的物件
***************************************************************************************************************
    注意事項    :       1:執行前修改引數@object_name的值
***************************************************************************************************************
    腳本引數    :       @object_name  按實際情況填寫對應的視圖名稱
***************************************************************************************************************
    參考資料    :       無
***************************************************************************************************************
    更新記錄    ：      2018-06-28 創建此腳本
                       2022-01-06 修改腳本，如果被參考的物件不是跨資料庫或跨服務器的物件，
                                   那么server_name，database_name為null，修改腳本邏輯，
*-**************************************************************************************************************/
declare @object_name varchar(128)
set @object_name = 'dbo.v_SecPolicyInfo'

;WITH cte_objects
AS
(
SELECT 1 as  nested_level
      ,d.referencing_id
      ,d.referenced_id
      ,d.referenced_server_name
      ,d.referenced_database_name
      ,d.referenced_class_desc
      ,d.referenced_schema_name
      ,d.referenced_entity_name
FROM sys.sql_expression_dependencies d
WHERE d.referencing_id = object_id(@object_name)
UNION ALL
SELECT t.nested_level+1 nested_level
      ,d.referencing_id
      ,d.referenced_id
      ,d.referenced_server_name
      ,d.referenced_database_name
      ,d.referenced_class_desc
      ,d.referenced_schema_name
      ,d.referenced_entity_name
FROM sys.sql_expression_dependencies d
INNER JOIN cte_objects t
   ON t.referenced_id = d.referencing_id
)

SELECT d.nested_level
     , schema_name(o.schema_id) +'.' + o.name as object_name
     , o.type_desc 
     , ISNULL(d.referenced_server_name,@@SERVERNAME) as referenced_server_name
     , ISNULL(d.referenced_database_name, DB_NAME()) as referenced_database_name
     , d.referenced_class_desc
     , ISNULL(d.referenced_schema_name,'dbo') 
       + '.' +d.referenced_entity_name as referenced_entity_name
     , p.type_desc as  referenced_object_type

FROM cte_objects d
INNER JOIN sys.objects o
    ON d.referencing_id = o.object_id
INNER JOIN sys.objects p
    ON d.referenced_id = p.object_id ;

場景：有時候，我們在資料庫優化或做一些SQL審計的時候，我們需要找出一些嵌套的視圖，那么有沒有一個現成的SQL陳述句找出嵌套視圖呢？我自己寫過一個SQL，但是How to query metadata to discover nested views中的SQL比我寫的要好，分享如下(下面腳本來源于參考資料)：

/*-*************************************************************************************************************
    --腳本名稱  ：      get_netsted_view_level.sql
    --腳本作者  :       Fredrik Rundgren
    --創建日期  :       2018-04-15
***************************************************************************************************************
    腳本功能    ：      找出資料庫視圖嵌套視圖的視圖/嵌套超過2層的視圖，
***************************************************************************************************************
    注意事項    :       此腳本來自下面參考資料，
***************************************************************************************************************
    腳本引數    :       無引數
***************************************************************************************************************
    參考資料    :       https://www.sqlservice.se/how-to-query-metadata-to-discover-nested-views/
***************************************************************************************************************
    更新記錄    ：      2018-04-15                    
*-**************************************************************************************************************/
;WITH cRefobjects
AS (
    -- Anchor level a view which refers to another view
    SELECT DISTINCT sed.referencing_id
        ,sed.referenced_id
        ,schema_name(o.schema_id) AS SchemaName
        ,o.name AS ViewName
        ,CONVERT(NVARCHAR(2000), N'>>' + schema_name(o.schema_id) + '.' + o.name) COLLATE DATABASE_DEFAULT AS NestViewPath
        ,o.type_desc
        ,1 AS LEVEL
    FROM sys.sql_expression_dependencies sed
    INNER JOIN sys.objects o ON o.object_id = sed.referencing_id
        AND o.type_desc = 'VIEW'
    LEFT OUTER JOIN sys.objects o2 ON o2.object_id = sed.referenced_id
        AND o2.type_desc IN ('VIEW')
    WHERE o2.object_id IS NULL

    UNION ALL

    -- Recursive part, retrieve any higher level views, build the path and increment the level
    SELECT sed.referencing_id
        ,sed.referenced_id
        ,s.name AS sch
        ,o.name AS viewname
        ,CONVERT(NVARCHAR(2000), cRefobjects.NestViewPath + N'>' + s.name + '.' + o.name) COLLATE DATABASE_DEFAULT
        ,o.type_desc
        ,LEVEL + 1 AS LEVEL
    FROM sys.sql_expression_dependencies AS sed
    INNER JOIN sys.objects o ON o.object_id = sed.referencing_id
        AND o.type_desc = 'VIEW'
    INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
    INNER JOIN cRefobjects ON sed.referenced_id = cRefobjects.referencing_id
    )
SELECT DISTINCT SchemaName + '.' + ViewName AS ViewName
    ,NestViewPath
    ,type_desc
    ,LEVEL
FROM cRefobjects
WHERE LEVEL > 1
ORDER BY LEVEL DESC
    ,viewname
OPTION (MAXRECURSION 32);

標籤：SQL Server

上一篇：GaussDB(DWS)運維：導致SQL執行不下推的改寫方案

下一篇：什么是主資料？（轉載）