View Dependencies

  • tinausa

    SSCrazy

    Points: 2697

    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
  • ScottPletcher

    SSC Guru

    Points: 98434

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • tinausa

    SSCrazy

    Points: 2697

    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: 98434

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • tinausa

    SSCrazy

    Points: 2697

    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: 6561

    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: 6561

    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 7 (of 7 total)

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