STORED PROCEDURE TIPS

sp_server_info : Gives complete information about the current SQL Server that you are connected to.

sp_databases, sp_helpdb – Lists all the databases, with this two sp_helpdb provides more precise and clear information on the size, status created date etc..,

sp_attach_db, sp_detach_db –These stored procedures are usefull when you are porting a database from one server to another server. This is one of the best methods to change the database without any problems. This is just like copying a file from one place to another place. The Operation is to Detach from the Source and Attach to the Destination. After this you may need to use sp_change_users_login to update the logins and users link.

sp_helpuser – Lists all the users in the system.

sp_who, sp_who2 – Gives the list of logged in users and their complete details about the execution status.

sp_msforeachtable – This is one of the undocumented procedures, which you can used to find the Total Physical Space Occupied by each tables in the database.

Eg:sp_msforeachtable 'sp_spaceused "?"'

sp_password – Used to change password for an SQL Server Login.

sp_tables – Lists all the tables and virtual tables (Views) along with the system tables, with information’s including table owner.

sp_stored_procedures  - Lists all the stored procedures as like the table list with custom stored procedures in name; 1 format. (Please note it doesn’t mean that you can execute all the procedures that are listed, it depends on the permissions that you have)

sp_help tablename – Lists all the information pertaining to the given tablename with details about every column, constraints and the file table is located.

sp_helptext  - Used to view the contents of a view, stored procedure, user defined functions.

sp_changeobjectowner  - Changing Table / Stored Procedure Owner 
If you ever want to change the owner of the object to another user this procedure will be helpful.

sp_change_users_login – This is a very useful procedure when you are porting the database using sp_attach_db and where you need to keep your old logins and users as same.