Thursday, 8 September 2011


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.

No comments:

Post a Comment