Dependencies in each SQL Server database are stored in the table dbo.sysdepends in that database. The system stored procedure sp_depends is a simple stored procedure that reads and formats data from this table for us. So when you have to change a column attribute it seems reasonable to run sp_depends to get a list of the DDL objects that might be affected or even broken after your change. Sounds simple enough, but as anyone that has depended on sp_depends knows, it’s not good enough. This article will look a little closer at sp_depends, the problem it pretends to help you solve, and also explore some of the other alternatives at your disposal to identify dependencies.

sysdepends is populated at parse time when a stored procedure, trigger, table or view is “compiled”. It is important to note that objects are parsed only at the time they are created. With SQL2000, 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)

Consider this demo of sp_depends misbehavior. Just cut-n-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 happening 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 dependency'
print 'but the other one does not'
exec sp_depends SomeTable
exec sp_depends ParseDemo

go

print 'note that the non existent table causes an error'
print 'when passed to sp_depends'
exec sp_depends SomeOtherTable

go

print 'now create the other table and repeat the dependency check'
create table SomeOtherTable (id int)

go

print 'note that the dependency still does not show up'
print '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'
print 'and get it to see the dependency'
exec sp_recompile ParseDemo
exec ParseDemo

go

exec sp_depends SomeTable
exec sp_depends SomeOtherTable
exec sp_depends ParseDemo

go

print 'but if the procedure that is a phantom dependency'
print 'is dropped and recreated now that both tables exist'
print 'sp_depends shows the missing dependency'
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 attached to a table may not be considered when looking for dependencies for that table. This is discussed in the Microsoft Knowledge Base article Q180490.
• Dependencies in dynamic SQL (EXEC() , sp_execitesql or even osql via xp_cmdshell) are not cataloged in sysdepends. Any time a string is built and then dynamically executed in a stored procedure no dependencies to objects referenced in the string will be reported.
• Dependencies in OPENQUERY and OPENROWSET calls are not cataloged in sysdepends.
• Dependencies in other databases are not cataloged in sysdepends.
• Dependencies on other SQL Server instances are not cataloged in sysdepends.
• Dependencies in embedded SQL application client code or script file dependencies are not cataloged in sysdepends.
• Under some circumstances, 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. The dependency may not be properly cataloged in sysdepends.

You get the idea, right? sp_depends can lead you down the garden path only to feed you to the crocodiles. Finding the dependencies of an object will require an exhaustive search. You'll want to check the syscomments table in all databases on all SQL Server instances used by an application as well as the application source code. GREP, PERL, SourceSafe’s “Find in Files” option, or any good tool with similar functionality can be used for source files. In SourceSafe you could also look for dependencies external to SQL Server as well as application source code. Obviously, the limitation here is that if it didn't get checked in to SourceSafe you won’t find a dependency. Looking for a string match in the syscomments table seems to be most effective inside a SQL Server database.

Something like:

      select object_name(id) from

dbo.syscomments where text like '%SomeTable%'

You can do this using the sp_MSforeachdb stored procedure to cover all databases on a server. For example:

         exec sp_msforeachdb 'use ?

 select db_name() + ''.'' + object_name(id)

 from syscomments

 where text like ''%SomeTable%'''

If you have more than one server to check you could run this once on each server 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

 

-- all other servers will need a linked server defined and a hard coded entry here

if charindex('SERVER_A',@@servername) = 0

and charindex('SERVER_B',@@servername) = 0

and charindex('SERVER_C',@@servername) = 0

  begin

    raiserror('find_dependancies cannot be executed on this server',16,1)

    return -1

  end

else -- local server always has a linked server defined for itself with the same name as the server

  set @LinkedServer = @@servername

 

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

This will produce a list of all objects with the literal that you passed. You could even get fancier with the linked servers and make sure they are in master.dbo.sysservers, or available through sp_linkedservers, or even from a table. Lots of possibilities here, it all boils down to what makes the most sense for your situation. From any of the source file or syscomments parsing results you will still need to review each procedure, trigger, or reference listed to verify that it is a real dependency not simply a comment or coincidence. If you look for "SomeTable" the result could produce a match on "LonesomeTableBuzzard". Sorry, finding dependencies is a pretty manual process in the real world.

Within SQL Server you’ll still want to keep an eye out for rules and even permissions that might be affected by the change you are considering. 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.

Bill