System Stored Procedures in SQl

System Stored Procedures in SQl

Below are the list of  some of the Important system Stored Procedures which we used most while working with SQl

SP_Tables--Shows list of all Tables in a database

sp_spaceused--if you will run this procedure without any parameter it will show the space used by DB

Output
name rows reserved data index_size unused
TESTING 7                   72 KB 8 KB 8 KB 56 KB

SP_SPaceused 'Test'--Shows the Space used by Table

--Rename db first parameter old db name second parameter new db Name
SP_Renamedb

--Rename Table Name first parameter old Name second old table Name
SP_Rename 'Testing','Testing4'


--Rename column Name first parameter old column Name along with table name second old column Name
 sp_rename 'Departments.id','departmentid' ,'column'

 You can use SP_rename procedure to rename Index,constraints datatype and statics also

 --sp wHO AND SP_WHO2 checks what user is doing what on server
 sp_who
 sp_who2

--Shows all index in table
sp_helpindex 'Departments'


--List of locks held by Database
sp_lock;

 --list of all columns of a table
 SP_columns 'Departments'
  
  --list of all constraints of a table
sp_helpconstraint 'Departments'

Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure,function, trigger, computed column, CHECK constraint, view, or system stored procedure.
sp_helptext '[dbo].[Proc_BasicAnnualSalary]'

Note :Above are the list of System SP which we used a lot in order to see all system stored procedure check msdn link:
http://msdn.microsoft.com/en-us/library/ms187961.aspx