September 28, 2016 at 9:54 am
The following SQL works fine. Gets a list of views, sps and functions in the DB.
However I would like the rows to land in a certain order.
Lets say a view1 calls view2 inside view1. So then I like VIEW2 to appear first in the list then VIEW1 below ( I am trying to make use of view dependencies )
Help please 🙂
Select SO.name, SO.type, SC.name + '.' + SO.name as full_object_name,
(
CASE SO.type
WHEN 'V' THEN 1
WHEN 'Fn' THEN 2
WHEN 'P' THEN 3
ELSE
0
END
) as object_order
FROM sys.objects SO
INNER JOIN sys.schemas SC on SC.schema_id = SO.schema_id
where SO.type IN ( 'Fn', 'P','V' )
and SO.name is not null
ORDER BY object_order
September 28, 2016 at 10:24 am
mw112009 (9/28/2016)
The following SQL works fine. Gets a list of views, sps and functions in the DB.However I would like the rows to land in a certain order.
Lets say a view1 calls view2 inside view1. So then I like VIEW2 to appear first in the list then VIEW1 below ( I am trying to make use of view dependencies )
Help please 🙂
Select SO.name, SO.type, SC.name + '.' + SO.name as full_object_name,
(
CASE SO.type
WHEN 'V' THEN 1
WHEN 'Fn' THEN 2
WHEN 'P' THEN 3
ELSE
0
END
) as object_order
FROM sys.objects SO
INNER JOIN sys.schemas SC on SC.schema_id = SO.schema_id
where SO.type IN ( 'Fn', 'P','V' )
and SO.name is not null
ORDER BY object_order
Probably a recursive CTE joining the SYS.sysdepends might solve the problem for you.
But what are you trying to achieve here?
This may not work if you have any SPs that use dynamic SQL.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply