Quickest Way To Delete All Products & Categories?

Anyone got a quick way to delete all products and categories from a store?  I need to create a new store based off a current one but don't need any of the products or categories.

Make "Deleted" flag as true in database from sqlquery.
Here is sqlquery::

UPDATE Nop_Category SET Deleted=1
UPDATE Nop_Product SET Deleted=1

Thanks what about orders?  And customers do these all have a deleted flag?

Yes, the switch is available for customers and orders as well.

E. Adams

Here's what I use to reset most things. The second batch of commands resets the seed for the identity column. This doesn't affect customer data but you can follow the pattern for other tables if you wish. Alternatively you could drop and recreate the tables but then you have to make sure you reestablish foreign keys and the likes.

Worth mentioning that this probably isn't best for the customer table, you don't want to inadvertently delete the admin account! Modify the delete statement appropriately to exclude the admin account(s) you wish to preserve, and set the new seed value appropriately based on the highest identity value for any admin accounts you are preserving.

DELETE FROM Nop_ShoppingCartItem
DELETE FROM Nop_OrderNote
DELETE FROM Nop_OrderProductVariant
DELETE FROM Nop_Order
DELETE FROM Nop_ProductVariantAttributeCombination
DELETE FROM Nop_RelatedProduct
DELETE FROM Nop_ProductVariantAttributeValue
DELETE FROM Nop_ProductVariant_ProductAttribute_Mapping
DELETE FROM Nop_ProductAttribute
DELETE FROM Nop_ProductVariant
DELETE FROM Nop_ProductPicture
DELETE FROM Nop_Product_Category_Mapping
DELETE FROM Nop_Product
DELETE FROM Nop_Category
DELETE FROM Nop_Picture

DBCC CHECKIDENT (Nop_ShoppingCartItem, RESEED, 0)
DBCC CHECKIDENT (Nop_OrderNote, RESEED, 0)
DBCC CHECKIDENT (Nop_OrderProductVariant, RESEED, 0)
DBCC CHECKIDENT (Nop_Order, RESEED, 0)
DBCC CHECKIDENT (Nop_ProductVariantAttributeCombination, RESEED, 0)
DBCC CHECKIDENT (Nop_RelatedProduct, RESEED, 0)
DBCC CHECKIDENT (Nop_ProductVariantAttributeValue, RESEED, 0)
DBCC CHECKIDENT (Nop_ProductVariant_ProductAttribute_Mapping, RESEED, 0)
DBCC CHECKIDENT (Nop_ProductAttribute, RESEED, 0)
DBCC CHECKIDENT (Nop_ProductVariant, RESEED, 0)
DBCC CHECKIDENT (Nop_ProductPicture, RESEED, 0)
DBCC CHECKIDENT (Nop_Product_Category_Mapping, RESEED, 0)
DBCC CHECKIDENT (Nop_Product, RESEED, 0)
DBCC CHECKIDENT (Nop_Category, RESEED, 0)
DBCC CHECKIDENT (Nop_Picture, RESEED, 0)

Tzael wrote:

I just used your example and also included some TSQL to rebuild the indexes on all the effected tables:

DBCC DBREINDEX (Nop_ShoppingCartItem, " ")
DBCC DBREINDEX (Nop_ProductVariantAttributeCombination, " ")
DBCC DBREINDEX (Nop_ProductVariantAttributeValue, " ")
DBCC DBREINDEX (Nop_ProductVariant_ProductAttribute_Mapping, " ")
DBCC DBREINDEX (Nop_ProductAttribute, " ")
DBCC DBREINDEX (Nop_ProductVariant, " ")
DBCC DBREINDEX (Nop_Product, " ")
DBCC DBREINDEX (Nop_ProductPicture, " ")
DBCC DBREINDEX (Nop_Product, " ")
DBCC DBREINDEX (Nop_Category, " ")
DBCC DBREINDEX (Nop_Picture, " ")
DBCC DBREINDEX (Nop_Manufacturer, " ")

But SQL is now complaining about a FOREIGN KEY constraint when adding products! See below, any help greatly apprcieated!

ERROR: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Nop_ProductVariant_ProductAttribute_Mapping_Nop_ProductAttribute". The conflict occurred in database "surfari-nop18", table "dbo.Nop_ProductAttribute", column 'ProductAttributeID'.
The statement has been terminated.

Sorry ignore the error, was something obvious and specific to my code. If you are doing these mass deletes it is also worth running the following two commands:

Exec sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")'

- ReIndexes all tables.

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

- Truncates all tables - shrinks you DB and permenantly delets your deleted data

JaxUK,

I like this approach. I am upgrading to 1.9 for a new site and took my 1.7 DB and copied it over. I want to delete the orders, cart, etc from it. But one statement in your post worries me EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'. Won't this truncate all the tables in the database?

We already have a Delete Selected button in Product/List

So I just make a new button Delete All

<input type="button" id="delete-all" class="t-button" value="@T("Admin.Common.Delete.All")" />

Add js for him:

//"Delete (all)" button
            $('#delete-all').click(function (e) {
                e.preventDefault();
                //redirect to required URL
                setLocation('@(Url.Action("DeleteSelected", "Product"))');
                return false;
            });

And change existing DeleteSelected method in ProductController.cs:

public ActionResult DeleteSelected(string selectedIds)
        {
            if (!_permissionService.Authorize(StandardPermissionProvider.ManageCatalog))
                return AccessDeniedView();

            var products = new List<Product>();
            if (selectedIds != null)
            {
                var ids = selectedIds
                    .Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
                    .Select(x => Convert.ToInt32(x))
                    .ToArray();
                products.AddRange(_productService.GetProductsByIds(ids));
            }
            else
            {
                products.AddRange(_productService.GetAllProducts(true));
            }

            for (int i = 0; i < products.Count; i++)
            {
                var product = products[i];
                _productService.DeleteProduct(product);
                //update product tag totals
                UpdateProductTagTotals(product);
            }

            return RedirectToAction("List");
        }

For me this is best way to delete all products. I just use existing functionality of the system to do all work.

For those of you that found this helpful, you may have realized that this schema is no longer valid...

Here is an updated script...It is compatible with nop v2.65 as that is the version we are currently running.  
We will be upgrading to 3.10 soon and I will post an updated script if I have the chance.

Feel free to comment if I missed anything.

DELETE FROM ShoppingCartItem
DELETE FROM OrderNote
DELETE FROM OrderProductVariant
DELETE FROM RecurringPayment
DELETE FROM RewardPointsHistory
DELETE FROM [Order]
DELETE FROM ProductVariantAttributeCombination
DELETE FROM RelatedProduct
DELETE FROM ProductVariantAttributeValue
DELETE FROM ProductVariant_ProductAttribute_Mapping
DELETE FROM ProductAttribute
DELETE FROM ProductVariant
DELETE FROM Picture
DELETE FROM Product_Category_Mapping
DELETE FROM Product
DELETE FROM Category
DELETE FROM Picture

DBCC CHECKIDENT (ShoppingCartItem, RESEED, 0)
DBCC CHECKIDENT (OrderNote, RESEED, 0)
DBCC CHECKIDENT (OrderProductVariant, RESEED, 0)
DBCC CHECKIDENT (RecurringPayment, RESEED, 0)
DBCC CHECKIDENT (RewardPointsHistory, RESEED, 0)
DBCC CHECKIDENT ([Order], RESEED, 0)
DBCC CHECKIDENT (ProductVariantAttributeCombination, RESEED, 0)
DBCC CHECKIDENT (RelatedProduct, RESEED, 0)
DBCC CHECKIDENT (ProductVariantAttributeValue, RESEED, 0)
DBCC CHECKIDENT (ProductVariant_ProductAttribute_Mapping, RESEED, 0)
DBCC CHECKIDENT (ProductAttribute, RESEED, 0)
DBCC CHECKIDENT (ProductVariant, RESEED, 0)
DBCC CHECKIDENT (Picture, RESEED, 0)
DBCC CHECKIDENT (Product_Category_Mapping, RESEED, 0)
DBCC CHECKIDENT (Product, RESEED, 0)
DBCC CHECKIDENT (Category, RESEED, 0)
DBCC CHECKIDENT (Picture, RESEED, 0)