SQL Server如何找出视图依赖的对象和视图嵌套层数
2023-09-11 14:13:57 时间
场景:在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 2005基础应用视频教程下载学习
- SQL Server 2008中增强的"汇总"技巧
- 搭建 Windows Server 2012 FTP 服务器
- Sql Server之旅——第五站 确实不得不说的DBCC命令(文后附年会福利)
- sql server 2008评估期已到的解决办法
- C# 连接SQL Server数据库的几种方式--server+data source等方式
- SQL Server基础Sql语句复习
- sql server 中将由逗号“,”分割的一个字符串,转换为一个表,并应用与 in 条件
- loadrunner12.55 :HTTP Properties > Advanced设置 之 parameterize server names
- 《转》SQL Server 2008 数据维护实务
- KVM安装Windows Server 2008 R2使用virtio硬盘
- [SQL] sql server中如何查看执行效率不高的语句
- SQL Server 2005 查看数据库表的大小 按照表大小排列
- SQL Server中clustered与nonclustered的区别
- Sql Server在建好的表中,新增一列
- Sql Server 导入另一个数据库中的表数据
- SQL SERVER CHARINDEX函数
- Sql Server 分页存储过程
- SQL SERVER服务器链接连接(即sql server的跨库连接)
- Sql_Server中如何判断表中某列是否存在
- C# 连接SQL Server数据库的几种方式--server+data source等方式
- SharePoint 2013 中的SQL Server 安全
- 从 Microsoft Dynamics CRM 4.0 server迁移到 Microsoft Dynamics CRM 2013 Server
- Win10(Server)与Ubuntu18.04(Client)使用Synergy--键盘鼠标共享
- SQL——Sql_Server中如何判断表中某字段、判断表、判断存储过程以及判断函数是否存在
- JSP(Java Server Pages,即:Java服务器页面
- SQL Server 基础系列篇
- SQL Server日期函数
- 在与SQL Server建立连接时出现与网络相关的或特定于实例的错误