More than sp_help

4

August 14, 2014 by Kenneth Fisher

If you have worked with SQL Server for very long you have probably run across the extremely useful system function called sp_help. This handy little function will return a list of the objects in the database if you don’t pass in a parameter. If you do pass in a parameter (and it’s a valid object name) then it returns different types of detailed information about the object named in the parameter depending on the type of object.

If for example you run:

sp_help sp_help -- parameter name can be in quotes or not if 
         -- it's a single part name

You get the date the SP was created and a list of any parameters with their names, data_types, and schemas.

sp_help_1

If on the other hand you run:

sp_help [sys.objects] -- If the object name is a two part name 
         -- then it must be quoted or put in []s

Now you get the create date, the list of columns and their schema, the identity column if there is one, the RowGuidCol if there is one, and lists of indexes, constraints and foreign keys if they exist.

sp_help_2

Again, if you have worked with SQL Server for a while you probably know most if not all of this. What you may not know is that there are a number of sp_help functions. sp_helpindex for example returns the list of indexes from a table or view. Likewise sp_helpconstraint and any CHECK or DEFAULT constraints, sp_helptrigger and any triggers and sp_helptext and the definition of any code based object. At a higher level you see sp_helpdb, sp_helpfile and sp_helpfilegroups that display information on databases, files and filegroups respectively. It’s well worth taking at least a brief look at the list of sp_help functions because while all of this information and more is available in the system views and DMOs, sometimes a sp_help function has just the information you need and can be much quicker than writing a query.

4 thoughts on “More than sp_help

  1. […] R2, 2012, 2014 by Aaron Bertrand Limiting error log file size in SQL Server 2012 by Paul Randal More than sp_help by Kenneth Fisher Can it -whatif by Richard Siddaway Select-Object or Where-Object by Richard Siddaway Look at me! by […]

  2. Jeffrey Cox says:

    Just wanted to say ‘thanks’ for the post. Stumbled across this today looking for information on if sp_help could help me find info re indexes and constraints. Thanks!

  3. […] a big fan of the sp_help system stored procedures, which is why I’ve been a bit disappointed that the sp_helpindex stored procedure is rather […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: