Deleting All Data from a SQL Server Database: Listing 2

Use AdventureWorks

GO

-- Delete From [Sales].[StoreContact]
BEGIN
    DELETE FROM [Sales].[StoreContact]
    Print 'Deleted From [Sales].[StoreContact]'
END
GO

-- Delete From [Sales].[Store]
BEGIN
    DELETE FROM [Sales].[Store]
    Print 'Deleted From [Sales].[Store]'
END
GO

-- Delete From [Sales].[SalesTerritoryHistory]
BEGIN
    DELETE FROM [Sales].[SalesTerritoryHistory]
    Print 'Deleted From [Sales].[SalesTerritoryHistory]'
END
GO

-- Delete From [Sales].[SalesPersonQuotaHistory]
BEGIN
    DELETE FROM [Sales].[SalesPersonQuotaHistory]
    Print 'Deleted From [Sales].[SalesPersonQuotaHistory]'
END
GO

-- Delete From [Sales].[SalesOrderHeaderSalesReason]
BEGIN
    DELETE FROM [Sales].[SalesOrderHeaderSalesReason]
    Print 'Deleted From [Sales].[SalesOrderHeaderSalesReason]'
END
GO

-- Delete From [Sales].[SalesOrderDetail]
BEGIN
    DELETE FROM [Sales].[SalesOrderDetail]
    Print 'Deleted From [Sales].[SalesOrderDetail]'
    DBCC CHECKIDENT ('[Sales].[SalesOrderDetail]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Sales].[SalesOrderDetail]'
END
GO

-- Delete From [Sales].[SalesOrderHeader]
BEGIN
    DELETE FROM [Sales].[SalesOrderHeader]
    Print 'Deleted From [Sales].[SalesOrderHeader]'
    DBCC CHECKIDENT ('[Sales].[SalesOrderHeader]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Sales].[SalesOrderHeader]'
END
GO

-- Delete From [Sales].[SalesPerson]
BEGIN
    DELETE FROM [Sales].[SalesPerson]
    Print 'Deleted From [Sales].[SalesPerson]'
END
GO

-- Delete From [Purchasing].[PurchaseOrderDetail]
BEGIN
    DELETE FROM [Purchasing].[PurchaseOrderDetail]
    Print 'Deleted From [Purchasing].[PurchaseOrderDetail]'
    DBCC CHECKIDENT ('[Purchasing].[PurchaseOrderDetail]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Purchasing].[PurchaseOrderDetail]'
END
GO

-- Delete From [Purchasing].[PurchaseOrderHeader]
BEGIN
    DELETE FROM [Purchasing].[PurchaseOrderHeader]
    Print 'Deleted From [Purchasing].[PurchaseOrderHeader]'
    DBCC CHECKIDENT ('[Purchasing].[PurchaseOrderHeader]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Purchasing].[PurchaseOrderHeader]'
END
GO

-- Delete From [HumanResources].[JobCandidate]
BEGIN
    DELETE FROM [HumanResources].[JobCandidate]
    Print 'Deleted From [HumanResources].[JobCandidate]'
    DBCC CHECKIDENT ('[HumanResources].[JobCandidate]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [HumanResources].[JobCandidate]'
END
GO

-- Delete From [HumanResources].[EmployeePayHistory]
BEGIN
    DELETE FROM [HumanResources].[EmployeePayHistory]
    Print 'Deleted From [HumanResources].[EmployeePayHistory]'
END
GO

-- Delete From [HumanResources].[EmployeeDepartmentHistory]
BEGIN
    DELETE FROM [HumanResources].[EmployeeDepartmentHistory]
    Print 'Deleted From [HumanResources].[EmployeeDepartmentHistory]'
END
GO

-- Delete From [HumanResources].[EmployeeAddress]
BEGIN
    DELETE FROM [HumanResources].[EmployeeAddress]
    Print 'Deleted From [HumanResources].[EmployeeAddress]'
END
GO

-- Delete From [HumanResources].[Employee]
BEGIN
    DELETE FROM [HumanResources].[Employee]
    Print 'Deleted From [HumanResources].[Employee]'
    DBCC CHECKIDENT ('[HumanResources].[Employee]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [HumanResources].[Employee]'
END
GO

-- Delete From [Sales].[CustomerAddress]
BEGIN
    DELETE FROM [Sales].[CustomerAddress]
    Print 'Deleted From [Sales].[CustomerAddress]'
END
GO

-- Delete From [Purchasing].[VendorAddress]
BEGIN
    DELETE FROM [Purchasing].[VendorAddress]
    Print 'Deleted From [Purchasing].[VendorAddress]'
END
GO

-- Delete From [Person].[Address]
BEGIN
    DELETE FROM [Person].[Address]
    Print 'Deleted From [Person].[Address]'
    DBCC CHECKIDENT ('[Person].[Address]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Person].[Address]'
END
GO

-- Delete From [Sales].[SalesTaxRate]
BEGIN
    DELETE FROM [Sales].[SalesTaxRate]
    Print 'Deleted From [Sales].[SalesTaxRate]'
    DBCC CHECKIDENT ('[Sales].[SalesTaxRate]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Sales].[SalesTaxRate]'
END
GO

-- Delete From [Person].[StateProvince]
BEGIN
    DELETE FROM [Person].[StateProvince]
    Print 'Deleted From [Person].[StateProvince]'
    DBCC CHECKIDENT ('[Person].[StateProvince]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Person].[StateProvince]'
END
GO

-- Delete From [Production].[BillOfMaterials]
BEGIN
    DELETE FROM [Production].[BillOfMaterials]
    Print 'Deleted From [Production].[BillOfMaterials]'
    DBCC CHECKIDENT ('[Production].[BillOfMaterials]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Production].[BillOfMaterials]'
END
GO

-- Delete From [Sales].[SpecialOfferProduct]
BEGIN
    DELETE FROM [Sales].[SpecialOfferProduct]
    Print 'Deleted From [Sales].[SpecialOfferProduct]'
END
GO

-- Delete From [Sales].[ShoppingCartItem]
BEGIN
    DELETE FROM [Sales].[ShoppingCartItem]
    Print 'Deleted From [Sales].[ShoppingCartItem]'
    DBCC CHECKIDENT ('[Sales].[ShoppingCartItem]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Sales].[ShoppingCartItem]'
END
GO

-- Delete From [Purchasing].[ProductVendor]
BEGIN
    DELETE FROM [Purchasing].[ProductVendor]
    Print 'Deleted From [Purchasing].[ProductVendor]'
END
GO

-- Delete From [Production].[WorkOrderRouting]
BEGIN
    DELETE FROM [Production].[WorkOrderRouting]
    Print 'Deleted From [Production].[WorkOrderRouting]'
END
GO

-- Delete From [Production].[WorkOrder]
BEGIN
    DELETE FROM [Production].[WorkOrder]
    Print 'Deleted From [Production].[WorkOrder]'
    DBCC CHECKIDENT ('[Production].[WorkOrder]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Production].[WorkOrder]'
END
GO

-- Delete From [Production].[TransactionHistory]
BEGIN
    DELETE FROM [Production].[TransactionHistory]
    Print 'Deleted From [Production].[TransactionHistory]'
    DBCC CHECKIDENT ('[Production].[TransactionHistory]',RESEED,100000)
    Print 'Reset Identity Seed To 100000 for [Production].[TransactionHistory]'
END
GO

-- Delete From [Production].[ProductReview]
BEGIN
    DELETE FROM [Production].[ProductReview]
    Print 'Deleted From [Production].[ProductReview]'
    DBCC CHECKIDENT ('[Production].[ProductReview]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Production].[ProductReview]'
END
GO

-- Delete From [Production].[ProductProductPhoto]
BEGIN
    DELETE FROM [Production].[ProductProductPhoto]
    Print 'Deleted From [Production].[ProductProductPhoto]'
END
GO

-- Delete From [Production].[ProductListPriceHistory]
BEGIN
    DELETE FROM [Production].[ProductListPriceHistory]
    Print 'Deleted From [Production].[ProductListPriceHistory]'
END
GO

-- Delete From [Production].[ProductInventory]
BEGIN
    DELETE FROM [Production].[ProductInventory]
    Print 'Deleted From [Production].[ProductInventory]'
END
GO

-- Delete From [Production].[ProductDocument]
BEGIN
    DELETE FROM [Production].[ProductDocument]
    Print 'Deleted From [Production].[ProductDocument]'
END
GO

-- Delete From [Production].[ProductCostHistory]
BEGIN
    DELETE FROM [Production].[ProductCostHistory]
    Print 'Deleted From [Production].[ProductCostHistory]'
END
GO

-- Delete From [Production].[Product]
BEGIN
    DELETE FROM [Production].[Product]
    Print 'Deleted From [Production].[Product]'
    DBCC CHECKIDENT ('[Production].[Product]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Production].[Product]'
END
GO

-- Delete From [Production].[ProductModelIllustration]
BEGIN
    DELETE FROM [Production].[ProductModelIllustration]
    Print 'Deleted From [Production].[ProductModelIllustration]'
END
GO

-- Delete From [Production].[ProductModelProductDescriptionCulture]
BEGIN
    DELETE FROM [Production].[ProductModelProductDescriptionCulture]
    Print 'Deleted From [Production].[ProductModelProductDescriptionCulture]'
END
GO

-- Delete From [Production].[ProductSubcategory]
BEGIN
    DELETE FROM [Production].[ProductSubcategory]
    Print 'Deleted From [Production].[ProductSubcategory]'
    DBCC CHECKIDENT ('[Production].[ProductSubcategory]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Production].[ProductSubcategory]'
END
GO

-- Delete From [Purchasing].[VendorContact]
BEGIN
    DELETE FROM [Purchasing].[VendorContact]
    Print 'Deleted From [Purchasing].[VendorContact]'
END
GO

-- Delete From [Sales].[ContactCreditCard]
BEGIN
    DELETE FROM [Sales].[ContactCreditCard]
    Print 'Deleted From [Sales].[ContactCreditCard]'
END
GO

-- Delete From [Sales].[CountryRegionCurrency]
BEGIN
    DELETE FROM [Sales].[CountryRegionCurrency]
    Print 'Deleted From [Sales].[CountryRegionCurrency]'
END
GO

-- Delete From [Sales].[CurrencyRate]
BEGIN
    DELETE FROM [Sales].[CurrencyRate]
    Print 'Deleted From [Sales].[CurrencyRate]'
    DBCC CHECKIDENT ('[Sales].[CurrencyRate]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Sales].[CurrencyRate]'
END
GO

-- Delete From [Sales].[Individual]
BEGIN
    DELETE FROM [Sales].[Individual]
    Print 'Deleted From [Sales].[Individual]'
END
GO

-- Delete From [Sales].[Customer]
BEGIN
    DELETE FROM [Sales].[Customer]
    Print 'Deleted From [Sales].[Customer]'
    DBCC CHECKIDENT ('[Sales].[Customer]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Sales].[Customer]'
END
GO

-- Truncate table [dbo].[AWBuildVersion]
BEGIN
    TRUNCATE TABLE [dbo].[AWBuildVersion]
    Print 'Truncated table [dbo].[AWBuildVersion]'
END
GO

-- Truncate table [dbo].[DatabaseLog]
BEGIN
    TRUNCATE TABLE [dbo].[DatabaseLog]
    Print 'Truncated table [dbo].[DatabaseLog]'
END
GO

-- Delete From [HumanResources].[Department]
BEGIN
    DELETE FROM [HumanResources].[Department]
    Print 'Deleted From [HumanResources].[Department]'
    DBCC CHECKIDENT ('[HumanResources].[Department]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [HumanResources].[Department]'
END
GO

-- Delete From [HumanResources].[Shift]
BEGIN
    DELETE FROM [HumanResources].[Shift]
    Print 'Deleted From [HumanResources].[Shift]'
    DBCC CHECKIDENT ('[HumanResources].[Shift]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [HumanResources].[Shift]'
END
GO

-- Delete From [Person].[AddressType]
BEGIN
    DELETE FROM [Person].[AddressType]
    Print 'Deleted From [Person].[AddressType]'
    DBCC CHECKIDENT ('[Person].[AddressType]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Person].[AddressType]'
END
GO

-- Delete From [Person].[Contact]
BEGIN
    DELETE FROM [Person].[Contact]
    Print 'Deleted From [Person].[Contact]'
    DBCC CHECKIDENT ('[Person].[Contact]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Person].[Contact]'
END
GO

-- Delete From [Person].[ContactType]
BEGIN
    DELETE FROM [Person].[ContactType]
    Print 'Deleted From [Person].[ContactType]'
    DBCC CHECKIDENT ('[Person].[ContactType]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Person].[ContactType]'
END
GO

-- Delete From [Person].[CountryRegion]
BEGIN
    DELETE FROM [Person].[CountryRegion]
    Print 'Deleted From [Person].[CountryRegion]'
END
GO

-- Delete From [Production].[Culture]
BEGIN
    DELETE FROM [Production].[Culture]
    Print 'Deleted From [Production].[Culture]'
END
GO

-- Delete From [Production].[Document]
BEGIN
    DELETE FROM [Production].[Document]
    Print 'Deleted From [Production].[Document]'
    DBCC CHECKIDENT ('[Production].[Document]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Production].[Document]'
END
GO

-- Delete From [Production].[Illustration]
BEGIN
    DELETE FROM [Production].[Illustration]
    Print 'Deleted From [Production].[Illustration]'
    DBCC CHECKIDENT ('[Production].[Illustration]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Production].[Illustration]'
END
GO

-- Delete From [Production].[Location]
BEGIN
    DELETE FROM [Production].[Location]
    Print 'Deleted From [Production].[Location]'
    DBCC CHECKIDENT ('[Production].[Location]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Production].[Location]'
END
GO

-- Delete From [Production].[ProductCategory]
BEGIN
    DELETE FROM [Production].[ProductCategory]
    Print 'Deleted From [Production].[ProductCategory]'
    DBCC CHECKIDENT ('[Production].[ProductCategory]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Production].[ProductCategory]'
END
GO

-- Delete From [Production].[ProductDescription]
BEGIN
    DELETE FROM [Production].[ProductDescription]
    Print 'Deleted From [Production].[ProductDescription]'
    DBCC CHECKIDENT ('[Production].[ProductDescription]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Production].[ProductDescription]'
END
GO

-- Delete From [Production].[ProductModel]
BEGIN
    DELETE FROM [Production].[ProductModel]
    Print 'Deleted From [Production].[ProductModel]'
    DBCC CHECKIDENT ('[Production].[ProductModel]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Production].[ProductModel]'
END
GO

-- Delete From [Production].[ProductPhoto]
BEGIN
    DELETE FROM [Production].[ProductPhoto]
    Print 'Deleted From [Production].[ProductPhoto]'
    DBCC CHECKIDENT ('[Production].[ProductPhoto]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Production].[ProductPhoto]'
END
GO

-- Delete From [Production].[ScrapReason]
BEGIN
    DELETE FROM [Production].[ScrapReason]
    Print 'Deleted From [Production].[ScrapReason]'
    DBCC CHECKIDENT ('[Production].[ScrapReason]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Production].[ScrapReason]'
END
GO

-- Truncate table [Production].[TransactionHistoryArchive]
BEGIN
    TRUNCATE TABLE [Production].[TransactionHistoryArchive]
    Print 'Truncated table [Production].[TransactionHistoryArchive]'
END
GO

-- Delete From [Production].[UnitMeasure]
BEGIN
    DELETE FROM [Production].[UnitMeasure]
    Print 'Deleted From [Production].[UnitMeasure]'
END
GO

-- Delete From [Purchasing].[ShipMethod]
BEGIN
    DELETE FROM [Purchasing].[ShipMethod]
    Print 'Deleted From [Purchasing].[ShipMethod]'
    DBCC CHECKIDENT ('[Purchasing].[ShipMethod]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Purchasing].[ShipMethod]'
END
GO

-- Delete From [Purchasing].[Vendor]
BEGIN
    DELETE FROM [Purchasing].[Vendor]
    Print 'Deleted From [Purchasing].[Vendor]'
    DBCC CHECKIDENT ('[Purchasing].[Vendor]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Purchasing].[Vendor]'
END
GO

-- Delete From [Sales].[CreditCard]
BEGIN
    DELETE FROM [Sales].[CreditCard]
    Print 'Deleted From [Sales].[CreditCard]'
    DBCC CHECKIDENT ('[Sales].[CreditCard]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Sales].[CreditCard]'
END
GO

-- Delete From [Sales].[Currency]
BEGIN
    DELETE FROM [Sales].[Currency]
    Print 'Deleted From [Sales].[Currency]'
END
GO

-- Delete From [Sales].[SalesReason]
BEGIN
    DELETE FROM [Sales].[SalesReason]
    Print 'Deleted From [Sales].[SalesReason]'
    DBCC CHECKIDENT ('[Sales].[SalesReason]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Sales].[SalesReason]'
END
GO

-- Delete From [Sales].[SalesTerritory]
BEGIN
    DELETE FROM [Sales].[SalesTerritory]
    Print 'Deleted From [Sales].[SalesTerritory]'
    DBCC CHECKIDENT ('[Sales].[SalesTerritory]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Sales].[SalesTerritory]'
END
GO

-- Delete From [Sales].[SpecialOffer]
BEGIN
    DELETE FROM [Sales].[SpecialOffer]
    Print 'Deleted From [Sales].[SpecialOffer]'
    DBCC CHECKIDENT ('[Sales].[SpecialOffer]',RESEED,1)
    Print 'Reset Identity Seed To 1 for [Sales].[SpecialOffer]'
END
GO

About the Author

Joe Kunk is a Microsoft MVP in Visual Basic, three-time president of the Greater Lansing User Group for .NET, and developer for Dart Container Corporation of Mason, Michigan. He's been developing software for over 30 years and has worked in the education, government, financial and manufacturing industries. Kunk's co-authored the book "Professional DevExpress ASP.NET Controls" (Wrox Programmer to Programmer, 2009). He can be reached via email at [email protected].

comments powered by Disqus

Featured

  • New TypeScript 4.2 Tweaks Include Project Explainer

    Microsoft shipped TypeScript 4.2 -- the regular quarterly update to the open source programming language that improves JavaScript with static types -- with a host of tweaks including a way to explain why files are included in a project.

  • What's Top-Paying .NET Skill, In-Demand Language?

    New tech reports reveal the top-paying .NET skills and most in-demand programming languages in the Microsoft-centric developer landscape.

  • Microsoft's Distributed Application Runtime (Dapr) Debuts for Microservices

    Dapr is now production ready in version 1.0, easing microservices development on-premises, on the edge or in the cloud by abstracting away distributed computing hassles.

  • What's New in EF Core 6.0 Preview 1

    The Entity Framework Core 6 dev team shipped Preview 1 this week, headed toward a debut with the larger, unifying .NET 6 umbrella platform in November.

Upcoming Events