new cmdlet suggestion

[Splaxi]

We should implement something that can work on a configuration style approach to clean up different data.

Emails is the first

PurchTable.Email
SalesTable.Email
SalesQuotationTable.Email
VendInvoiceInfoTable.VendorRequestedWorkerEmail
VendInvoiceJour.VendorRequestedWorkerEmail
EventInbox.EmailRecipient
ProjTable.Email
BankAccountTable.Email
BankGroup.Email
CustBankAccount.Email
CustInvoiceJour.mcrEmail
VendBankAccount.Email
CustPackingSlipJour.ShipCarrierEmail
HCMWorkerBankAccount.Email
Plus SysUserInfo.Email and LogisticsElectronicAddress.Locator which are cross company.

Raw SQL script

-- Ensure that database log is purged
TRUNCATE TABLE SYSDATABASELOG

DECLARE @TableID int;
DECLARE @TriggerName nvarchar(40)

BEGIN TRANSACTION

-- Drop any trigger over DirPartyTable
SELECT @TableID=TABLEID FROM SQLDICTIONARY WHERE NAME='DirPartyTable' AND FIELDID=0
SET @TriggerName = 'SysDbLog_' + CAST(@TableID AS NVARCHAR(10)) + '_Trigger';
IF EXISTS (SELECT * FROM sys.triggers WHERE NAME = @TriggerName)
BEGIN
	EXECUTE ('DROP TRIGGER [' + @TriggerName+']')
END

-- Drop any trigger over LogisticsPostalAddress
SELECT @TableID=TABLEID FROM SQLDICTIONARY WHERE NAME='LOGISTICSPOSTALADDRESS' AND FIELDID=0
SET @TriggerName = 'SysDbLog_' + CAST(@TableID AS NVARCHAR(10)) + '_Trigger';
IF EXISTS (SELECT * FROM sys.triggers WHERE NAME = @TriggerName)
BEGIN
	EXECUTE ('DROP TRIGGER [' + @TriggerName+']')
END

-- Drop any trigger over LogisticsElectronicAddress
SELECT @TableID=TABLEID FROM SQLDICTIONARY WHERE NAME='LOGISTICSELECTRONICADDRESS' AND FIELDID=0
SET @TriggerName = 'SysDbLog_' + CAST(@TableID AS NVARCHAR(10)) + '_Trigger';
IF EXISTS (SELECT * FROM sys.triggers WHERE NAME = @TriggerName)
BEGIN
	EXECUTE ('DROP TRIGGER [' + @TriggerName+']')
END

-- Drop any trigger over LogisticsLocation
SELECT @TableID=TABLEID FROM SQLDICTIONARY WHERE NAME='LOGISTICSLOCATION' AND FIELDID=0
SET @TriggerName = 'SysDbLog_' + CAST(@TableID AS NVARCHAR(10)) + '_Trigger';
IF EXISTS (SELECT * FROM sys.triggers WHERE NAME = @TriggerName)
BEGIN
	EXECUTE ('DROP TRIGGER [' + @TriggerName+']')
END

-- =================================================================================================================

-- Vendor name
UPDATE DIRPARTYTABLE
SET NAME = 'Vendor ' + VENDTABLE.ACCOUNTNUM, NAMEALIAS = 'Vendor ' + VENDTABLE.ACCOUNTNUM
FROM VENDTABLE WHERE DIRPARTYTABLE.RECID = VENDTABLE.PARTY

-- Vendor Address
UPDATE LOGISTICSPOSTALADDRESS
SET ADDRESS = 'Vendor ' + VENDTABLE.ACCOUNTNUM + ' address'
FROM	LOGISTICSPOSTALADDRESS, LOGISTICSLOCATION, LOGISTICSLOCATIONROLE, DIRPARTYLOCATION, VENDTABLE
WHERE	LOGISTICSLOCATION.RECID = LOGISTICSPOSTALADDRESS.LOCATION
AND		DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
AND		DIRPARTYLOCATION.PARTY = VENDTABLE.PARTY

-- Vendor telephone/fax numbers (basically, everything except email addresses)
UPDATE LOGISTICSELECTRONICADDRESS
SET LOCATOR = 'Vendor ' + VENDTABLE.ACCOUNTNUM + ' contact', DESCRIPTION = 'Vendor ' + VENDTABLE.ACCOUNTNUM + ' contact'
FROM	LOGISTICSELECTRONICADDRESS, LOGISTICSLOCATION, DIRPARTYLOCATION, VENDTABLE
WHERE	LOCATOR NOT LIKE '%@%'
AND		LOGISTICSLOCATION.RECID = LOGISTICSELECTRONICADDRESS.LOCATION
AND		DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
AND		DIRPARTYLOCATION.PARTY = VENDTABLE.PARTY

-- Vendor Location description
UPDATE LOGISTICSLOCATION
SET		DESCRIPTION = 'Vendor ' + VENDTABLE.ACCOUNTNUM + ' location'
FROM	LOGISTICSLOCATION, LOGISTICSLOCATIONROLE, DIRPARTYLOCATION, VENDTABLE
WHERE	DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
AND		DIRPARTYLOCATION.PARTY = VENDTABLE.PARTY

-- =================================================================================================================

-- Customer name
UPDATE DIRPARTYTABLE
SET NAME = 'Customer ' + CUSTTABLE.ACCOUNTNUM, NAMEALIAS = 'Customer ' + CUSTTABLE.ACCOUNTNUM
FROM	CUSTTABLE
WHERE DIRPARTYTABLE.RECID = CUSTTABLE.PARTY

-- Customer Address
UPDATE LOGISTICSPOSTALADDRESS
SET		ADDRESS = 'Customer ' + CUSTTABLE.ACCOUNTNUM + ' address'
FROM	LOGISTICSPOSTALADDRESS, LOGISTICSLOCATION, LOGISTICSLOCATIONROLE, DIRPARTYLOCATION, CUSTTABLE
WHERE	LOGISTICSLOCATION.RECID = LOGISTICSPOSTALADDRESS.LOCATION
AND		DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
AND		DIRPARTYLOCATION.PARTY = CUSTTABLE.PARTY

-- Customer telephone/fax numbers (basically, everything except email addresses)
UPDATE LOGISTICSELECTRONICADDRESS
SET LOCATOR = 'Customer ' + CUSTTABLE.ACCOUNTNUM + ' contact', DESCRIPTION = 'Customer ' + CUSTTABLE.ACCOUNTNUM + ' contact'
FROM	LOGISTICSELECTRONICADDRESS, LOGISTICSLOCATION, DIRPARTYLOCATION, CUSTTABLE
WHERE	LOCATOR NOT LIKE '%@%'
AND		LOGISTICSLOCATION.RECID = LOGISTICSELECTRONICADDRESS.LOCATION
AND		DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
AND		DIRPARTYLOCATION.PARTY = CUSTTABLE.PARTY

-- Customer Location description
UPDATE LOGISTICSLOCATION
SET		DESCRIPTION = 'Customer ' + CUSTTABLE.ACCOUNTNUM + ' location'
FROM	LOGISTICSLOCATION, LOGISTICSLOCATIONROLE, DIRPARTYLOCATION, CUSTTABLE
WHERE	DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
AND		DIRPARTYLOCATION.PARTY = CUSTTABLE.PARTY

-- =================================================================================================================

-- Worker name
UPDATE DIRPARTYTABLE
SET NAME = 'Worker ' + HCMWORKER.PERSONNELNUMBER, NAMEALIAS = 'Worker ' + HCMWORKER.PERSONNELNUMBER
FROM	 HCMWORKER
WHERE	DIRPARTYTABLE.RECID = HCMWORKER.PERSON

-- Worker Address
UPDATE LOGISTICSPOSTALADDRESS
SET		ADDRESS = 'Worker ' + HCMWORKER.PERSONNELNUMBER + ' address'
FROM	LOGISTICSPOSTALADDRESS, LOGISTICSLOCATION, LOGISTICSLOCATIONROLE, DIRPARTYLOCATION, HCMWORKER
WHERE	LOGISTICSLOCATION.RECID = LOGISTICSPOSTALADDRESS.LOCATION
AND		DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
AND		DIRPARTYLOCATION.PARTY = HCMWORKER.PERSON

-- Worker telephone/fax numbers (basically, everything except email addresses)
UPDATE LOGISTICSELECTRONICADDRESS
SET LOCATOR = 'Worker ' + HCMWORKER.PERSONNELNUMBER + ' contact', DESCRIPTION = 'Worker ' + HCMWORKER.PERSONNELNUMBER + ' contact'
FROM	LOGISTICSELECTRONICADDRESS, LOGISTICSLOCATION, DIRPARTYLOCATION, HCMWORKER
WHERE	LOCATOR NOT LIKE '%@%'
AND		LOGISTICSLOCATION.RECID = LOGISTICSELECTRONICADDRESS.LOCATION
AND		DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
AND		DIRPARTYLOCATION.PARTY = HCMWORKER.PERSON

-- Worker Location description
UPDATE LOGISTICSLOCATION
SET DESCRIPTION = 'Worker ' + HCMWORKER.PERSONNELNUMBER + ' location'
FROM	LOGISTICSLOCATION, LOGISTICSLOCATIONROLE, DIRPARTYLOCATION, HCMWORKER
WHERE	DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
AND		DIRPARTYLOCATION.PARTY = HCMWORKER.PERSON

-- =================================================================================================================

-- Projects
UPDATE PROJTABLE
SET NAME = 'Project ' + PROJID + ' name'

-- =================================================================================================================

-- Custom dimensions (no backing entity)
UPDATE DIMENSIONFINANCIALTAG
SET DESCRIPTION = 'Custom dimension ' + VALUE

-- =================================================================================================================

-- Remove all email addresses
UPDATE LogisticsElectronicAddress
SET		LOCATOR = ''
WHERE	LOCATOR LIKE '%@%'

-- =================================================================================================================

COMMIT TRANSACTION

[valerymoskalenko]

Thank you! It's helpful. Feel free to add this to the SQL script.

/* The Customers and Vendors Government ID number should be removed  */
update CustTable set IDENTIFICATIONNUMBER = N''
update VendTable set TAX1099REGNUM = N''