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]
Here is the 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 describe any information about the integrity constraints defined for a table. Use sp_helpconstraint to display information about any integrity constraints.
