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

Examples

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:

InformationValues
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

Related reference
sp_help