SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

More than sp_help

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.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, system functions, T-SQL


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...