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.