sp_help Usage on Database Objects

Use sp_help to display information about a specified database object (that is, any object listed in sysobjects), a specified datatype (listed in systypes), or all objects and datatypes in the current database.

sp_help [objname] 

This is sp_help output for the publishers table:

Name               Owner        Object_type     Create_date 
----------------   -----------  -------------   ------------------------------
publishers         dbo          user table      Nov 9 2004 9:57AM
 
(1 row affected)
Column_name Type     Length   Prec  Scale   Nulls   Default_name   Rule_name
----------- -------  ------   ----- ------- ------- -------------- ---------- 
pub_id      char          4    NULL  NULL        0  NULL           pub_idrule
pub_name    varchar      40    NULL  NULL        1  NULL           NULL
city        varchar      20    NULL  NULL        1  NULL           NULL
state       char          2    NULL  NULL        1  NULL           NULL
Access_Rule_name    Computed_Column_object     Identity
------------------- -------------------------  ------------
NULL                NULL                                  0
NULL                NULL                                  0
NULL                NULL                                  0
NULL                NULL                                  0
 
Object has the following indexes
index_name index_keys  index_description  index_max_rows_per_page
---------- ----------  ---------- ------- -------------
pubind     pub_id      clustered, unique              0
          
index_fill_factor  index_reservepagegap   index_created      index_local
------------------ ---------------------- ----------------   ----------------
                0                      0  Nov 9 2004 9:58AM  Global Index
(1 row affected)
index_ptn_name   index_ptn_segment
---------------- --------------------
pubind_416001482  default

(1 row affected)
keytype  object      related_object  related_keys
-------  ----------  --------------  ------------
primary  publishers  -- none --      pub_id, *, *, *, *, *
foreign  titles      publishers      pub_id, *, *, *, *, *
 
(1 row affected)
name         type        partition_type     partitions     partition_keys
--------     --------    ------------------ -------------- ------------------
publishers   base table  roundrobin                    1   NULL

partition_name        partition_id  pages   segment     Create_date
------------------    ------------  ------  ----------- ----------------------
publishers_416001482  416001482         1   default     Nov 9 2004 9:58AM

Partition_Conditions
----------------------
NULL

Avg_pages   Max_pages   Min_pages   Ratio
 
(return status = 0)
No defined keys for this object.
name    type       partition_type   partitions   partition_keys
------- ---------- ---------------- ------------ ---------------
mytable base table roundrobin                  1 NULL

partiton_name       partition_id   pages     segment   create_date
-----------------   -------------- --------- --------- ------------------------
mytable_1136004047  1136004047            1  default     Nov 29 2004 1:30PM

partition_conditions
--------------------
NULL

Avg_pages   Max_pages   Min_pages   Ratio(Max/Avg)        Ration(Min/Avg)
----------- ----------- ----------- --------------------- -------------------
         1           1           1              1.000000                1.000000
Lock scheme Allpages
The attribute ’exp_row_size’ is not applicable to tables with 
allpages lock scheme.
The attribute ’concurrency_opt_threshold’ is not applicable to 
tables with allpages lock scheme.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap 
 ------------ -------------- ---------- ----------------- ------------ 
            1              0         0                 0            0
(1 row affected)
concurrency_opt_threshold   optimistic_index_lock   dealloc_first_txtpg
--------------------------- ----------------------- --------------------
                        0                         0                     0
(return status = 0)

If you execute sp_help without supplying an object name, the resulting report shows each object in sysobjects, along with its name, owner, and object type. Also shown is each user-defined datatype in systypes and its name, storage type, length, whether null values are allowed, and any defaults or rules bound to it. The report also notes if any primary or foreign-key columns have been defined for a table or view.

sp_help lists any indexes on a table, including those created by defining unique or primary key constraints. However, it does not include information about the integrity constraints defined for a table.