September 6, 2012 at 9:44 pm
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
September 6, 2012 at 11:20 pm
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
September 7, 2012 at 7:12 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply