sp_depends (Transact-SQL)

 

Displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure. References to objects outside the current database are not reported.

System_CAPS_ICON_important.jpg Important


This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

  
sp_depends [ @objname = ] '<object>'   
  
<object> ::=  
{  
    [ database_name. [ schema_name ] . | schema_name.  
    object_name  
}  

database_name
Is the name of the database.

schema_name
Is the name of the schema to which the object belongs.

object_name
Is the database object to examine for dependencies. The object can be a table, view, stored procedure, user-defined function, or trigger. object_name is nvarchar(776), with no default.

0 (success) or 1 (failure)

sp_depends displays two result sets.

The following result set shows the objects on which <object> depends.

Column nameData typeDescription
namenvarchar(257 )Name of the item for which a dependency exists.
typenvarchar(16)Type of the item.
updatednvarchar(7)Whether the item is updated.
selectednvarchar(8)Whether the item is used in a SELECT statement.
columnsysnameColumn or parameter on which the dependency exists.

The following result set shows the objects that depend on <object>.

Column nameData typeDescription
namenvarchar(257 )Name of the item for which a dependency exists.
typenvarchar(16)Type of the item.

Requires membership in the public role.

A. Listing dependencies on a table

The following example lists the database objects that depend on the Sales.Customer table in the AdventureWorks2012 database. Both the schema name and table name are specified.

USE AdventureWorks2012;  
GO  
EXEC sp_depends @objname = N'Sales.Customer' ;  

B. Listing dependencies on a trigger

The following example lists the database objects on which the trigger iWorkOrder depends.

EXEC sp_depends @objname = N'AdventureWorks2012.Production.iWorkOrder' ;  

Database Engine Stored Procedures (Transact-SQL)
EXECUTE (Transact-SQL)
sp_help (Transact-SQL)
System Stored Procedures (Transact-SQL)
sys.sql_dependencies (Transact-SQL)

Community Additions

ADD
Show: