sys.dm_sql_referenced_entities

• Developer Man

  Valued Member

  Points: 59

  More actions

  March 25, 2013 at 6:40 pm

  #275667

  Hi buddies!

  I'm in troubles with a query, when I execute the next statement SQL returns a set of rows:

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

  But, when I use sys.dm_sql_referenced_entities inside a User Defined Function, SQL returns always nothing:

  CREATE FUNCTION getDependencyActionType

  (

  @FullObjectName AS NVARCHAR(1000)

  )

  RETURNS NVARCHAR(10)

  AS

  BEGIN

  DECLARE

  @SELECT INT,

  @SELECTALL INT,

  @UPDATE INT,

  @RESULT NVARCHAR(10)

  SELECT @SELECT = COUNT(CASE

  WHEN E.is_selected = 1 THEN

  1

  ELSE

  NULL

  END)

  ,@SELECTALL = COUNT(CASE

  WHEN E.is_select_all = 1 THEN

  1

  ELSE

  NULL

  END)

  ,@UPDATE = COUNT(CASE

  WHEN E.is_updated = 1 THEN

  1

  ELSE

  NULL

  END)

  FROM sys.dm_sql_referenced_entities(@FullObjectName, 'OBJECT') E

  IF ISNULL(@UPDATE, 0) > 0

  SET @RESULT = 'I/D/U'

  ELSE IF ISNULL(@SELECTALL, 0) > 0

  SET @RESULT = 'SELECT *'

  ELSE

  SET @RESULT = 'SELECT'

  RETURN @RESULT

  END

  GO

  SELECT dbo.getDependencyActionType('dbo.MySp')

  --And the result is nothing :'(

  Please, help me.