[Tip] getting local context from sp_ in master

In SQL 2005, if you create an SP in master that queries from system tables (or the INFORMATION_SCHEMA views), it'll return the details from the Master database - regardless of where you're running it. In SQL 2000, it worked.

So, an unsupported workaround, pointed out to me by Erland Sommarskog, SQL MVP & SQL God:

EXEC sp_MS_marksystemobject [your_sp_name]

[Cruft] New DMV in 2008 - SP details

One of the things I've been hoping/waiting/praying for, in SQL 2008, is the ability to see when an SP was last run. In 2000 and 2005 you can do it, so long as it's still in the cache. Which means that things only run sporadically won't occur.

Imagine my happiness:

SELECT * FROM sys.dm_exec_procedure_stats

Extra credit: join to sys.procedures (also new) to get the object.

So, now you can see, since the server was started, what runs. And since your server stays up for several months at a time, you have a good "hit list" of procs that can be scripted out and removed.

I'll update this post when we get a good working version.
(Thanks to Hammer for his help)

[Cruft] Fixing dependencies

EXEC sys.sp_refreshsqlmodule 'dbo.MyProcFnOrView'

That will update your dependencies table, allowing you to use sp_depends even if the objects weren't inserted in order. New to 2005, thankfully someone at MS realized and fixed sp_depends' weakness.

When objects are added, rows are added to the internal dependency table. However, it's very easy to get out of sync - say if you add SP A that calls SP B, but add them in the reverse order. Which _never_ happens. Sure.

So, anyhow. Use that, then use one of the handy pieces of code online that assume that the dependency table always works.


UPDATE 2012:
or, you could always use the versions that 2008+ have (don't bother on 2005; not there):

sys.dm_sql_referenced_entities
sys.dm_sql_referencing_entities


sys.sql_expression_dependencies