Finding Dependencies in SQL Server

by Greg M Lucas on 9 November 2010

I think that anyone who has used the system stored procedure sp_depends to find dependencies in SQL Server will have discovered  that the results cannot always be relied upon.  Most notably, this can occur when modules (procedures, views etc) have been created out of dependency order.  It seems that SQL Server only populates sys.depends values when the object depended upon already exists.  This makes perfect sense in a referential world, you can’t point to something unless it already exists.

I’m sure we all have our own standard alternatives, running queries against sys.comments or INFORMATION_SCHEMA.ROUTINES for example.  The more astute will wrap this query up into a stored procedure named something like sp_depends2 and dump it in the master database.  That works great if you don’t have hundreds of instances or you have the necessary access and grudging acceptance from the production DBA team (removes developer hat).  As a freelancer, I work in a number of different environments and it is simply not practical for me to install these helper sprocs in every environment I work in.

So, one solution I use is to make use of SQL Server Management Studio templates.  SSMS comes with a whole load of standard templates already and you can edit those or add your own.  You can view the complete list of templates in SSMS under “View… Template Explorer” or (Ctrl + Alt + T).  I normally add my own folder called “_Custom” so that it stands out at one end of a long list of folders and add my utility scripts here.  So then I just double-click the one I want and use (Ctrl + Shift + M) to specify the parameterised values.

This example will search for references to a given string in any module including triggers and some constraints and , optionally columns too.

--! This applies to the following:
--!
--! SQL stored procedure
--! SQL scalar function
--! SQL inline table-valued function
--! SQL table-valued function
--! SQL trigger (DML or DDL trigger at either the DB or server scope)
--! View
--! Check constraint
--! Default (constraint or stand-alone)
--! Rule
--! Replication filter procedure
--! Column Names (if @IncludeColumns = 1)
--!
USE <database_name,sysname,>;
DECLARE @IncludeColumns bit = <include_columns, bit, 0>;
SELECT
OBJECT_SCHEMA_NAME([object_id])     AS [Schema]
, [name]           AS [ObjectName]
, [type_desc]          AS [ObjectType]
, SUBSTRING(OBJECT_DEFINITION([object_id]), 1, 250) AS [DefinitionStart]
FROM sys.objects
WHERE OBJECT_SCHEMA_NAME([object_id]) = '<schema_name,sysname,dbo>'
AND  OBJECT_DEFINITION([object_id]) LIKE '%<object_name,sysname,>%'
UNION
SELECT
OBJECT_SCHEMA_NAME(o.[object_id]) AS [Schema]
, o.[name]
, o.[type_desc]
, ac.[name]       AS [ColumnName]
FROM sys.all_columns AS [ac]
JOIN sys.objects AS [o] ON ac.[object_id] = o.[object_id]
WHERE OBJECT_SCHEMA_NAME(ac.[object_id]) = '<schema_name,sysname,dbo>'
AND ac.[name] LIKE '%<object_name,sysname,>%'
AND @IncludeColumns = 1
ORDER BY [Schema], [ObjectName]

 I find that having utility scripts like this on my local machine as SSMS templates makes them quickly accessible and easy to use against any environment.

Comments on this entry are closed.

Previous post:

Next post: