View Dependencies

Question

Post reply

View Dependencies

tinausa

SSCrazy

Points: 2697

March 2, 2020 at 8:15 pm

#3730327

I have SQL Server 2012 and running the following query. THe results do not include all dependencies.

I have a stored proc that calls a view, but that does notshwo up. How do I get full dependency list?

SELECT ReferencingObjectType = o1.type,
ReferencingObjectName = o1.type_desc,
ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,
ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name,
ReferencedObjectType = o2.type,
ReferencedObjectName = o2.type_desc
FROM   sys.sql_expression_dependencies ed
INNER JOIN   sys.objects o1
ON ed.referencing_id = o1.object_id
INNER JOIN sys.objects o2
ON ed.referenced_id = o2.object_id
ORDER BY ReferencingObjectType, ReferencingObject

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101117 More actions · Answer 1

Not perfect, but this should get you much closer:

SELECT 
    ReferencingObjectType = o1.type,
ReferencingObjectName = o1.type_desc,
ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,
ReferencedObject = ISNULL(ed.referenced_database_name, DB_NAME()) + '.' +
        COALESCE(ed.referenced_schema_name, SCHEMA_NAME(o2.schema_id), '?schema') + '.' + 
            ed.referenced_entity_name,
ReferencedObjectType = o2.type,
ReferencedObjectName = o2.type_desc,
    ed.*
FROM sys.sql_expression_dependencies ed
INNER JOIN sys.objects o1
ON ed.referencing_id = o1.object_id
LEFT OUTER JOIN sys.objects o2
ON ed.referenced_id = o2.object_id
ORDER BY ReferencingObjectType, ReferencingObject

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

tinausa SSCrazy Points: 2697 More actions · Answer 2

Thanks it gives me more info. But still if I filter by the SP as a referencing object, it does not point out the views being called by that SP.

ScottPletcher SSC Guru Points: 101117 More actions · Answer 3

Is the view reference directly in the code or is it some type of generated / dynamic SQL? Naturally the view can only give references for the code that is present, not code that will be generated at run time.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

tinausa SSCrazy Points: 2697 More actions · Answer 4

A list of relevant views is inserted into a temp table and then looped thru each of the entries using a cursor.

jonathan.crawford SSCertifiable Points: 6613 More actions · Answer 5

can you give an example (pseudo-code if you don't want to post the actual) showing how the sp is calling the views?

If you're doing something like the below, for example, then to Scott's point above, the view names themselves aren't in the code, so no way for the system to know they are, and the only way would be for you to build a reference somewhere that tracks situations like this that developers keep up to date, so you can add in the forced dependencies for however you're using this data:

DECLARE @sql varchar(255);
SELECT top 10 ROW_NUMBER() as rowNum,*
INTO #myTable
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'view'

DECLARE @loopCounter int,@max int
SET @loopCounter = 0
SELECT @max = max(rowNum) FROM #myTable;

WHILE @loopCounter < @max
BEGIN
SELECT @sql = 'SELECT * FROM '+ TABLE_SCHEMA + '.' + table_name
FROM #myTable
WHERE rowNum = @loopCounter;
EXEC(@SQL);
SET @loopCounter = @loopCounter + 1;
END

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

jonathan.crawford SSCertifiable Points: 6613 More actions · Answer 6

That was one hell of a run-on sentence, my apologies to my high school English teacher.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses