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