Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server

Replacing sp_depends with sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
6 Jul 2018CPOL 5.6K  
How to replace sp_depends with sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities

sp_depends have been one of the most used system stored procedures in SQL Server. In fact, many of us still use that even though Microsoft had annouced that it will be removed from the future releases.

image

Alternatively, Microsoft has provided two dynamic management views (these have been introduced with SQL Server 2008) in order to get similar kind of information.

You can get further details on the aforementioned view by visiting the link. (Links are embedded into the view name.)

However, if you have used sp_depends, you might have already faced the issue that the results which are being returned from this stored procedure are not very accurate (most of the time, it seems fine).

The other day, I was going through these two views in order to create an sp which is similar to sp_depends and thought of sharing the query so that it can be useful to anyone who depends on this sp.

SQL
DECLARE
 @objname   AS NVARCHAR(100) = 'Website.SearchForPeople'
 ,@objclass   AS NVARCHAR (60) = 'OBJECT'
 
 
  SELECT
   CONCAT(sch.[name],'.',Obj.[name]) AS [name]
   ,(CASE Obj.type
    WHEN 'C'  THEN 'CHECK constraint'
    WHEN 'D'  THEN 'DEFAULT (constraint or stand-alone)'
    WHEN 'F'  THEN 'FOREIGN KEY constraint'
    WHEN 'PK' THEN 'PRIMARY KEY constraint'
    WHEN 'R'  THEN 'Rule (old-style, stand-alone)'
    WHEN 'TA' THEN 'Assembly (CLR-integration) trigger'
    WHEN 'TR' THEN 'SQL trigger'
    WHEN 'UQ' THEN 'UNIQUE constraint'
    WHEN 'AF' THEN 'Aggregate function (CLR)'
    WHEN 'C' THEN 'CHECK constraint'
    WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
    WHEN 'F' THEN 'FOREIGN KEY constraint'
    WHEN 'FN' THEN 'SQL scalar function'
    WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
    WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
    WHEN 'IF' THEN 'SQL inline table-valued function'
    WHEN 'IT' THEN 'Internal table'
    WHEN 'P' THEN 'SQL Stored Procedure'
    WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
    WHEN 'PG' THEN 'Plan guide'
    WHEN 'PK' THEN 'PRIMARY KEY constraint'
    WHEN 'R' THEN 'Rule (old-style, stand-alone)'
    WHEN 'RF' THEN 'Replication-filter-procedure'
    WHEN 'S' THEN 'System base TABLE'
    WHEN 'SN' THEN 'Synonym'
    WHEN 'SO' THEN 'Sequence OBJECT'
    WHEN 'U' THEN 'Table (user-defined)'
    WHEN 'V' THEN 'VIEW'
    WHEN 'SQ' THEN 'Service queue'
    WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
    WHEN 'TF' THEN 'SQL table-valued-function'
    WHEN 'TR' THEN 'SQL DML trigger'
    WHEN 'TT' THEN 'Table type'
    WHEN 'UQ' THEN 'UNIQUE CONSTRAINT'
    WHEN 'X'  THEN 'Extended stored procedure'
    ELSE 'Undefined'
   END) AS [type]
   ,Obj.create_date
   ,Obj.modify_date
   ,src.referenced_minor_name AS [column]
   ,IIF(src.is_selected   = 1,'yes','no') AS is_selected
   ,IIF(src.is_updated    = 1,'yes','no') AS is_updated
   ,IIF(src.is_select_all = 1,'yes','no') AS is_select_all
   ,IIF(src.is_insert_all = 1,'yes','no') AS is_insert_all
  FROM
   sys.dm_sql_referenced_entities (@objname,@objclass) AS src
   JOIN sys.objects AS Obj
    ON src.referenced_id = Obj.[object_id]
   JOIN sys.schemas AS Sch
    ON Sch.[schema_id] = Obj.[schema_id]
  WHERE 1=1
   
  SELECT
   CONCAT(Src.referencing_schema_name,'.',Src.referencing_entity_name) AS [name]
   ,(CASE Obj.type
    WHEN 'C'  THEN 'CHECK constraint'
    WHEN 'D'  THEN 'DEFAULT (constraint or stand-alone)'
    WHEN 'F'  THEN 'FOREIGN KEY constraint'
    WHEN 'PK' THEN 'PRIMARY KEY constraint'
    WHEN 'R'  THEN 'Rule (old-style, stand-alone)'
    WHEN 'TA' THEN 'Assembly (CLR-integration) trigger'
    WHEN 'TR' THEN 'SQL trigger'
    WHEN 'UQ' THEN 'UNIQUE constraint'
    WHEN 'AF' THEN 'Aggregate function (CLR)'
    WHEN 'C' THEN 'CHECK constraint'
    WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
    WHEN 'F' THEN 'FOREIGN KEY constraint'
    WHEN 'FN' THEN 'SQL scalar function'
    WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
    WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
    WHEN 'IF' THEN 'SQL inline table-valued function'
    WHEN 'IT' THEN 'Internal table'
    WHEN 'P' THEN 'SQL Stored Procedure'
    WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
    WHEN 'PG' THEN 'Plan guide'
    WHEN 'PK' THEN 'PRIMARY KEY constraint'
    WHEN 'R' THEN 'Rule (old-style, stand-alone)'
    WHEN 'RF' THEN 'Replication-filter-procedure'
    WHEN 'S' THEN 'System base TABLE'
    WHEN 'SN' THEN 'Synonym'
    WHEN 'SO' THEN 'Sequence OBJECT'
    WHEN 'U' THEN 'Table (user-defined)'
    WHEN 'V' THEN 'VIEW'
    WHEN 'SQ' THEN 'Service queue'
    WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
    WHEN 'TF' THEN 'SQL table-valued-function'
    WHEN 'TR' THEN 'SQL DML trigger'
    WHEN 'TT' THEN 'Table type'
    WHEN 'UQ' THEN 'UNIQUE CONSTRAINT'
    WHEN 'X'  THEN 'Extended stored procedure'
    ELSE 'Undefined'
   END) AS [type]
   ,Obj.create_date
   ,Obj.modify_date
  FROM
   sys.dm_sql_referencing_entities (@objname,@objclass) AS Src
   JOIN sys.objects AS Obj
    ON Obj.[object_id] = Src.referencing_id 

I have even compiled a stored procedure using this syntax and it can be found in the following repository:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

Comments and Discussions

 
-- There are no messages in this forum --