今天和大家分享两段有用的代码
在SQL Server 2008中如何查看一个数据库对象(Database Object)的依赖关系
--Change the "dbo.v_searchall" into your own object
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name
,referenced_server_name AS server_name
,referenced_database_name AS database_name
,referenced_schema_name AS schema_name
, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'dbo.v_searchall');
分层查看依赖关系
--Change the "v_searchall" into your own object
DECLARE @referencing_entity AS sysname;
SET @referencing_entity = N'v_searchall';
WITH ObjectDepends(entity_name,referenced_schema, referenced_entity, referenced_id,level)
AS (
SELECT entity_name =
CASE referencing_class
WHEN 1 THEN OBJECT_NAME(referencing_id)
WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t
WHERE t.object_id = sed.referencing_id)
WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
WHERE st.object_id = sed.referencing_id) COLLATE database_default
END
,referenced_schema_name
,referenced_entity_name
,referenced_id
,0 AS level
FROM sys.sql_expression_dependencies AS sed
WHERE OBJECT_NAME(referencing_id) = @referencing_entity
UNION ALL
SELECT entity_name =
CASE sed.referencing_class
WHEN 1 THEN OBJECT_NAME(sed.referencing_id)
WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t
WHERE t.object_id = sed.referencing_id)
WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
WHERE st.object_id = sed.referencing_id) COLLATE database_default
END
,sed.referenced_schema_name
,sed.referenced_entity_name
,sed.referenced_id
,level + 1
FROM ObjectDepends AS o
JOIN sys.sql_expression_dependencies AS sed ON sed.referencing_id = o.referenced_id
)
SELECT entity_name,referenced_schema, referenced_entity, level
FROM ObjectDepends
ORDER BY level;