How can SQL procedures from the Master database help db developers?

Question

There are a lot of procedures and functions in the master database of SQL Server that provide handy tools for the database developer. For instance, sp_help [database object] will provide several results sets with useful attributes of the specified object; sp_helptext [procedure or function name] will return the body of a procedure or function in your results pane, (as opposed to you looking through your object explorer and opening the object with "Script as..." just to see its code contents.) Other really handy procedures include sp_depends and sp_msdependencies, the first of which lists all objects that will cease to work if you drop a specified object, and the latter of which lists all objects that must not be dropped if a specified object is to continue to work properly.

I learned about all of these today from a colleague, and I was shocked that I did not know about these sooner.

Does anyone out there know of any other handy little functions or procedures that will help a developer sort through the puzzle of an unfamiliar (and usually buggy) database?

Answer 1

They are all on the MS Site: System Stored Procedures (Transact-SQL)

Answer 2

two that come to my mind

Locks: sp_lock2

Orphans: sp_change_users_login

Answer 3

sp_who2 is useful - especially if you dump the results to a temp table and then pull out only the blocking information.

Answer 4

have a look at this post: Hidden Features of SQL Server