Friday, December 2, 2011

Dependent Objects

How many time you need to scan thru a stored procedure to find what are all the tables it uses.  Specially when you are working on a legacy application, it becomes most frequent task.

To simplify the work you can use SYS.SQL_Expression_Dependencies system table.

You can use the following CTE to get deeper object references

CREATE PROC [dbo].GetDependentObjectList @ObjectName AS sysname
AS
WITH ObjectDepends(entity_name,referenced_database_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_database_name
,referenced_schema_name
,referenced_entity_name
,referenced_id
,0 AS level
FROM SYS.SQL_Expression_Dependencies AS sed
WHERE OBJECT_NAME(referencing_id) = @ObjectName
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_database_name
,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 AS referencing_entity, referenced_database_name,referenced_schema,referenced_entity, level
FROM ObjectDepends
ORDER BY level;

GO

No comments:

Post a Comment

Managing SQL Role

Recently, we had a scenario, where we need a simple SQL Role which gets read access to bunch of views.  While it was easy to create the vie...