T-SQL Admin 101: Helpful stored procedures

Helpful stored procs:

sp_configure

Displays (or changes) server config settings

sp_configure 'show advanced option', '1';
go
reconfigure
go
sp_configure
go

Will show advanced options, not just the basic ones

sp_createstats

Creates statistics that are used by Query Optimizer for all tables in a database

sp_help

Displays summary information of objects of all types that exist in the current database such as tables, views, functions, and stored procedures

sp_help <object_name>

Displays information about the object_name

sp_helpdb

Returns a list of database names (and information about each)

sp_helpdb <db_name>

Returns additional information about the db_name such as the physical filenames.

sp_helpfile

Returns physical information about the current database such as the logical names, physical file names, and other information.

sp_lock

Displays current locking information for the entire SQL instance

sp_spaceused

Returns used/unused disk space of the current database

sp_spaceused <db_objectname>

Returns space utilization of db_objectname (like a table and its index size).
Also tells the number of rows.

sp_who

Returns a list of current processes that are connected to the SQL instance

sp_who2

Same as above, but more information about each process

(Partially lifted from SQL 2008R2 Unleashed)