Microsoft® SQL Server 2012 Unleashed

System Stored Procedures

System stored procedures have been a favorite of SQL Server DBAs since the inception of SQL Server. They provide a rich set of information that covers many different aspects of SQL Server. They can return some of the same types of information as system views, but they return a fixed set of information that you cannot modify as you can when using a SELECT statement against the system views. That is not to say that they are not valuable; they are valuable, and they are particularly useful for people who have been using SQL Server for a long time. System stored procedures such as sp_who2, sp_lock, and sp_help are tools for a database professional that are as basic as a hammer is to a carpenter.

System stored procedures ...