sp_MSforeachtable Stored Procedure

sp_MSforeachtable stored procedure provided by Microsoft, is used for smooth processing on all tables of a single database. So why to use complex query when we have already that procedure.

How and when to use sp_MSFooreachtable :

Parameters
sp_MSforeachtable @command1, @replacechar, @command2,  @command3, @whereand, @precommand, @postcommand

  • @command1 nvarchar(2000) – is the first command to be executed
  • @replacechar nchar(1) – is a character in the command string that will be replaced with the table name being processed (default character is “?”)
  • @command2 nvarchar(2000)-  additional command that can be run for each table. It runs after @ command1
  • @command2 nvarchar(2000)-  another additional command that can be run for each table. It runs after @ comman2
  • @whereand nvarchar(2000)- you can specify the where clause in this parameter
  • @precommand nvarchar(2000) -  specifies a command to be run prior to processing any table
  • @postcommand nvarchar(2000)- command to be executed  after all commands have been processed
Eg 1 : Retriving all tables starting with 'c' and their row count

CREATE TABLE  #rowcount (tablename varchar(128), cnt int)
EXEC sp_MSforeachtable
 @command1 = 'Insert into #rowcount select ''?'',
              count(*) from ?',
 @whereand = 'and o.name like ''p%''',
 @postcommand = 'SELECT * from #rowcount'

DROP TABLE  #rowcount

Eg 2:  Disabling all constraints on all tables
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
Eg 3:  Enabling all constraints on all tables

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Eg 4:  Deleting rows from all tables

EXEC sp_MSForEachTable 'DELETE FROM ?'
Eg 5:  Reseed identity column

If any table doesn't contain identity column, it raise error and as a quality of stored procedure it will move on


EXEC sp_MSforeachtable 'DBCC CHECKIDENT ( ''?'', RESEED, 0)'


Eg 6:   Rebuild clustered index/all non clusted indexes on all the tables


EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)'

No comments:

Post a Comment