Cann't find ' sys.dm_sql_referenced_entities '
in [Programming SQL]
|
Prev: Temp tables vs Permanent table with deletes
Next: Date Difference Between 2 Rows for Readmission Calculation
From: RJ Roberts on 5 Apr 2010 17:17 I meant to include this link that gives a few other options to consider. http://www.mssqltips.com/tip.asp?tip=1294 -- RJ Roberts DB Architect/Developer "Dan" wrote: > I wanted to check object dependencies using ' sys.dm_sql_referenced_entities > '. However, I get the erROR "Invalid object name > 'sys.dm_sql_referenced_entities'." when I use Microsoft's example code (see > below). Furthermore, I don't see the DMV under system views either. What is > going on? > I am using SQL Server 2005 Ent, SP2. > > Thanks, > Dan > > CREATE TABLE dbo.Table1 (a int, b int, c AS a + b); > GO > SELECT referenced_schema_name AS schema_name, > referenced_entity_name AS table_name, > referenced_minor_name AS referenced_column, > COALESCE(COL_NAME(OBJECT_ID(N'dbo.Table1'),referencing_minor_id), 'N/A') > AS referencing_column_name > FROM sys.dm_sql_referenced_entities ('dbo.Table1', 'OBJECT'); > GO > > ERROR GENERATED: > Msg 208, Level 16, State 1, Line 1 > Invalid object name 'sys.dm_sql_referenced_entities'.
From: RJ Roberts on 5 Apr 2010 17:41 I am pretty certain that DMV is only available on Sql Server 2008. -- RJ Roberts DB Architect/Developer "Dan" wrote: > I wanted to check object dependencies using ' sys.dm_sql_referenced_entities > '. However, I get the erROR "Invalid object name > 'sys.dm_sql_referenced_entities'." when I use Microsoft's example code (see > below). Furthermore, I don't see the DMV under system views either. What is > going on? > I am using SQL Server 2005 Ent, SP2. > > Thanks, > Dan > > CREATE TABLE dbo.Table1 (a int, b int, c AS a + b); > GO > SELECT referenced_schema_name AS schema_name, > referenced_entity_name AS table_name, > referenced_minor_name AS referenced_column, > COALESCE(COL_NAME(OBJECT_ID(N'dbo.Table1'),referencing_minor_id), 'N/A') > AS referencing_column_name > FROM sys.dm_sql_referenced_entities ('dbo.Table1', 'OBJECT'); > GO > > ERROR GENERATED: > Msg 208, Level 16, State 1, Line 1 > Invalid object name 'sys.dm_sql_referenced_entities'.
From: Gail Erickson [MS] on 5 Apr 2010 19:56 >I am pretty certain that DMV is only available on Sql Server 2008. Yes, that is true. The SQL dependency DMVs are in SQL Server 2008 and higher. In SQL Server 2005 and earlier, you can use sp_depends (http://msdn.microsoft.com/en-us/library/ms189487(SQL.90).aspx) or the sys.sql_dependencies catalog view (http://msdn.microsoft.com/en-us/library/ms345449(SQL.90).aspx). -- Gail Erickson [MS] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights Download the latest version of SQL Server 2005 Books Online from http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx Download the latest version of SQL Server 2008 Books Online from http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx "RJ Roberts" <RJRoberts(a)discussions.microsoft.com> wrote in message news:C96C26DB-EF4D-404C-AD26-99A7292C8FD1(a)microsoft.com... >I am pretty certain that DMV is only available on Sql Server 2008. > -- > RJ Roberts > DB Architect/Developer > > > "Dan" wrote: > >> I wanted to check object dependencies using ' >> sys.dm_sql_referenced_entities >> '. However, I get the erROR "Invalid object name >> 'sys.dm_sql_referenced_entities'." when I use Microsoft's example code >> (see >> below). Furthermore, I don't see the DMV under system views either. >> What is >> going on? >> I am using SQL Server 2005 Ent, SP2. >> >> Thanks, >> Dan >> >> CREATE TABLE dbo.Table1 (a int, b int, c AS a + b); >> GO >> SELECT referenced_schema_name AS schema_name, >> referenced_entity_name AS table_name, >> referenced_minor_name AS referenced_column, >> COALESCE(COL_NAME(OBJECT_ID(N'dbo.Table1'),referencing_minor_id), >> 'N/A') >> AS referencing_column_name >> FROM sys.dm_sql_referenced_entities ('dbo.Table1', 'OBJECT'); >> GO >> >> ERROR GENERATED: >> Msg 208, Level 16, State 1, Line 1 >> Invalid object name 'sys.dm_sql_referenced_entities'.
From: Dan on 6 Apr 2010 14:21
Thank you, RJ and Gail. Much appreciated. Dan "RJ Roberts" wrote: > I meant to include this link that gives a few other options to consider. > > http://www.mssqltips.com/tip.asp?tip=1294 > > -- > RJ Roberts > DB Architect/Developer > > > "Dan" wrote: > > > I wanted to check object dependencies using ' sys.dm_sql_referenced_entities > > '. However, I get the erROR "Invalid object name > > 'sys.dm_sql_referenced_entities'." when I use Microsoft's example code (see > > below). Furthermore, I don't see the DMV under system views either. What is > > going on? > > I am using SQL Server 2005 Ent, SP2. > > > > Thanks, > > Dan > > > > CREATE TABLE dbo.Table1 (a int, b int, c AS a + b); > > GO > > SELECT referenced_schema_name AS schema_name, > > referenced_entity_name AS table_name, > > referenced_minor_name AS referenced_column, > > COALESCE(COL_NAME(OBJECT_ID(N'dbo.Table1'),referencing_minor_id), 'N/A') > > AS referencing_column_name > > FROM sys.dm_sql_referenced_entities ('dbo.Table1', 'OBJECT'); > > GO > > > > ERROR GENERATED: > > Msg 208, Level 16, State 1, Line 1 > > Invalid object name 'sys.dm_sql_referenced_entities'. |