Dependencies in each SQL Server database are stored in the table sysdepends. sp_depends is a simple stored procedure that reads and formats data from this table for us.
sysdepends is populated at parse time of a stored procedure, trigger, table or view. Objects are parsed only at the time they are created. If a procedure is parsed and an object from the local database that is referenced by the procedure does not exist in the database the parse does not fail, however there is a warning message issued that the row could not be added to sysdepends. If sp_depends is executed after this procedure is parsed into the database or even after the table is created it will not see the dependency until the procedure is parsed again. (i.e. drop procedure, create procedure)
Here is a little demo of the behavior. Just cust and paste into your Query Analyzer. Note that all comments are included in the output, so if you run the script and review the output you'll most likely understand what is hapening better than if you just read the script.
use tempdb print 'create a table ' create table SomeTable (id int) print ' create a procedure that references this table and another table ' go create procedure ParseDemo as select id from SomeTable select id from SomeOtherTable go print ' verify that the existing table shows up as a dependancy but the other one does not ' exec sp_depends SomeTable exec sp_depends ParseDemo go print ' note that the non existant table causes an error when passed to sp_depends ' exec sp_depends SomeOtherTable go print ' now create the other table and repeat the dependancy check ' create table SomeOtherTable (id int) go print ' note that the dependancy still does not show up but the sp_depends that failed works now ' exec sp_depends SomeTable exec sp_depends SomeOtherTable exec sp_depends ParseDemo go print ' you cant even recompile the stored procedure and get it to see the dependancy ' exec sp_recompile parse_demo exec parse_demo go exec sp_depends SomeTable exec sp_depends SomeOtherTable exec sp_depends ParseDemo go print ' but if the procedure that is a phantom dependancy is dropped and recreated now that both tables exist, sp_depends shows the missing dependancy ' drop procedure ParseDemo go create procedure ParseDemo as select id from SomeTable select id from SomeOtherTable go exec sp_depends SomeTable exec sp_depends SomeOtherTable exec sp_depends ParseDemo go print ' end of demo cleanup ' drop procedure ParseDemo drop table SomeTable drop table SomeOtherTable go
Even without this limitation that a dependency is not catalogued if the object it depends upon does not exist, there are major limitations to using sp_depends.
Finding the dependencies on an object will require an exhaustive search. You'll want to check the syscomments table in all databases on all servers. You can do this using the sp_MSforeachdb system stored procedure. For example, lets look for all stored procedures and triggers that reference sysdepends on a server.
exec sp_msforeachdb 'use ? select db_name() + ''.'' + object_name(id) from syscomments where text like ''%sysdepends%'''You could run this once on each server in an environment or you could build a procedure that looks on all servers. Such as:
CREATE PROCEDURE dbo.find_dependancies @SearchString nvarchar(255) as declare @LinkedServer nvarchar(20), @SearchQuery nvarchar(4000) set nocount on if charindex('SERVER_A',@@servername) = 0 and charindex('SERVER_B',@@servername) = 0 and charindex('SERVER_C',@@servername) = 0 begin raiserror('string_finder2 cannot be executed on this server',16,1) return -1 end while @LinkedServer is not null begin print 'Checking for string ' + @SearchString + ' on server ' + @LinkedServer set @SearchQuery = 'set nocount on exec ' + @LinkedServer + '.master.dbo.sp_msforeachdb ''set nocount on use ? if exists (select 1 from syscomments where text like ''''%' + @SearchString + '%'''') select distinct @@servername + ''''.'''' + db_name() + ''''.'''' + object_name(id) from syscomments where text like ''''%' + @SearchString + '%''''''' exec sp_executesql @SearchQuery if @LinkedServer = 'SERVER_A' set @LinkedServer = 'SERVER_B' else if @LinkedServer = 'SERVER_B' set @LinkedServer = 'SERVER_C' else set @LinkedServer = null -- done end GOThis will produce a list of all objects with the literal that you passed as
You can also use SourceSafe to do a similar search. In SourceSafe you could also look for dependencies external to SQL Server as well. Obviously, the limitation here is that if it didn't get checked in to SourceSafe you wont find a dependency.
The reality is that finding dependencies in SQL server can be fraught with misinformation. In the end, the developer must do exhaustive and even redundant investigative work to properly identify all dependencies.
And for sure, don't depend on sp_depends.