How to delete junk customer records using stored procedure.

Actually in my site database lake of customer junk records. So how to delete's lot of records with delete relevant tables mapping like customer_role_mapping, ShoppingCartItem, Order, BlogComment, ProductReviews, NewsComment, ProductReviewHelpfulness, PollVotingRecord, Forums_Topic, Forums_Post & GenericAttribute.

Thanks In Advanced!!!

RE: "Junk"
If the customer is only a Guest (not Registered), then I think he can only have ShoppingCartItem.

Hello,

If you are trying to delete all records for your guests you can try this:
Go to Administration -> System -> Maintenance. You should see a Deleting guest customers block. From there you can delete your guest customers.

If you have too many guest customers in your database, trying to delete them from the administration might timeout, though. In that case, you can try this:
Edit your database, you should be able to find the dbo.DeleteGuests stored procedure. You can execute it. The stored procedure takes 4 parameters. You can pass null values for them, or pass values that will limit the records the stored procedure will delete from your database. E.g. CreatedFromUtc and CreatedToUtc.

Hope this information was helpful!

Regards,
Anton

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

This is how I find spam/inactive users and it filters the registered user's whos has none of these
-address,
-orders,
-shop-cart item
And never opened the site again after registration

SELECT c.[Id],[Username],[Email],c.[CreatedOnUtc],[LastLoginDateUtc],[LastActivityDateUtc]
FROM [Customer] as c
join Customer_CustomerRole_Mapping as crm on c.id=crm.Customer_Id
left join ShoppingCartItem as sc on c.id=sc.CustomerId
left join CustomerAddresses as ca on c.id=ca.Customer_Id
where  CustomerRole_Id =3 and c.deleted=0
and ca.Address_Id is null 
and sc.CustomerId is null
and datediff(day, c.CreatedOnUtc, LastActivityDateUtc) = 0 
order by c.Id, LastLoginDateUtc, LastActivityDateUtc 

How do you delete them? I deployed a 4.3, we still didn't go live and only have the admin as an active user, now I have about a thousand inactive users.

I just enabled Recaptcha 3 ( I didn't think I would need it before going live )

Any information on how to delete all users (all inactive) / except the admin (active)

Thanks in advanced

If they are 'Guest' users, then use the scheduled task to delete them.
If they are 'Registered' spammy users, then you can use SQL tool to delete them (like mentioned above).

Agree with NY. Guest customers are easily removed using the maintenance tools of default nopCommerce. If your allowing standard registration anybody can register without hesitations including the spammers of the former posters post, although reCaptcha can limit the robots, it can't stop the spammer with fingers. If you truly only want legit registrations you have to think about using email verification as your registration method. I wish there was a separate guest customer table that you can truncate and reset, as the guest customer accounts rack up the customerid's exponentially over time. You might have a 100 or thousands of actual customers but there id values may be in the hundreds thousand and even millions. I know there are ways to workaround this for db gurus and us who have some knowledge of development.

But it's just an "Id", so does it really matter?

Technically no. As I said, there are work around's that can be implemented, as I do and have for many years. It wasn't said to start an argument. There are many quirks in nop that many wish existed to make day to day management of their stores easier. This is one of mine! Technically then it should be a column named Id rather than CustomerId in my opinion, but that's all it is my opinion. I also know why it is, as it is easier to reference in other table mappings.