SQL Server如何找出视图依赖的对象和视图嵌套层数

万有斥力 · 2023-4-4 14:04:12

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？立即注册

x

场景：在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如何找出视图依赖的对象和视图嵌套层数

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

0 个回复

快速回复

楼主热帖

标签云

浏览过的版块