Using sp_help

Sp_help is a system stored procedure that is similar to a ‘help’ command that is issued at a command prompt. This procedure is extremely useful in the database because almost any discreet object can be passed as a parameter in order to return back detailed information regarding the object. It should be noted that it will only return information for objects that are able to be referenced within the database you are currently in.

Being that there are so many different result sets returned for sp_help, we will only cover the most common usages here, starting with the obvious, executing sp_help with no parameters.

All Database Object Information

When executing sp_help with no parameters, all major objects within the database are enumerated (except triggers and indexes). Let’s execute the procedure and see the result set.
[cc lang=”sql”]
EXEC sp_help
[/cc]

From this, we see two different result sets appear. The first result set contains a list of all major objects. These include:

• views
• tables
• constraints
• triggers
• functions
• stored procedures
• service broker queues
• and probably some other objects I’m missing..

The second result set is a listing of the user defined data types that are in the system.

Returning table information

Probably my most common use of sp_help is to return back all information regarding a table. This includes columns, datatypes, and index information (including the index columns). In order to retrieve this information, we simply pass in the table name as a parameter.
[cc lang=”sql”]
EXEC sp_help ‘SalesLT.Address’
[/cc]
From this example we returned back 8 different result sets, none of which we will explore here because they are basically intuitive.

What I find most useful about the output is the column information and the columns used in the indexes. Trying to navigate these items using ssms is sometimes a pain.

Returning Routine Information

The last example we will cover here is also very useful. It returns back the parameters for routines. What’s so great about this you say? Well, if you want to know what is in some system created stored procedures, this will help.
[cc lang=”sql”]
EXEC sp_help ‘sp_who2’
[/cc]