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, and the tables and views in the database on which the specified view, trigger, procedure, or predicate depends. Predicates cannot be granted in a view.
Also displays information about table column dependencies—the indexes, defaults, check constraints, rules, 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.
sp_depends objname[, column_name]
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.
is the name of the column to be examined for dependencies.
Lists the database objects that depend on the table sysobjects:
sp_depends sysobjects
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
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"
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
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
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
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
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
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
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.
sp_depends follows these Adaptive Server 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.
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.
SQLJ functions and SQLJ stored procedures are Java methods wrapped in SQL wrappers. See Java in Adaptive Server Enterprise for more information.
SQLJ functions and SQLJ stored procedures are database objects for which you can list dependencies. The only dependencies of SQLJ stored procedures and SQLJ functions are Java classes.
If objname is a SQLJ stored procedure or SQLJ function, sp_depends lists the Java class in the routine’s external name declared in the create statement, not classes specified as the return type or datatypes in the parameter list.
SQLJ stored procedures and SQLJ functions can be listed as dependencies of other database objects.
Any user can execute sp_depends. Permission checks do not differ based on the granular permissions settings.
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
Commands create procedure, create table, create view, execute
System procedures sp_help
