Firstly thanks a lot Anton & everyone !!!
Actually i'm not active captcha in my site that cause lot of junk records (not valid users) added. I don't want to delete Guest records.
e.g. some emails -
[email protected][email protected][email protected][email protected].
.
...etc. thousands of records in customer table.
i'm trying follows SP & this can be work for me...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteJunkCustomer]
@JunkCustomerPattern nvarchar,
@TotalRecordsDeleted int = null OUTPUT
AS
BEGIN
CREATE TABLE #tmp_customers (CustomerId int)
INSERT #tmp_customers (CustomerId)
SELECT [Id] FROM [Customer] c with (NOLOCK)
WHERE Email like @JunkCustomerPattern
AND
((NOT EXISTS(SELECT 1 FROM [ShoppingCartItem] sci inner join [Customer] on sci.[CustomerId]=c.[Id])))
--no orders
AND
(NOT EXISTS(SELECT 1 FROM [Order] o with (NOLOCK) inner join [Customer] with (NOLOCK) on o.[CustomerId]=c.[Id]))
----delete junk cust
DELETE [Customer]
WHERE [Id] IN (SELECT [CustomerID] FROM #tmp_customers)
----delete attributes
DELETE [GenericAttribute]
WHERE ([EntityID] IN (SELECT [CustomerID] FROM #tmp_customers))
AND
([KeyGroup] = N'Customer')
----total records
SELECT @TotalRecordsDeleted = COUNT(1) FROM #tmp_customers
DROP TABLE #tmp_customers
END
GO