• How to Delete Rows from all tables in SQL Server Database

    Posted on August 23, 2012 by in Database, SQL Server

    You might run into a requirement where you need to delete all rows from all tables without writing script for each table. This might look trivial but there are things you might need to keep in mind such as if there are foreign key relationships between tables, you have to disable those constrains before deleting data from those tables.  After deleting data from a table that contains identity columns, we need to reseed them.  This can be done by looping through all tables and deleting rows from one-by-one.  But why sweat it, when we have Microsoft team behind our back. There is a system stored procedure sp_MSForEachTable which will get the job done for us.

    sp_MSForEachTable
    This is a undocumented system stored procedure in the master database. It’s like a cursor in that it loops through each table in the current database and executes a script that we define.

    Script:
    Running below script would do the following things …

    • disable constraints on all tables
    • delete data from all tables
    • Enable Constraints
    • Reseed identity columns
    --DISABLE CONSTRAINTS
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    --DELETE ALL ROWS
    EXEC sp_MSForEachTable 'DELETE FROM ?'
    --ENABLE CONSTRAINTS
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    --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:
    Now I would create some sample data to test above script
    Run following script to create a ProductCategory table and populate it with some sample data.

    --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
    

    Run following script to create a Product table and populate it with some sample data. This tables contains a foreign key relationship to ProductCategory table and also contains an identity column.

    -- DROP PRODUCT TABLE
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
    DROP TABLE [dbo].[Product]
    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)
    

    Data in those tables before running our delete script 
    Delete Rows from all tables 1

    Data in those tables after running our delete script
    Delete Rows from all tables 2

    Be Sociable, Share!

    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