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.

for example:

triggers on the table may not be considered

Q180490

will not show dependencies in other databases

will not show dependencies on other servers

will not show dependencies created via EXEC or sp_executesql

Any time a string is built and then dynamically executed, no dependencies to objects referenced in the string will be reported

will not show embedded sql dependencies in client code or script file dependencies

will not show proper dependencies if a table or view is dropped and recreated

if the data from a table is moved into a new table with the same name, the new table will have a new identifier. The parser stores the identifier not the name in the query plan

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%'''

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


GO