In this Blog we learn about Some Important System Store Procedure SQL SERVER.This procedure help us to solve various problem in easy way.System store procedure are useful in administrative and informational activities in Microsoft. Some important system store procedure is given bellow.
1. sp_MSforeachtable:
This procedure run for all table in selected database. sp_MSforeachtable is used in various situation.
a. Display the size of all table in selected database to achieve this goal we run the SQL command,
- EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
b. To RESEED all table – with the help of this procedure we reseed all table (Auto Incremented id in table),
- EXEC sp_MSForEachTable '
- IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
- DBCC CHECKIDENT (''?'', RESEED, 0)
- '
- GO
c. Delete All Constraint of All Table – with the help of sp_MSforeachtable we can delete all constraint of all table. We run following code for delete all constraint for all table is,
- EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
We can also perform update, delete all table and disable all Trigger with help of sp_MSforeachtable.
2. sp_MSforeachdb: This Procedure run on all database of Sql Server.
- EXEC sp_MSforeachdb @command
- EXEC sp_MSforeachdb '
- BEGIN
- SELECT name,physical_name,state,size
- FROM ?.sys.database_files
- END'
3.
sp_helpdb:
This procedure help us to know about database. When we execute sp_helpdb then it give information about all database but you want to know about specific database then give database name.
sp_helpdb [Database Name] 4.
sp_rename: this procedure help in rename of user created object like table, column or data type.
- EXEC sp_rename 'customers', 'customer'
- For rename column - EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'
5. xp_startmail - when:
We need to start mail from SQL Server Database then use xp_startmail procedure. For start mail we need to provide profile name and password,
exec xp_startmail [‘Mail Profile Name’], [‘Password’]
When execute or run this procedure it return this message - SQL mail session started.