Deleting All Data from a SQL Server Database: Listing 2 -- Visual Studio Magazine

Deleting All Data from a SQL Server Database: Listing 2

Use AdventureWorks

By Joe Kunk
04/27/2011

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

Printable Format

comments powered by Disqus

Featured

Stack Overflow Dev Survey: VS Code, Visual Studio and .NET Shine

In this year's big Stack Overflow developer survey things are much the same for Microsoft-centric data points: VS Code and Visual Studio still rule the IDE roost, while .NET maintains its No. 1 position among non-web frameworks.
Cloud-Native .NET Aspire 8.1 Targets Building Containers, Orchestrating Python

Microsoft's first significant update to .NET Aspire for cloud-native app development addresses the building of container images and the orchestration of Python code among a bevy of new features and enhancements.
Uno Platform 5.3 Enhances Hot Reload UI

Uno Platform 5.3 shipped with enhanced Hot Reload UI functionality, full support for the Rider IDE from JetBrains and more.
Stop Using Entity Framework as Just a DTO Provider

Chris Klug keeps seeing developers using Entity Framework, Microsoft's open-source object-relational mapper (ORM) for .NET applications, in less optimal ways.
Visual Studio Uses RUBICON to Improve AI Conversations

AI assistants have improved coding productivity, but how do you know if they're getting better?