Is this a bug in sys.dm_sql_referenced_entities?

  • Reference: sys.dm_sql_referenced_entities

    It sure sounds like the referencing_minor_id is supposed to be the column_id of the referencing object here. So, why does this always return 0?

    USE tempdb;

    GO

    IF OBJECT_ID('dbo.temp','U') IS NOT NULL DROP TABLE dbo.temp;

    IF OBJECT_ID('dbo.temp2','U') IS NOT NULL DROP TABLE dbo.temp2;

    IF OBJECT_ID('dbo.vtemp','V') IS NOT NULL DROP VIEW dbo.vtemp;

    IF OBJECT_ID('dbo.ptemp','P') IS NOT NULL DROP PROCEDURE dbo.ptemp;

    CREATE TABLE [dbo].[temp](

    [Sr] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

    [val] [varchar](2048) NULL);

    GO

    CREATE TABLE dbo.temp2(

    [Sr] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

    [val] [varchar](2048) NULL);

    GO

    CREATE VIEW dbo.vtemp AS

    SELECT t1.Sr,

    t1.val,

    v2=t2.val,

    s2=t2.Sr

    FROM dbo.temp t1

    CROSS JOIN dbo.temp2 t2;

    GO

    CREATE PROCEDURE dbo.pTemp AS

    SELECT t1.Sr,

    t1.val,

    v2=t2.val,

    s2=t2.Sr

    FROM dbo.temp t1

    CROSS JOIN dbo.temp2 t2;

    GO

    SELECT * FROM sys.dm_sql_referenced_entities('dbo.vtemp', 'OBJECT');

    SELECT * FROM sys.dm_sql_referenced_entities('dbo.ptemp', 'OBJECT');

    -- just in case the referencing_object needs to include the column name:

    SELECT * FROM sys.dm_sql_referenced_entities('dbo.vtemp.Sr', 'OBJECT');

    SELECT * FROM sys.dm_sql_referenced_entities('dbo.ptemp.Sr', 'OBJECT');

    GO

    IF OBJECT_ID('dbo.temp','U') IS NOT NULL DROP TABLE dbo.temp;

    IF OBJECT_ID('dbo.temp2','U') IS NOT NULL DROP TABLE dbo.temp2;

    IF OBJECT_ID('dbo.vtemp','V') IS NOT NULL DROP VIEW dbo.vtemp;

    IF OBJECT_ID('dbo.ptemp','P') IS NOT NULL DROP PROCEDURE dbo.ptemp;

    GO

    I've tested this on 2008, 2008R2 and 2012. Am I doing something wrong here (and if so, what), or is this a bug in the DMV?

    (I'm trying to write a query that will recursively go through a view (it calls other views) to get the table/column source for each column in the view. If this would return the column_id as the referencing_minor_id, then I'd have all the information that I need to accomplish this. Heck, even if it would return the data in column order... but it doesn't).

    Granted, the view/proc could reference a column in a join condition or where clause, and then it wouldn't have a column_id (or rather, then the referenced_minor_id should be 0). But shouldn't this have the column_id here?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Hi Wayne,

    If you're looking at the first column of your resultset (i.e. referencing_minor_id) then that should always be 0 unless your object in the query is of type COLUMN.

    In your resultset, the referenced_minor_id column will give you the id of columns that are referenced in view vtemp and procedure ptemp.

    Cheers,

    Fahim



    [font="Tahoma"]Fahim Ahmed[/font]
    [font="Times New Roman"]Knowledge is a journey, not a destiny [/font]

  • Thanks for taking the time to respond Fahim.

    Well, except for the rows where the referenced_minor_id = 0, then it is obviously talking about a column, and referencing_minor_id is returning a 0 for those also rows also.

    The last two selects in my code are in case we're supposed to supply the referencing_entity as a column, and that returns zero rows, so that doesn't work either.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 3 posts - 1 through 2 (of 2 total)

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