Some Very Useful System Stored procedures

sp_help to get a definition of table, store procedures or views.

sp_spaceused, You can use it to determine the size of a table or the entire database. If you pass the table name, it returns the space used for that table, when called with no argument it gives the space of the database

sp_who/sp_who2 - lets you know who is doing what on the server.

Select * From sysobjects where xtype='U' order by Name : Gives a list of all user-defined tables in a database.

sp_rename: for renaming database objects (tables, columns, indexes, etc.)

 

sp_ helpindex [table] - shows you index info (same info as sp_help)

sp_helpconstraint [table] - shows you primary/foreign key/defaults and other constraints.

sp_depends [obj] - shows dependencies of an object.

sp_depends [table] - shows you what stored procs, views, triggers, UDF affect this table.

sp_depends [sproc] - shows what tables etc are affected/used by this stored proc.

Hope these will help you.. i will be posting  more later.