Quickest Way To Delete All Products & Categories?

Is any One have Updated script to delete sample database for nop commerce version 3.60. like you have in above post.

Use this works with 3.5.


DELETE FROM ShoppingCartItem
DELETE FROM OrderNote
DELETE FROM RecurringPayment
DELETE FROM RewardPointsHistory
DELETE FROM [Order]
DELETE FROM ProductAttributeCombination
DELETE FROM RelatedProduct
DELETE FROM ProductAttribute
DELETE FROM Picture
DELETE FROM Product_Category_Mapping
DELETE FROM Product

DELETE FROM ProductAttributeCombination
DELETE FROM Product_Picture_Mapping
DELETE FROM Product_ProductAttribute_Mapping
DELETE FROM ProductAttributeValue
DELETE FROM Product_ProductTag_Mapping
DELETE FROM Product_SpecificationAttribute_Mapping
DELETE FROM ProductAttributeCombination
DELETE FROM ProductReview
DELETE FROM ProductReviewHelpfulness
Delete UrlRecord Where EntityName = 'Product'



DBCC CHECKIDENT (ShoppingCartItem, RESEED, 0)
DBCC CHECKIDENT (OrderNote, 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 (ProductAttribute, RESEED, 0)

DBCC CHECKIDENT (Picture, RESEED, 0)
DBCC CHECKIDENT (Product_Category_Mapping, RESEED, 0)
DBCC CHECKIDENT (Product, RESEED, 0)

DBCC CHECKIDENT ( ProductVariantAttributeCombination, RESEED, 0)
DBCC CHECKIDENT ( Product_Picture_Mapping, RESEED, 0)
DBCC CHECKIDENT ( Product_ProductAttribute_Mapping, RESEED, 0)
DBCC CHECKIDENT (ProductVariantAttributeValue, RESEED, 0)

DBCC CHECKIDENT (Product_SpecificationAttribute_Mapping, RESEED, 0)
DBCC CHECKIDENT (ProductVariantAttributeCombination, RESEED, 0)
DBCC CHECKIDENT ( ProductReview, RESEED, 0)
DBCC CHECKIDENT (ProductReviewHelpfulness, RESEED,
0)


Let me know if you have any question.

3.7
Deletes from tables, reseeds the identity, and rebuilds the indexes.

DELETE FROM ShoppingCartItem
DELETE FROM OrderNote
DELETE FROM RecurringPayment
DELETE FROM RewardPointsHistory
DELETE FROM [Order]
DELETE FROM ProductAttributeCombination
DELETE FROM RelatedProduct
DELETE FROM ProductAttributeValue
DELETE FROM ProductAttribute
DELETE FROM Picture
DELETE FROM Product_Category_Mapping
DELETE FROM Product
DELETE FROM Product_Picture_Mapping
DELETE FROM Product_ProductAttribute_Mapping
DELETE FROM Product_ProductTag_Mapping
DELETE FROM Product_SpecificationAttribute_Mapping
DELETE FROM ProductReview
DELETE FROM ProductReviewHelpfulness
DELETE UrlRecord Where EntityName = 'Product'

DBCC CHECKIDENT (ShoppingCartItem, RESEED, 0)
DBCC CHECKIDENT (OrderNote, RESEED, 0)
DBCC CHECKIDENT (RecurringPayment, RESEED, 0)
DBCC CHECKIDENT (RewardPointsHistory, RESEED, 0)
DBCC CHECKIDENT ([Order], RESEED, 0)
DBCC CHECKIDENT(ProductAttributeCombination, RESEED, 0)
DBCC CHECKIDENT (RelatedProduct, RESEED, 0)
DBCC CHECKIDENT ([ProductAttributeValue], RESEED, 0)
DBCC CHECKIDENT (ProductAttribute, RESEED, 0)
DBCC CHECKIDENT (Picture, RESEED, 0)
DBCC CHECKIDENT (Product_Category_Mapping, RESEED, 0)
DBCC CHECKIDENT (Product, RESEED, 0)
DBCC CHECKIDENT (Product_Picture_Mapping, RESEED, 0)
DBCC CHECKIDENT (Product_ProductAttribute_Mapping, RESEED, 0)
DBCC CHECKIDENT (Product_SpecificationAttribute_Mapping, RESEED, 0)
DBCC CHECKIDENT (ProductReview, RESEED, 0)
DBCC CHECKIDENT (ProductReviewHelpfulness, RESEED, 0)

DBCC DBREINDEX (ShoppingCartItem, " ")
DBCC DBREINDEX (OrderNote, " ")
DBCC DBREINDEX (RecurringPayment, " ")
DBCC DBREINDEX (RewardPointsHistory, " ")
DBCC DBREINDEX ([Order], " ")
DBCC DBREINDEX (ProductAttributeCombination, " ")
DBCC DBREINDEX (RelatedProduct, " ")
DBCC DBREINDEX (ProductAttributeValue, " ")
DBCC DBREINDEX (ProductAttribute, " ")
DBCC DBREINDEX (Picture, " ")
DBCC DBREINDEX (Product_Category_Mapping, " ")
DBCC DBREINDEX (Product, " ")
DBCC DBREINDEX (Product_Picture_Mapping, " ")
DBCC DBREINDEX (Product_ProductAttribute_Mapping, " ")
DBCC DBREINDEX (Product_SpecificationAttribute_Mapping, " ")
DBCC DBREINDEX (ProductReview, " ")
DBCC DBREINDEX (ProductReviewHelpfulness, " ")

The above query worked for Products only. I still have the Categories to remove.



0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(21 row(s) affected)

(0 row(s) affected)

(21 row(s) affected)

(3 row(s) affected)

(0 row(s) affected)

(6 row(s) affected)

(7 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

I'm using this with 3.80.

This procedure optionally resets categories and manufacturers.

Notice it uses ALTER INDEX rather than the now depracated DBCC DBREINDEX.

ALTER INDEX does not automatically update statistics as DBCC DBREINDEX does; thus EXEC sp_updatestats at the end.

Be careful; as a stored procedure this is dangerous in that it deletes a lot of data in one step. So, back up your database before installing and running this stored procedure.

CREATE PROCEDURE [dbo].[RESET_DB]
(
   @ResetCategories bit,
   @ResetManufacturers bit
)
AS
SET NOCOUNT ON;
----------------------------------------------------------------------------------------
-- Delete Product and Order and related tables and data.
DELETE FROM ShoppingCartItem;
DELETE FROM OrderNote;
DELETE FROM RecurringPayment;
DELETE FROM RewardPointsHistory;
DELETE FROM [Order];  -- many deletes cascade from here
DELETE FROM ProductAttributeCombination;
DELETE FROM RelatedProduct;
DELETE FROM ProductAttributeValue;
DELETE FROM ProductAttribute;
DELETE FROM Picture;
DELETE FROM Product_Category_Mapping;
DELETE FROM Product;
DELETE FROM Product_Picture_Mapping;
DELETE FROM Product_ProductAttribute_Mapping;
DELETE FROM Product_ProductTag_Mapping;
DELETE FROM Product_SpecificationAttribute_Mapping;
DELETE FROM ProductReview;
DELETE FROM ProductReviewHelpfulness;
DELETE UrlRecord Where EntityName = 'Product';

-- Delete Category and related tables and data.
IF (@ResetCategories = 1) BEGIN
   DELETE FROM Category;
   DELETE UrlRecord Where EntityName = 'Category';
END

-- Delete Manufacturer and related tables and data.
IF (@ResetManufacturers = 1) BEGIN
   DELETE FROM Manufacturer;
   DELETE UrlRecord Where EntityName = 'Manufacturer';
END

-- Reseed IDENTITY property
DBCC CHECKIDENT (ShoppingCartItem, RESEED, 0);
DBCC CHECKIDENT (OrderNote, RESEED, 0);
DBCC CHECKIDENT (RecurringPayment, RESEED, 0);
DBCC CHECKIDENT (RewardPointsHistory, RESEED, 0);
DBCC CHECKIDENT ([Order], RESEED, 0);
DBCC CHECKIDENT(ProductAttributeCombination, RESEED, 0);
DBCC CHECKIDENT (RelatedProduct, RESEED, 0)
DBCC CHECKIDENT ([ProductAttributeValue], RESEED, 0);
DBCC CHECKIDENT (ProductAttribute, RESEED, 0);
DBCC CHECKIDENT (Picture, RESEED, 0);
DBCC CHECKIDENT (Product_Category_Mapping, RESEED, 0);
DBCC CHECKIDENT (Product, RESEED, 0);
DBCC CHECKIDENT (Product_Picture_Mapping, RESEED, 0);
DBCC CHECKIDENT (Product_ProductAttribute_Mapping, RESEED, 0);
DBCC CHECKIDENT (Product_SpecificationAttribute_Mapping, RESEED, 0);
DBCC CHECKIDENT (ProductReview, RESEED, 0);
DBCC CHECKIDENT (ProductReviewHelpfulness, RESEED, 0);

IF (@ResetCategories = 1) BEGIN
   DBCC CHECKIDENT (Category, RESEED, 0);
END

IF (@ResetManufacturers = 1) BEGIN
   DBCC CHECKIDENT (Manufacturer, RESEED, 0);
END

-- Rebuild indexes on Product and Order and related tables.
ALTER INDEX ALL ON dbo.ShoppingCartItem REBUILD;
ALTER INDEX ALL ON dbo.OrderNote REBUILD;
ALTER INDEX ALL ON dbo.RecurringPayment REBUILD;
ALTER INDEX ALL ON dbo.RewardPointsHistory REBUILD;
ALTER INDEX ALL ON dbo.[Order] REBUILD;
ALTER INDEX ALL ON dbo.ProductAttributeCombination REBUILD;
ALTER INDEX ALL ON dbo.RelatedProduct REBUILD;
ALTER INDEX ALL ON dbo.ProductAttribute REBUILD;
ALTER INDEX ALL ON dbo.Picture REBUILD;
ALTER INDEX ALL ON dbo.Product_Category_Mapping REBUILD;
ALTER INDEX ALL ON dbo.Product REBUILD;
ALTER INDEX ALL ON dbo.Product_Picture_Mapping REBUILD;
ALTER INDEX ALL ON dbo.Product_ProductAttribute_Mapping REBUILD;
ALTER INDEX ALL ON dbo.Product_SpecificationAttribute_Mapping REBUILD;
ALTER INDEX ALL ON dbo.ProductReview REBUILD;
ALTER INDEX ALL ON dbo.ProductReviewHelpfulness REBUILD;

IF (@ResetCategories = 1) BEGIN
   ALTER INDEX ALL ON dbo.Category REBUILD;
END

IF (@ResetManufacturers = 1) BEGIN
   ALTER INDEX ALL ON dbo.Manufacturer REBUILD;
END

-- Update statistics
EXEC sp_updatestats;

/*---- EOSP ----*/

Can anyone shared 3.9 Version script or will it work on 3.8 version script?

I've tried the stored procedure RESET_DB purposed by Jeff330cia.  I can confirm that it works on nopCommerce 4.00.

zyxious,

zyxious wrote:

How do you run this stored procedure?

Do I simply copy it to a query window in Microsoft SQL Server Management Studio and execute it?

Any help that you can provide would be gratefully appreciated.

Thanks,
Tony

Hello,

I'm using nopCommerce 4.0 with source and SQL Server 2016 with SQL Server Management Studio.

Does anyone know how to execute the stored procedure proposed by jeff330cia on this page?

I tried following the directions from this article, but I get this error: Msg 201, Level 16, State 4, Procedure RESET_DB, Line 0 [Batch Start Line 2]
Procedure or function 'RESET_DB' expects parameter '@ResetCategories', which was not supplied.

Any help would be gratefully appreciated.

Thanks,
Tony

Any updates on 4.1?

Trying to delete Category, Manufactures, products