July 8, 2015 at 2:29 pm
Hello,
I found this script for dependencies and want to extend it:
I want to add the obj_typ (U, FN, P, V, TR...) for the dependency
How can I do this?
I am a TSQL-Beginner...
here is the script:
WITH DepTree (referenced_id, referenced_name, referencing_id, referencing_name, NestLevel, obj_type )
AS
(
SELECT
o.[object_id] AS referenced_id ,
o.name AS referenced_name,
o.[object_id] AS referencing_id,
o.name AS referencing_name,
0 AS NestLevel,
(CAST(o.[type] COLLATE Latin1_General_CI_AS_KS_WS AS NVARCHAR(2)))as obj_type
FROM sys.objects o
WHERE o.name = 'myTable'
UNION ALL
SELECT
d1.referenced_id,
OBJECT_NAME( d1.referenced_id) ,
d1.referencing_id,
OBJECT_NAME( d1.referencing_id) ,
NestLevel + 1,
(CAST('' COLLATE Latin1_General_CI_AS_KS_WS AS NVARCHAR(2)))as obj_type -- this should be the the object_type of the referencing_id
FROM sys.sql_expression_dependencies d1
JOIN DepTree r ON d1.referenced_id = r.referencing_id
)
SELECT DISTINCT obj_type, referenced_id, referenced_name, referencing_id, referencing_name, NestLevel
FROM DepTree
WHERE NestLevel > 0
July 15, 2015 at 6:57 am
What is the Question?
July 15, 2015 at 5:25 pm
I want to add the obj_typ (U, FN, P, V, TR...) for the dependency ...
but I don't know how
July 16, 2015 at 8:07 am
Try this
WITH DepTree (referenced_id, referenced_name, referencing_id, referencing_name, NestLevel, obj_type )
AS
(
SELECT
o.[object_id] AS referenced_id ,
o.name AS referenced_name,
o.[object_id] AS referencing_id,
o.name AS referencing_name,
0 AS NestLevel,
(CAST(o.[type] COLLATE Latin1_General_CI_AS_KS_WS AS NVARCHAR(2)))as obj_type
FROM sys.objects o
WHERE o.name = 'my Table'
UNION ALL
SELECT
d1.referenced_id,
OBJECT_NAME( d1.referenced_id) ,
d1.referencing_id,
OBJECT_NAME( d1.referencing_id) ,
NestLevel + 1,
--(CAST('' COLLATE Latin1_General_CI_AS_KS_WS AS NVARCHAR(2)))as obj_type -- this should be the the object_type of the referencing_id
(CAST(z.[type] COLLATE Latin1_General_CI_AS_KS_WS AS NVARCHAR(2)))as obj_type
FROM sys.sql_expression_dependencies d1
JOIN DepTree r ON d1.referenced_id = r.referencing_id
INNER JOIN sysobjects z on z.id = d1.referencing_id
)
SELECT DISTINCT obj_type, referenced_id, referenced_name, referencing_id, referencing_name, NestLevel
FROM DepTree
WHERE NestLevel > 0
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy