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

  • Microsoft Details Native Integration of Elastic on Azure

    Microsoft detailed the native integration of Elastic tech with its Azure cloud computing platform, increasing application observability.

  • Java on Visual Studio Code Going Cloud Native

    Cloud-native development figures prominently in a new roadmap published by Microsoft's Java on Visual Studio Code dev team.

  • Speed Lines Graphic

    Quantum-Inspired Annealing Using C# or Python

    Dr. James McCaffrey of Microsoft Research explains a new idea that slightly modifies standard simulated annealing by borrowing ideas from quantum mechanics.

  • Visual Studio 2022 v17.1 Preview 3 Improves Web Tools

    Microsoft quietly shipped Visual Studio 2022 v17.1 Preview 3 with enhancements to web tools.

Upcoming Events