The command argument for sp_MSforeachdb has a max length of 2,000 characters, and your query exceeds that.
There are alternatives to sp_MSforeachdb out there which work better – here is...

Or you can use dynamic SQL:

DECLARE @category VARCHAR(50);
DECLARE @sql VARCHAR(1000);

--Based on category values i should get records dynamically
SET @category = 'lamp';
SET...

I appreciate that this is not using SSMS, but getting this info out in a structured format is just a few clicks away if you create a database project in...

Try this

DROP TABLE IF EXISTS #LoanNumber;

CREATE TABLE #LoanNumber
(
  Loannumber  VARCHAR(10)
, ID    INT IDENTITY(1000, 1)
, GeneratedLoan AS RIGHT('0000000000' + CAST(ID AS VARCHAR(10)),10)
);