• sp_MSforeachtable Stored Procedure

    Posted on September 13, 2012 by in Database, SQL Server

    sp_MSforeachtable allows you to easily process statements against every table in a single database.
    This could also be done via cursor, but why sweat when we have this stored procedure which has many provisions and options.

    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

    Example 1: Retrieve list of tables that starts with “P” and their rows counts 

    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
    

    Output:
    sp_MSforeachtabl 1

    Example 2:  DISABLE CONTRAINTS ON ALL TABLES

    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

    Example 3:  ENABLE CONTRAINTS ON ALL TABLES

    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

    Example 4:  DELETE ROWS FROM ALL TABLES

    EXEC sp_MSForEachTable 'DELETE FROM ?'

    Example 5:  RESEED IDENTITY COLUMNS.
    IF ANY TABLE DOES NOT CONTAIN IDENTITY COLUMNS IT WILL THROUGH AN ERROR AND MOVES ON

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

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

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

    Example 7:   DISABLE TRIGGERS ON ALL TABLES

    EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL '

    SAMPLE DATA:
    Now I would create some sample data to test above script

    Run following script to create ProductCategory and product tables and populate it with some sample data.

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
    DROP TABLE [dbo].[Product]
    GO
    
    --DROP THE TABLE IF IT EXISTS
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductCategory]') AND type in (N'U'))
    DROP TABLE [dbo].[ProductCategory]
    GO
    
    --CREATE THE TABLE
    CREATE TABLE [dbo].[ProductCategory](
    	[ProductCategoryID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](255) NOT NULL,
     CONSTRAINT [PK_ProductCategory_ProductCategoryID] PRIMARY KEY CLUSTERED
    (
    	[ProductCategoryID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    --INSERT SOME SAMPLE DATA
    INSERT INTO [ProductCategory] Values ('Bikes')
    INSERT INTO [ProductCategory] Values ('Components')
    INSERT INTO [ProductCategory] Values ('Clothing')
    INSERT INTO [ProductCategory] Values ('Accessories')
    GO
    
    --CREATE PRODUCT TABLE
    CREATE TABLE [dbo].[Product](
    	[ProductID] [int] IDENTITY(1,1) NOT NULL,
    	[ProductCategoryID] [int] NOT NULL,
    	[Name] [varchar](255) NOT NULL,
    	[ProductNumber] [varchar](255) NOT NULL,
    	[ListPrice] [numeric](9, 2) NOT NULL,
     CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED
    (
    	[ProductID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    --ADD FOREIGN KEY CONSTRAINT
    ALTER TABLE [Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_ProductCategoryID] FOREIGN KEY([ProductCategoryID])
    REFERENCES [ProductCategory] ([ProductCategoryID])
    GO
    
    --INSERT SAMPLE DATA INTO PRODUCT TABLE
    INSERT INTO [Product] VALUES(1, 'Adjustable Race',	'AR-5381', 400)
    INSERT INTO [Product] VALUES(2, 'Bearing Ball',	'BA-8327', 600)
    INSERT INTO [Product] VALUES(1, 'BB Ball Bearing',	'BE-2349', 500)
    
    Be Sociable, Share!
      Post Tagged with ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    Leave a Reply