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

  • Creating Reactive Applications in .NET

    In modern applications, data is being retrieved in asynchronous, real-time streams, as traditional pull requests where the clients asks for data from the server are becoming a thing of the past.

  • AI for GitHub Collaboration? Maybe Not So Much

    No doubt GitHub Copilot has been a boon for developers, but AI might not be the best tool for collaboration, according to developers weighing in on a recent social media post from the GitHub team.

  • Visual Studio 2022 Getting VS Code 'Command Palette' Equivalent

    As any Visual Studio Code user knows, the editor's command palette is a powerful tool for getting things done quickly, without having to navigate through menus and dialogs. Now, we learn how an equivalent is coming for Microsoft's flagship Visual Studio IDE, invoked by the same familiar Ctrl+Shift+P keyboard shortcut.

  • .NET 9 Preview 3: 'I've Been Waiting 9 Years for This API!'

    Microsoft's third preview of .NET 9 sees a lot of minor tweaks and fixes with no earth-shaking new functionality, but little things can be important to individual developers.

  • Data Anomaly Detection Using a Neural Autoencoder with C#

    Dr. James McCaffrey of Microsoft Research tackles the process of examining a set of source data to find data items that are different in some way from the majority of the source items.

Most   Popular

Subscribe on YouTube