If you need to drop a stored procedure (or other object) in SQL Server, it’s often useful to check whether it’s used by other stored procedures etc. That is, what other objects have a dependency on the stored procedure you want to drop.

As you probably know, this can easily be achieved by right clicking the stored procedure in object explorer and selecting the Dependencies option. The problem with this approach is that previously SQL Server hasn’t been too good at keeping track of the dependencies (for example, if you’ve dropped and recreated a stored procedure, the dependencies get lost). This is improved in SQL Server 2008, but for those of us using SQL Server 2005, here’s a simple script that will force an update of dependencies on all the user stored procedures in the database:

DECLARE @sql nvarchar(max)
SELECT @SQL = IsNull(@SQL, '') + 'exec sp_refreshsqlmodule ' + name + char(13)
FROM sysObjects
WHERE Type = 'P'
AND Category = 0
ORDER BY name
-- Uncomment this line to see the resulting SQL
-- print @sql
EXEC sp_ExecuteSQL @sql

You’ll need at least service pack 2, because the sp_refreshsqlmodule was introduced then.