More than sp_help6
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.
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.
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.
Category: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL | Tags: code language, language sql, microsoft sql server, sql statements, system functions, T-SQL
6 thoughts on “More than sp_help”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] 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 […]
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!
Awesome! Glad to hear it! Yea there are some great sp_help* functions. I was just using sp_helpindex today 🙂
[…] 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 […]
[…] You’ll notice that they each provide slightly different information, and of course the system view provides more than the sp_help function. […]
[…] sp_help functions. […]