sp_depends

Displays information about database object dependencies—the views, triggers, user-defined functions, procedures, and predicates—in the database that depend on a specified table or view, the tables and views in the database on which the specified view, trigger, procedure, or predicate depends, and multiple triggers associated with a table. Predicates cannot be granted in a view.

Also displays information about table column dependencies—the indexes, defaults, check constraints, rules, precomputed result sets, referential integrity constraints, and predicates—defined in either the column specified, if column_name is provided, or on all the columns in the table, if column_name is not provided.

Syntax

sp_depends objname[, column_name]

Parameters

objname – is the name of the table, view, Transact-SQL stored procedure, SQLJ stored procedure, SQLJ function, or trigger to be examined for dependencies. You cannot specify a database name. Use owner names if the object owner is not the user running the command and is not the database owner.
column_name – is the name of the column to be examined for dependencies.

Examples

Objects Dependent on a Table – Lists the database objects that depend on the table sysobjects:
```
sp_depends sysobjects
```

Objects Dependent on a View – Lists the database objects that depend on the titleview view, and the database objects on which the titleview view depends:

sp_depends titleview

Things that the object references in the current database.
object          type        updated selected
--------------  ----------- ------- -----
dbo.authors     user table  no      no
dbo.titleauthor user table  no      no
dbo.titles      user table  no      no
Things inside the current database that reference the object.
object          type 
------------    --------------- 
dbo.tview2      view

Objects Dependent on a Specific Table – Lists the database objects that depend on the titles table owned by the user “mary”. The quotes are needed, since the period is a special character:
```
sp_depends "mary.titles"
```

Precomputed Result Sets – The following examples assume that prs1 and view1 are created with the following dependency structure:

prs1 is defined on base table tab1 (with unique constraint on column c1) and view1 is defined on prs1
prs1 is configured for immediate refresh

This example displays the precomputed result sets that include dependencies for column c1:

sp_depends prs1,c1
Things the object references in the current database.
object       type           updated    selected         
------------ -------------- ---------- --------- 
dbo.tab1     user table     no         no               

Things inside the current database that reference the object.
object             type     
------------------ -------- 
dbo.view1          view     

Dependent objects that reference column c1.
Columns referenced in stored procedures, views or triggers are not 
included in this report.
Type  Property   Object Names or Column Names Also see/Use command                   
----- ---------- ---------------------------- -------------------- 
index constraint prs1_10240036482 (c1)        sp_helpindex,
                                              drop index,
                                              sp_helpconstraint, 
                                              alter table drop
                                              constraint

sp_depends prs1,c1
Things the object references in the current database.
object       type           updated    selected         
------------ -------------- ---------- --------- 
dbo.tab1     user table     no         no               

Things inside the current database that reference the object.
object             type     
------------------ -------- 
dbo.view1          view     

Dependent objects that reference column c1.
Columns referenced in stored procedures, views or triggers are not 
included in this report.
Type  Property   Object Names or Column Names Also see/Use command                   
----- ---------- ---------------------------- -------------------- 
index constraint prs1_10240036482 (c1)        sp_helpindex,
                                              drop index,
                                              sp_helpconstraint, 
                                              alter table drop
                                              constraint

Dependencies Between Predicate and Table – Displays the dependencies between predicate pred1 and any tables it references:

sp_depends pred1

Things the object references in the current database.
object           type                 updated        selected
---------------- -------------------- -------------- ----------------
dbo.tab1         user table           no             no
dbo.tab2         user table           no             no

Dependencies Between Predicate, Table, and Column – Displays the dependencies between predicates and table tab1 and column col1:

sp_depends tab1, col1

Things inside the current database that reference the object.
object             type
------------------ ------------------
dbo.pred1          predicate
Dependent objects that reference column col1.
Columns referenced in stored procedures, views or triggers are not included in this report.
Type                 Property                  
    Object Names or Column Names
       Also see/Use command
-------------------- --------------------
    -----------------------------------------------------------------
       ------------------------------------------------------
permission           permission
    column permission
       sp_helprotect, grant/revoke

Column-Level Dependencies – Shows the column-level dependencies for all columns of the sysobjects table:

sp_depends sysobjects

Things inside the current database that reference the object.
object                                   type
---------------------------------------- ----------------
dbo.sp_dbupgrade                         stored procedure
dbo.sp_procxmode                         stored procedure

Dependent objects that reference all columns in the table. Use sp_depends
on each column to get more information.
Columns referenced in stored procedures, views or triggers are not included
in this report.

Column           Type         Object Names or Column Names
----------------------- ------------ ------------------------------
cache            permission   column permission
ckfirst          permission   column permission
crdate           permission   column permission
deltrig          permission   column permission
expdate          permission   column permission
id               index        sysobjects (id)
id               logical RI   From syscolumns (id) To sysobjects (id)
id               logical RI   From syscomments (id) To sysobjects (id)
id               logical RI   From sysdepends (id) To sysobjects (id)
id               logical RI   From sysindexes (id) To sysobjects (id)
id               logical RI   From syskeys (depid) To sysobjects (id)
id               logical RI   From syskeys (id) To sysobjects (id)
id               logical RI   From sysobjects (id) To sysprocedures (id)
id               logical RI   From sysobjects (id) To sysprotects (id)
id               logical RI   sysobjects (id)
id               permission   column permission
indexdel         permission   column permission
instrig          permission   column permission
loginame         permission   column permission
name             index        ncsysobjects (name, uid)
name             permission   column permission
objspare         permission   column permission
schemacnt        permission   column permission
seltrig          permission   column permission
sysstat          permission   column permission
sysstat2         permission   column permission
type             permission   column permission
uid              index        ncsysobjects (name, uid)
uid              logical RI   From sysobjects (uid) To sysusers (uid)
uid              permission   column permission
updtrig          permission   column permission
userstat         permission   column permission
versionts        permission   column permission

Detailed Column-Level Dependencies – Shows more details about the column-level dependencies for the id column of the sysobjects table:

sp_depends sysobjects, id

Things inside the current database that reference the object.
object                                             type
------------------------------------               -------------
dbo.sp_dbupgrade                                   stored procedure
dbo.sp_procxmode                                   stored procedure
Dependent objects that reference column id.
Columns referenced in stored procedures, views or triggers are not included 
in this report.
Type          Property     Object Names or Column Names
                           Also see/Use command
----------   ---------     ----------------------------------
                           ----------------------------------
index         index       sysobjects (id)
                          sp_helpindex, drop index,
                          sp_helpconstraint, alter table drop constraint
logical RI    primary     sysobjects (id)
                          sp_helpkey, sp_dropkey
logical RI    foreign     From syskeys (id) To sysobjects (id)
                          sp_helpkey, sp_dropkey
logical RI    common      From syscolumns (id) To sysobjects (id)
                          sp_helpkey, sp_dropkey
logical RI    common      From sysdepends (id) To sysobjects (id)
                          sp_helpkey, sp_dropkey
logical RI    common      From sysindexes (id) To sysobjects (id)
                          sp_helpkey, sp_dropkey
logical RI    common      From syskeys (depid) To sysobjects (id)
                          sp_helpkey, sp_dropkey
logical RI    common      From syscomments (id) To sysobjects (id)
                          sp_helpkey, sp_dropkey
logical RI    common      From sysobjects (id) To sysprotects (id)
                          sp_helpkey, sp_dropkey
logical RI    common      From sysobjects (id) To sysprocedures (id)
                          sp_helpkey, sp_dropkey
permission    permission  column permission
                          sp_helprotect, grant/revoke

Column-Level Dependencies for All Columns – Shows the column-level dependencies for all columns of the user-created table, titles:

1> sp_depends titlesThings inside the current database that reference the object.
object                                type
-----------------------------------  ---------------
dbo.deltitle                          trigger
dbo.history_proc                      stored procedure
dbo.title_proc                        stored procedure
dbo.titleid_proc                      stored procedure
dbo.titleview                         view
dbo.totalsales_trig                   trigger

Dependent objects that reference all columns in the table. Use sp_depends 
on each column to get more information.
Columns referenced in stored procedures, views or triggers are not included 
in this report.

Column     Type         Object Names or Column Names
------     -----        ----------------------------------------
pub_id     logical RI   From titles (pub_id) To publishers (pub_id)
pubdate    default      datedflt
title      index        titleind (title)
title      statistics   (title)
title_id   index        titleidind (title_id)
title_id   logical RI   From roysched (title_id) To titles (title_id)
title_id   logical RI   From salesdetail (title_id) To titles (title_id)
title_id   logical RI   From titleauthor (title_id) To titles (title_id)
title_id   logical RI   titles (title_id)
title_id   rule         title_idrule
title_id   statistics   (title_id)
type       default      typedflt

Column-Level Dependencies for a Specific Column – Shows more details about the column-level dependencies for the pub_id column of the user-created titles table:

sp_depends titles, pub_id

Things inside the current database that reference the object.
object                                  type
------------------------------------    ----------------
dbo.deltitle                            trigger
dbo.history_proc                        stored procedure
dbo.title_proc                          stored procedure
dbo.titleid_proc                        stored procedure
dbo.titleview                           view
dbo.totalsales_trig                     trigger
Dependent objects that reference column pub_id.
Columns referenced in stored procedures, views or triggers are not
included in this report.
Type          Property    Object Names or Column Names
                          Also see/Use command
----------    ---------   ---------------------
                          ----------------------------
logical RI    foreign     From titles (pub_id) To publishers (pub_id)
                          sp_helpkey, sp_dropkey

Usage

Executing sp_depends lists all objects in the current database that depend on objname, and on which objname depends. For example, views depend on one or more tables and can have procedures or other views that depend on them. An object that references another object is dependent on that object. References to objects outside the current database are not reported.
Before you modify or drop a column, use sp_depends to determine if the table contains any dependent objects that could be affected by the modification. For example, if you modify a column to use a new datatype, objects tied to the table may need to be redefined to be consistent with the column’s new datatype.
The sp_depends procedure determines the dependencies by looking at the sysdepends table.

If the objects were created out of order (for example, if a procedure that uses a view was created before the view was created), no rows exist in sysdepends for the dependencies, and sp_depends does not report the dependencies.
The updated and selected columns in the report from sp_depends are meaningful if the object being reported on is a stored procedure or trigger. The values for the updated column indicate whether the stored procedure updates the object. The selected column indicates whether the object is being used for a read cursor or a data modification statement.
Objects owned by database users other than the user executing a command and the database owner must always be qualified with the owner’s name, as in Example 3.

sp_depends follows these SAP ASE rules for finding objects:

If the user does not specify an owner name, and the user executing the command owns an object with the specified name, that object is used.
If the user does not specify an owner name, and the user does not own an object of that name, but the database owner does, the database owner’s object is used.
If neither the user nor the database owner owns an object of that name, the command reports an error condition, even if an object exists in the database with that object name, but with a different owner.
If both the user and the database owner own objects with the specified name, and the user wants to access the database owner’s object, the name must be specified, as in dbo.objectname.

See also create procedure, create table, create view, execute in Reference Manual: Commands.

Permissions

Any user can execute sp_depends. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect