DB Clean Up Scripts

TABLE OF CONTENTS

Introduction

This article describes how all the entity / transactional data (eg. Znode sample/default data) can be removed from the database, which is not required for projects before go-live.

Published data Clean Up Script

Implementation: 

In order to purge published data, the Znode_PurgepublishedData stored procedure needs to be executed using the below parameters:

@DeleteAllPublishedData :

1 - Deletes all published data from the Znode database.

0 - Does not delete any published data.

Sample execution call: 

EXEC Znode_PurgePublishedData @DeleteAllPublishedData = 1
 

Order Clean Up Script

Implementation: 

In order to purge Order data, the Znode_PurgeOrderData stored procedure needs to be executed using the below parameters:

@DeleteAllOrder :

1 - Deletes all orders from the Znode database.

0 - Does not delete any orders.

Sample execution call: 

EXEC Znode_PurgeOrderData @DeleteAllOrder = 1 

Product Clean Up Script

Implementation: 

In order to purge product data, the Znode_PurgeProductData stored procedure needs to be executed using the below parameters:

@DeleteAllProduct :

1 - Deletes all products from the Znode database. It is important to pass product IDs in the @ExceptProductId parameter to skip the deletion of required products.

0 - Does not delete any product.

Sample execution call: 

--Pass products which are  required (not to delete)

Declare @ExceptProductId TransferId

INSERT INTO @ExceptProductId(Id)

SElECT PimProductId

FROM ZnodePimProduct WHERE PimProductId in (0)

EXEC Znode_PurgeProductData @DeleteAllProduct = 1 ,@ExceptProductId=@ExceptProductId

Catalog Clean Up

Implementation: 

In order to purge catalog data, the Znode_PurgeCatalogData stored procedure needs to be executed using the below parameters:

@DeleteAllCatalog :

1 - Deletes all catalog from the Znode database. Pass catalog ids in the @ExceptCatalogId parameter to skip the deletion of required catalog.

0 - Will not delete any catalog.

Sample execution call: 

--Pass catalog which are required (not to delete)

Declare @ExceptCatalogId TransferId

INSERT INTO @ExceptCatalogId(Id)

SElECT PimCatalogId

FROM ZnodePimCatalog WHERE PimCatalogId IN (0)

 

EXEC Znode_PurgeCatalogData @DeleteAllCatalog = 1 ,@ExceptCatalogId=@ExceptCatalogId

Category Clean Up

Implementation: 

In order to purge category data, the Znode_PurgeCategoryData stored procedure needs to be executed using the below parameters:

@DeleteAllCategory :

1 - Deletes all categories from the Znode database. Pass category Ids in the @ExceptCategoryId parameter to skip the deletion of required categories.

0 - Will not delete any category.

Sample execution call: 

--Pass category ids which are required (not to delete)

Declare @ExceptCategoryId TransferId

INSERT INTO @ExceptCategoryId (Id)

SElECT PimCategoryId 

FROM ZnodePimCategory WHERE PimCategoryId  in (0)

 

EXEC Znode_PurgeCategoryData @DeleteAllCategory = 1 ,@ExceptCategoryId=@ExceptCategoryId

User Clean Up

Implementation: 

In order to purge user data, the Znode_PurgeUserData stored procedure needs to be executed using the below parameters:

@DeleteAllUser :

1 - Deletes all users from the Znode database. Pass user Ids in the @ExceptUserId parameter table to skip the deletion of required users.

0 - Will not delete any users.

Sample execution call: 

--Pass user ids which are required (not to delete)

Declare @ExceptUserId TransferId

INSERT INTO @ExceptUserId (Id)

SElECT UserId

FROM ZnodeUser WHERE UserId IN (2)

 

EXEC Znode_PurgeUserData @DeleteAllUser = 1 ,@ExceptUserId=@ExceptUserId

Global Attribute Clean Up

Implementation: 

In order to purge global attribute data, the Znode_PurgeGlobalAttributeData stored procedure needs to be executed using the below parameters:

@DeleteAllGlobalAttribute :

1 - Deletes all global attribute data from the Znode database. Pass global attribute Ids in the @ExceptGlobalAttributeId parameter to skip the deletion of required global attribute data.

0 - Will not delete any global attributes. 

Note: In any case system defined global attribute data not gets delete

Sample execution call: 

--Pass global attribute ids which are required (not to delete)

Declare @ExceptGlobalAttributeId TransferId

INSERT INTO @ExceptGlobalAttributeId (Id)

SElECT GlobalAttributeId

FROM ZnodeGlobalAttribute WHERE GlobalAttributeId IN (0)

 

EXEC Znode_PurgeGlobalAttributeData @DeleteAllGlobalAttribute = 1 ,@ExceptGlobalAttributeId=@ExceptGlobalAttributeId

 

Product And Category Attribute Clean Up

Implementation: 

In order to purge product and category attribute data, the Znode_PurgeProductCategoryAttributeData stored procedure needs to be executed using the below parameters:

@DeleteAllProductCategoryAttribute :

1 - Deletes all the product and category attribute data from the Znode database. Pass global attribute Ids in the @ExceptProductCategoryAttributeId parameter to skip the deletion of required global attribute data.

0 - Will not delete any product category attributes. 

Note: In any case system defined product and category attribute data not gets delete

Sample execution call: 

--Pass product and category attribute ids which are required (not to delete)

Declare @ExceptProductCategoryAttributeId TransferId

INSERT INTO @ExceptProductCategoryAttributeId (Id)

SElECT PimAttributeId

FROM ZnodePimAttribute WHERE PimAttributeId IN (0)

 

EXEC Znode_PurgeProductCategoryAttributeData @DeleteAllProductCategoryAttribute = 1 ,@ExceptProductCategoryAttributeId = @ExceptProductCategoryAttributeId

Store Clean Up

Implementation: 

In order to purge store data, the Znode_PurgeStoreData stored procedure needs to be executed using the below parameters:

@DeleteAllStore :

1 - Deletes all the store data from the Znode database. Pass store Ids (PotalId) in the @ExceptstoreId parameter to skip the deletion of required stores.

0 - Will not delete any stores.

Sample execution call: 

--Pass store ids which are required (not to delete)

Declare @ExceptstoreId TransferId

INSERT INTO @ExceptstoreId  (Id)

SElECT PortalId

FROM ZnodePortal WHERE PortalId IN (0)

 

EXEC Znode_PurgeStoreData @DeleteAllStore = 1 , @ExceptstoreId = @ExceptstoreId

CMS Data Clean Up

Implementation: 

In order to purge CMS data, the Znode_PurgeCMSData stored procedure needs to be executed using the below parameters:

@DeleteAllCMSData :

1 - Deletes all the CMS data from the Znode database. 

0 - Will not delete any CMS data. 

Sample execution call: 

EXEC Znode_PurgeCMSData @DeleteAllCMSData = 1

Media Data Clean Up

Implementation: 

In order to purge media data, the Znode_PurgeData stored procedure needs to be executed using the below parameters:

@DeleteAllMedia :

1 - Deletes all the Media data from the Znode database. Pass media Ids (MediaId) in the @ExceptMediaId parameter to skip the deletion of required media.

0 - Will not delete any media. 

Sample execution call: 

--Pass media ids which are required (not to delete)

Declare @ExceptMediaId TransferId

INSERT INTO @ExceptMediaId  (Id)

SElECT MediaId

FROM ZnodeMedia WHERE MediaId IN (1)

 

EXEC Znode_PurgeData @DeleteAllMedia = 1,@ExceptMediaId = @ExceptMediaId

PriceList Data Clean Up

Implementation: 

In order to purge pricelist data, the Znode_PurgeData stored procedure needs to be executed using the below parameters:

@DeleteAllPricelist :

1 - Deletes all the price list data from the Znode database. Pass pricelist Ids in the @ExceptPricelistId parameter to skip the deletion of required pricelist.

0 - Will not delete any price list. 

Sample execution call: 

--Pass pricelist ids which are required (not to delete)

Declare @ExceptPricelistId TransferId

INSERT INTO @ExceptPricelistId  (Id)

SElECT PriceListId

FROM ZnodePriceList WHERE PriceListId IN (0)

 

EXEC Znode_PurgeData @DeleteAllPricelist = 1, @ExceptPricelistId = @ExceptPricelistId


Profile Data Clean Up

Implementation: 

In order to purge price list data, the Znode_PurgeData stored procedure needs to be executed using the below parameters:

@DeleteAllProfile :

1 - Deletes all the price list data from the Znode database. Pass profile Ids in the @ExceptProfileId parameter to skip the deletion of required profiles.

0 - Will not delete any profile. 

Sample execution call: 

--Pass profile ids which are required (not to delete)

Declare @ExceptProfileId TransferId

INSERT INTO @ExceptProfileId  (Id)

SElECT ProfileId

FROM ZnodeProfile WHERE ProfileId IN (0)

 

EXEC Znode_PurgeData @DeleteAllProfile = 1, @ExceptProfileId = @ExceptProfileId

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.