September 28, 2017 at 3:42 am
Hi All,
I am using SQL below to get the object dependency.
select Obj.name,dep.referenced_entity_name from sys.sql_modules as Module,sys.objects AS Obj ,sys.sql_expression_dependencies
as Dep ,sys.objects as DepObj
where obj.object_id=Module.object_id and dep.referencing_id=obj.object_id
and DepObj.object_id=dep.referenced_id
and DepObj.type_desc <> 'USER_TABLE' order by Obj.type,Obj.name
I need the result to drill down to as the lowest dependency and then sort the result based on dependency rank.
For example if i have a view called AView depending on BView and BView depending on CView, i need CView to come first then BView then AView.
Appreciate your help on this please.
Regards
Nader
September 28, 2017 at 5:52 am
John Mitchell-245523 - Thursday, September 28, 2017 3:57 AMNaderYou need to use recursion to do this. Have a look through that link (or search for others) and post back if anything isn't clear. There should be enough there to get you started, though.
John
Thanks for your reply.
I checked the link and tried to apply it to my logic but didn't work.
Here is what i did
WITH CTE_Dep
as
(
select Obj.name,dep.referenced_entity_name,0 as SortIndex
from sys.sql_modules as Module,sys.objects AS Obj ,sys.sql_expression_dependencies
as Dep ,sys.objects as DepObj
where obj.object_id=Module.object_id and dep.referencing_id=obj.object_id
and DepObj.object_id=dep.referenced_id
and DepObj.type_desc <> 'USER_TABLE'
union all
select name,referenced_entity_name, (SortIndex + 1 ) as SortIndex from CTE_Dep WHERE name in (select referenced_entity_name
from sys.sql_modules as Module,sys.objects AS Obj ,sys.sql_expression_dependencies
as Dep ,sys.objects as DepObj
where obj.object_id=Module.object_id and dep.referencing_id=obj.object_id
and DepObj.object_id=dep.referenced_id
and DepObj.type_desc <> 'USER_TABLE') and referenced_entity_name IS NULL
)
select * from CTE_Dep order by SortIndex
September 28, 2017 at 7:16 am
nadersam - Thursday, September 28, 2017 5:52 AMJohn Mitchell-245523 - Thursday, September 28, 2017 3:57 AMNaderYou need to use recursion to do this. Have a look through that link (or search for others) and post back if anything isn't clear. There should be enough there to get you started, though.
John
Thanks for your reply.
I checked the link and tried to apply it to my logic but didn't work.
Here is what i did
WITH CTE_Dep
as
(
select Obj.name,dep.referenced_entity_name,0 as SortIndex
from sys.sql_modules as Module,sys.objects AS Obj ,sys.sql_expression_dependencies
as Dep ,sys.objects as DepObj
where obj.object_id=Module.object_id and dep.referencing_id=obj.object_id
and DepObj.object_id=dep.referenced_id
and DepObj.type_desc <> 'USER_TABLE'
union all
select name,referenced_entity_name, (SortIndex + 1 ) as SortIndex from CTE_Dep WHERE name in (select referenced_entity_name
from sys.sql_modules as Module,sys.objects AS Obj ,sys.sql_expression_dependencies
as Dep ,sys.objects as DepObj
where obj.object_id=Module.object_id and dep.referencing_id=obj.object_id
and DepObj.object_id=dep.referenced_id
and DepObj.type_desc <> 'USER_TABLE') and referenced_entity_name IS NULL
)
select * from CTE_Dep order by SortIndex
What sort of "didn't work" - returned wrong results? Gave an error message? Something else?
Your join on name in the second part of the CTE will give unexpected results if you happen to have the same object name in more than one schema. Try collecting the object ID in the anchor part of the CTE and joining on that instead.
John
October 1, 2017 at 12:13 am
John Mitchell-245523 - Thursday, September 28, 2017 7:16 AMnadersam - Thursday, September 28, 2017 5:52 AMJohn Mitchell-245523 - Thursday, September 28, 2017 3:57 AMNaderYou need to use recursion to do this. Have a look through that link (or search for others) and post back if anything isn't clear. There should be enough there to get you started, though.
John
Thanks for your reply.
I checked the link and tried to apply it to my logic but didn't work.
Here is what i did
WITH CTE_Dep
as
(
select Obj.name,dep.referenced_entity_name,0 as SortIndex
from sys.sql_modules as Module,sys.objects AS Obj ,sys.sql_expression_dependencies
as Dep ,sys.objects as DepObj
where obj.object_id=Module.object_id and dep.referencing_id=obj.object_id
and DepObj.object_id=dep.referenced_id
and DepObj.type_desc <> 'USER_TABLE'
union all
select name,referenced_entity_name, (SortIndex + 1 ) as SortIndex from CTE_Dep WHERE name in (select referenced_entity_name
from sys.sql_modules as Module,sys.objects AS Obj ,sys.sql_expression_dependencies
as Dep ,sys.objects as DepObj
where obj.object_id=Module.object_id and dep.referencing_id=obj.object_id
and DepObj.object_id=dep.referenced_id
and DepObj.type_desc <> 'USER_TABLE') and referenced_entity_name IS NULL
)
select * from CTE_Dep order by SortIndexWhat sort of "didn't work" - returned wrong results? Gave an error message? Something else?
Your join on name in the second part of the CTE will give unexpected results if you happen to have the same object name in more than one schema. Try collecting the object ID in the anchor part of the CTE and joining on that instead.
John
The results it returned is not what i expected,The sorting is not done and the SortIndex field is 0 for all records.
Regards
Nader
October 1, 2017 at 12:35 am
I have simplified the query by removing some unneeded joins and used the id instead of name as you mentioned earlier as follows.
WITH CTE_Dep
as
(
SELECT sys.objects.name, sys.sql_expression_dependencies.referenced_entity_name, 0 AS SortIndex
FROM sys.sql_expression_dependencies RIGHT OUTER JOIN
sys.objects ON sys.sql_expression_dependencies.referencing_id = sys.objects.object_id
union all
select name,referenced_entity_name, (SortIndex + 1 ) as SortIndex from CTE_Dep WHERE name in ( select referenced_entity_name
FROM sys.sql_expression_dependencies inner JOIN sys.objects ON referencing_id = object_id
WHERE (type_desc <> 'USER_TABLE') and referencing_id IS NULL)
)
select * from CTE_Dep where referenced_entity_name is not null order by SortIndex desc
But still something is not right the SortIndex field is always 0
Regards
Nader
October 2, 2017 at 2:31 am
You've still used name in the second part of the CTE, which is where I said you need to change it.
John
October 2, 2017 at 3:09 am
John Mitchell-245523 - Monday, October 2, 2017 2:31 AMYou've still used name in the second part of the CTE, which is where I said you need to change it.John
I thought you are talking about the join conditions which is the part i changed.
I changed it to be as follows but still the results are not correct.
WITH CTE_Dep
as
(
SELECT sys.objects.object_id, sys.sql_expression_dependencies.referenced_id, 0 AS SortIndex,111 as 'anchor'
FROM sys.sql_expression_dependencies RIGHT OUTER JOIN
sys.objects ON sys.sql_expression_dependencies.referencing_id = sys.objects.object_id
union all
select referenced_id,object_id, (SortIndex + 1 ) as SortIndex ,222 as 'recursive'
from CTE_Dep WHERE object_id in ( select referenced_id
FROM sys.sql_expression_dependencies inner JOIN sys.objects ON referencing_id = object_id
WHERE (type_desc <> 'USER_TABLE') and referencing_id IS NULL)
)
select * from CTE_Dep order by SortIndex desc
October 2, 2017 at 3:19 am
I am sorry i still couldn't do it but the recursive CTE concept is still new to me , i have tried many other example but table and data structure is different.
One thing i don't understand is that when i create the anchor part of the CTE my understanding is that i should put a condition to get the root objects only, the part which is confusing me is that in second part(recursive part), i need to get the leaves but i make an inner join with the first part so how will i get the records !!.
I am sure there is a misunderstanding from my part in this part but not sure exactly which one.
Thanks
Nader
October 2, 2017 at 3:46 am
Nader
The reason you aren't getting any results from the recursive section of your CTE is the AND referencing_id IS NULL clause. referencing_id is not a nullable column in sys.sql_expression_dependencies, so you automatically aren't going to get any results. If you take that clause out, you're closer, but now you'll get infinite recursion, which will generate an error message. The reason for that is that you're taking referenced_id and object_id from the anchor part of the CTE instead of joining to sys.sql_expression_dependencies to get the values for the objects at the next level of recursion. If you change your existence test (WHERE object_id IN) to a JOIN, you will then be able to use the values from the subquery instead of those from the anchor part of the CTE.
John
October 2, 2017 at 4:17 am
Thank you John for your explanation.
Now things are getting better after i changed the SQL as follows
WITH CTE_Dep
as
(
SELECT sys.objects.object_id, sys.sql_expression_dependencies.referenced_id,sys.objects.name, 0 AS SortIndex,111 as 'anchor'
FROM sys.sql_expression_dependencies inner JOIN
sys.objects ON sys.sql_expression_dependencies.referencing_id = sys.objects.object_id
union all
select dep.referencing_id,dep.referenced_id,obj.name, (SortIndex + 1 ) as SortIndex ,222 as 'recursive'
from CTE_Dep inner join
sys.sql_expression_dependencies dep
on cte_dep.object_id = dep.referenced_id
inner JOIN sys.objects obj
ON obj.object_id = dep.referencing_id
WHERE (type_desc <> 'USER_TABLE')
)
select * from CTE_Dep order by SortIndex desc
I have attached a simple structure to my database design in text and rar format to use whichever is appropriate.
Now the result is coming but with some duplication as follows, do i still have something wrong in my SQL or do i need to do some work on result.
1061578820 1045578763 AView 2 222
1061578820 1045578763 AView 1 222
1045578763 1029578706 BView 1 222
1029578706 245575913 CVIEW 0 111
1045578763 1029578706 BView 0 111
1061578820 1045578763 AView 0 111
Thanks again
Nader
October 2, 2017 at 4:45 am
Nader
Yes, that's right. The view sys.sql_expression_dependencies contains one row for each column dependency, so if, for example, ViewA explicitly selects three columns from TableA, there'll be three rows. Probably easiest to eliminate them with a DISTINCT.
By the way, check your results very carefully, and make sure they make sense. I think your first join predicate in the second part of the CTE needs to be ON CTE_Dep.referenced_id = dep.referencing_id. In other words, you're looking at objects that are referenced in the level above, and seeing whether they in turn reference other objects.
John
October 2, 2017 at 5:02 am
Thanks for your correction.
I have changed it like follows and used distinct and max to eliminate repetition, i think it's ok now and i hope it's useful for other people who might need it.
WITH CTE_Dep
as
(
SELECT sys.objects.object_id, sys.sql_expression_dependencies.referenced_id,sys.objects.name, 1 AS SortIndex,111 as 'anchor'
FROM sys.sql_expression_dependencies inner JOIN
sys.objects ON sys.sql_expression_dependencies.referencing_id = sys.objects.object_id and referenced_id not in (select OBJECT_ID from sys.objects where type ='u')
union all
select dep.referencing_id,dep.referenced_id,obj.name, (SortIndex + 1 ) as SortIndex ,222 as 'recursive'
from CTE_Dep inner join
sys.sql_expression_dependencies dep
on CTE_Dep.referenced_id = dep.referencing_id
inner JOIN sys.objects obj
ON obj.object_id = dep.referencing_id
WHERE (type_desc <> 'USER_TABLE')
)
select distinct name ,max(SortIndex) as SortIndex from CTE_Dep where SortIndex >0 GROUP BY NAME order by SortIndex desc,name
October 2, 2017 at 6:38 am
After reviewing the results, i found an issue.
The result is not containing the objects that don't depend on any other objects (ex: functions that splits some strings).
but have many objects dependent on them.
I think because they exist in first cte as a referenced object but not as a referencing object.
Regards
Nader
October 2, 2017 at 7:20 am
Nader
Difficult when I can't see what's in your database. But try changing referencing_id to referenced_id in the join predicate of the anchor part. I recommend that you arrange it so that you return the name of the referencing and referenced objects in the same row - that'll make it a lot easier to understand the results.
John
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply