如何检查SQL Server“视图”依赖项

如果这是您经常要做的事情,红门SQL依赖关系跟踪器(不,我不为他们工作)是一个很好的工具。如果你想试试的话,我想他们有试用期。

可以使用sys.dm_sql_referenced_entities函数查找指定视图引用的对象:

select distinct
引用的“架构名称”,
引用的实体名称
from sys.dm_sql_referered_entities('sales.vsalesPersonalSalesByFiscalyears','object');
< /代码> 



此外,还有sys.sql_expression_dependencies系统视图,您可以在其中指定表名和引用对象的类型:

选择
引用\u object\u name=o.name,
引用对象类型描述=类型描述
从sys.sql_expression_dependencies se
内部联接系统对象o
在se.reference_id=o[对象_id]
其中引用了“实体”和“个人”类型。
< /代码> 



为了避免“手动”工作,您还可以使用apexsql clean,一种可以查找所有内部和外部依赖项的SQL Server工具。在结果窗格中,选择一个对象并查看依赖于所选对象的所有对象以及所选对象依赖的对象:




您还可以筛选对象和可视化依赖项:



免责声明:我是ApexSQL的支持工程师

希望这有帮助
由指定视图引用:


SELECT DISTINCT 
referenced_schema_name , 
referenced_entity_name 
FROM sys.dm_sql_referenced_entities ('Sales.vSalesPersonSalesByFiscalYears', 'OBJECT');




此外,还有sys.sql_expression_dependencies系统视图,您可以在其中指定表名和引用对象的类型:

SELECT 
referencing_object_name = o.name, 
referencing_object_type_desc = o.type_desc 
FROM sys.sql_expression_dependencies se 
INNER JOIN sys.objects o 
ON se.referencing_id = o.[object_id] 
WHERE referenced_entity_name = 'Person' AND o.type_desc = 'View'




为了避免“手动”工作,您也可以使用ApexSQL Clean,可以查找所有内部和外部依赖项的SQL Server工具。在结果窗格中,选择一个对象并查看依赖于所选对象的所有对象以及所选对象依赖的对象:



您还可以筛选对象和可视化依赖项:



免责声明:我是ApexSQL的支持工程师

希望这有帮助

你可以用 sql_dependencies 观点:

select OBJECT_NAME(referenced_major_id) as DependantObject
from sys.sql_dependencies
where object_id = object_id('YourViewName')

要递归地检索依赖项(例如,如果从视图中选择,则会找到其他视图引用的表):

with deps (child, parent) as (
    select d.object_id, d.referenced_major_id
    from sys.sql_dependencies d
    where d.object_id = object_id('YourViewName')
    union all
    select d.object_id, d.referenced_major_id
    from sys.sql_dependencies d
    inner join deps on deps.parent = d.object_id
)
select OBJECT_NAME(parent)
from deps

这种方法不可靠。例如,如果使用 sp_rename ,其依赖项未更新。

下面是一个很好的解释:
https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=277

您可以使用sys.sql_expression_dependencies in SQL Server>=2008(R1+)来完成此操作。
注意:这将需要30秒或更长时间。

如果你 只有 想要这些观点, 删除标量+表值函数 (‘fn’和‘if’)。

表值函数可以是视图的依赖项,反之亦然,而且视图/TVF可能依赖于标量函数。

;WITH TFV_Dependencies AS 
(
    SELECT 
         --OBJECT_SCHEMA_NAME(sysdep.referencing_id) AS Referencing_Schema 
         ReferencingO.object_id AS ReferencingObject_Id 
        ,sysschema.name AS Referencing_Schema 
        ,ReferencingO.name AS Referencing_Object_Name 
        ,sysdep.referenced_schema_name AS Referenced_Schema 
        ,sysdep.referenced_entity_name AS Referenced_Object_Name 
        --,ReferencingO.type
        ,ReferencedO.name AS RefName 
        --,ReferencedO.type AS RefType
    FROM sys.objects as ReferencingO 

    LEFT JOIN sys.schemas AS sysschema 
        ON sysschema.schema_id = ReferencingO.schema_id 

    LEFT JOIN sys.sql_expression_dependencies AS sysdep 
    LEFT JOIN sys.objects as ReferencedO 
        ON ReferencedO.name = sysdep.referenced_entity_name 
        ON sysdep.referencing_id = ReferencingO.object_id 
        AND ReferencingO.name <> sysdep.referenced_entity_name 
        --AND ReferencedO.type <> 'U' 
        AND ReferencedO.type IN ( 'IF', 'FN', 'V' ) 

    WHERE (1=1) 
    --AND ReferencingO.type NOT IN ('PK','F','UQ','SQ','D','IT', 'S')
    --AND ReferencingO.type IN ( 'U', 'V', 'FN', 'IF', 'P') 
    AND ReferencingO.type IN ( 'IF', 'FN', 'V' ) 
    AND ReferencingO.name NOT IN 
    (
         'dt_adduserobject'
        ,'dt_droppropertiesbyid'
        ,'dt_dropuserobjectbyid'
        ,'dt_generateansiname'
        ,'dt_getobjwithprop'
        ,'dt_getobjwithprop_u'
        ,'dt_getpropertiesbyid'
        ,'dt_getpropertiesbyid_u'
        ,'dt_setpropertybyid'
        ,'dt_setpropertybyid_u'
        ,'dt_verstamp006'
        ,'dt_verstamp007'

        ,'sp_helpdiagrams'
        ,'sp_creatediagram'
        ,'sp_alterdiagram'
        ,'sp_renamediagram'
        ,'sp_dropdiagram'

        ,'sp_helpdiagramdefinition'
        ,'fn_diagramobjects'
    ) 

) 

,DependencyGroup AS 
( 
    -- base case 
    SELECT 
         ReferencingObject_Id AS ObjectId 
        ,Referencing_Object_Name AS ObjectName  
        ,Referencing_Schema AS ObjectSchema 
        ,1 AS Lvl 
    FROM TFV_Dependencies 
    WHERE 1=1 
    AND Referenced_Object_Name IS NULL 

    -- recursive case
    UNION ALL 

   SELECT 
         d.ReferencingObject_Id AS ObjectId 
        ,d.Referencing_Object_Name AS ObjectName 
        ,d.Referencing_Schema AS ObjectSchema 
        ,r.Lvl + 1 AS Lvl 
    FROM TFV_Dependencies AS d 
    INNER JOIN DependencyGroup AS r 
        ON r.ObjectName = d.Referenced_Object_Name 
) 
,
CTE AS 
(
    SELECT TOP 999999999999999999 
         MAX(Lvl) AS Lvl 
        ,ObjectId 
        ,ObjectSchema
        ,ObjectName 
        --,'DELETE FROM [' + REPLACE(ObjectName, '''', '''''') + ']; ' AS DeleteCmd 
    FROM DependencyGroup 

    GROUP BY ObjectId, ObjectSchema, ObjectName 
)

SELECT 
     CTE.Lvl
    ,CTE.ObjectSchema AS SPECIFIC_SCHEMA 
    ,CTE.ObjectName AS SPECIFIC_NAME 
    ,sysSqlModules.definition AS ROUTINE_DEFINITION 
FROM CTE 

LEFT JOIN sys.sql_modules AS sysSqlModules 
    ON sysSqlModules.object_id = CTE.ObjectId 

ORDER BY Lvl, ObjectSchema, ObjectName 

OPTION (MAXRECURSION 0)