March 2, 2020 at 8:15 pm
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
March 2, 2020 at 8:42 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
March 2, 2020 at 9:21 pm
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.
March 2, 2020 at 9:30 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
March 2, 2020 at 9:54 pm
A list of relevant views is inserted into a temp table and then looped thru each of the entries using a cursor.
March 3, 2020 at 3:35 pm
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
March 3, 2020 at 3:37 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply