May 3, 2017 at 3:10 pm
-------------------------------------------------------------------------
-- NESTED DEPENDENCIES: -- the SQL below does not show all objects
-- (for example, if table is from other DB, it is not shown in the results).
/*
Ideally,
if SP1 calls SP2, SP3, and SP4 inside, and the SP3 uses 3 views V1, V2, and V3
and V2 uses 3 functions Fn1, Fn2, and Fn3,
I would like to see something like this:
SP1
------
-- SP2
-- SP3
-- V1
-- V2
-- Fn1
-- Fn2
-- Fn3
-- V3
-- SP4
BUT EVEN IF THE ABOVE "TREE" IS AN UNIMAGINABLE LUXURY OR UNATTAINABLE FANTACY,
The list that the below script produces is incomplete. Needless to say that sp_depends is even more useles...
*/
SELECT * FROM (SELECT NAME AS ProcedureName, SUBSTRING(( SELECT ', ' + OBJDEP.NAME
FROM sysdepends
INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
WHERE obj.type = 'P'
AND Objdep.type in ('P', 'V', 'U', 'TR', 'FN', 'IF', 'TF')
AND sysdepends.id = procs.object_id
AND OBJ.Name = 'usp_RPT_R0040'
ORDER BY OBJ.name
FOR
XML PATH('')), 2, 8000) AS NestedProcedures
FROM sys.procedures procs
) InnerTab
WHERE NestedProcedures IS NOT NULL
--RESULT:
--ARE THESE ONLY DIRECT DEPENDENCIES? is a tree possible to build?
--------------------
Likes to play Chess
May 4, 2017 at 8:36 am
you can try to recursively query the metadata like this:
-- objects referenced by specified stored proc
;WITH ref_list AS
(SELECT o.object_id, OBJECT_SCHEMA_NAME(o.object_id) AS schema_name, o.name AS object_name, o.type_desc
FROM sys.sql_expression_dependencies ed
INNER JOIN sys.objects o ON ed.referenced_id = o.object_id
WHERE ed.referencing_id = OBJECT_ID('dbo.SP1','P')
AND ed.referenced_id <> ed.referencing_id
UNION ALL
SELECT o2.object_id, OBJECT_SCHEMA_NAME(o2.object_id) AS schema_name, o2.name AS object_name, o2.type_desc
FROM ref_list rl
INNER JOIN sys.sql_expression_dependencies ed2 ON rl.object_id = ed2.referencing_id
INNER JOIN sys.objects o2 ON ed2.referenced_id = o2.object_id
WHERE ed2.referenced_id <> ed2.referencing_id
)
SELECT DISTINCT schema_name, object_name, type_desc
FROM ref_list;
May 4, 2017 at 11:46 am
Thanks. but your query returns zero rows.. while there are at least a dozen of dependent (inner) objects.
Likes to play Chess
May 4, 2017 at 2:00 pm
not sure why, when I replace the procedure name in the function call:
OBJECT_ID('dbo.SP1','P')
I get listing of all views, tables, functions, and other procedures that get referenced by the one I specify. Do you have permissions to all of the referenced objects?
May 4, 2017 at 2:42 pm
yes, works now. (i just needed to be in correct DB context, for OBJECT_ID( to return not null..
it works, but returns substantially less rows/dep.objects than my original qry
Likes to play Chess
May 4, 2017 at 2:47 pm
You mention databases, are some of the objects in different databases? That would certainly not show up here.
May 7, 2017 at 5:33 pm
I use the following to get my dependencies.
Only works with objects within the same database - so if there are references to external databases it won't find dependencies of those - doing that would require a lot more coding and it would require dynamic sql.
This code below does deal with tables/views/functions/storedprocs/synonyms and types - other object types would require further code and for my purposes it wasn't required
I supply a list of schema/objectname pairs on a temp table and get back a list of all those dependencies.
final select can be adjusted to remove some columns.
I also have a "callstack" - that way in situations where I need it I can get the hierarchy of the calls
if object_id('tempdb..#base_objects') is not null
drop table #base_objects
create table #base_objects
(schemaname varchar(128)
,objectname varchar(128)
)
insert into #base_objects
select 'dbo', 'p_CopyRole'
union all
select 'dbo','fn_GetFiscalPeriodCLR'
;with DepTree
(top_level_name, referenced_id, referenced_schema, referenced_name, referencing_id, referencing_schema, referencing_name, NestLevel, callstack, typedesc
)
as
(select schema_name(o.schema_id) + '.' + o.name as top_level_name
, o.object_id as referenced_id
, schema_name(o.schema_id) as referenced_schema
, o.name as referenced_name
, o.object_id as referencing_id
, schema_name(o.schema_id) as referencing_schema
, o.name as referencing_name
, 0 as NestLevel
, cast ('|' + schema_name(o.schema_id) + '.' + o.name + '|' as nvarchar(max)) as callstack
, o.type_desc as typedesc
from sys.objects o
inner join #base_objects ro
on ro.schemaname = schema_name(o.schema_id)
and ro.objectname = o.name
union all
SELECT r.top_level_name
, ref.referenced_id
, ref.referenced_schema
, ref.referenced_name
, ref.referencing_id
, ref.referencing_schema
, ref.referencing_name
, ref.NestLevel
, callstack + ref.objectname + '|' as callstack
, cast(ref.typedesc as nvarchar(60)) as typedesc
FROM sys.sql_expression_dependencies d1
JOIN DepTree r
ON d1.referencing_id = r.referenced_id
outer apply (select ob2.object_id as referenced_id
, schema_name(ob2.schema_id) as referenced_schema
, ob1.name as referenced_name
, schema_name(ob2.schema_id) + '.' + ob2.name as objectname
, ob1.object_id as referencing_id
, schema_name(ob1.schema_id) as referencing_schema
, ob1.name as referencing_name
, NestLevel + 2 as NestLevel
, cast(ob2.type_desc as nvarchar(60)) as typedesc
from sys.synonyms sy1
inner join sys.objects ob1
on ob1.object_id = sy1.object_id
inner join sys.objects ob2
on '[' + schema_name(ob2.schema_id) + '].[' + ob2.name + ']' = sy1.base_object_name
where sy1.object_id = d1.referenced_id
union all
select d1.referenced_id
, schema_name(ob1.schema_id) as referenced_schema
, ob1.name as referenced_name
, schema_name(ob1.schema_id) + '.' + ob1.name as objectname
, r.referencing_id
, r.referencing_schema
, r.referencing_name
, NestLevel + 1 as NestLevel
, cast(ob1.type_desc as nvarchar(60)) as typedesc
from sys.objects ob1
where ob1.object_id = d1.referenced_id
union all
select d1.referenced_id
, schema_name(ty1.schema_id) as referenced_schema
, ty1.name as referenced_name
, schema_name(ty1.schema_id) + '.' + ty1.name as objectname
, r.referencing_id
, r.referencing_schema
, r.referencing_name
, NestLevel + 1 as NestLevel
, cast(d1.referenced_class_desc as nvarchar(60)) as typedesc
from sys.table_types ty1
where ty1.user_type_id = d1.referenced_id
) ref
where callstack not like '%|' + ref.objectname + '|%'
)
select *
from DepTree dt
where NestLevel > 0
option (maxrecursion 5000);
May 14, 2017 at 6:10 pm
Thank you so much, Frederico.!
Very helpful.
We are actually continuing the development that you have suggested above to see cross-db dependencies..
Willl keep you posted.
V.
Likes to play Chess
December 13, 2023 at 4:16 pm
Chris - I know this is like 2-3 years later but I tried your code and it doesn't work for me either. It will show me the tables that the SP I specify depends on but not anything else like other SP's
Kindest Regards,
Just say No to Facebook!December 13, 2023 at 4:19 pm
Have you made any improvements to what Frederico provided that you'd be willing to share? Also have you been able to make it so you don't have to use teh master DB? We are in a cloud situation and have access ONLY to our DB and not teh master and I too have been looking for code that will do a recursive take on object dependency, all types of objects. The example that Chris provided will show me the tables that the SP I specify depends on but not anything else it depends on like other SP's
Kindest Regards,
Just say No to Facebook!Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply