SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE  "AGENTS" 
   (	
    "CODE" CHAR(6) NOT NULL PRIMARY KEY, 
	"AGENT_NAME" CHAR(40), 
	"WORKING_AREA" CHAR(35), 
	"PHONE_NO" CHAR(15), 
	"COUNTRY" VARCHAR(25) 
	 );

CREATE TABLE  "CUSTOMER" 
   (	"CODE" VARCHAR(6) NOT NULL PRIMARY KEY, 
	"CUST_NAME" VARCHAR(40) NOT NULL, 
	"CUST_CITY" CHAR(35), 
	"WORKING_AREA" VARCHAR(35) NOT NULL, 
	"CUST_COUNTRY" VARCHAR(20) NOT NULL, 
	"OPENING_AMT" numeric(12,2) NOT NULL, 
	"RECEIVE_AMT" numeric(12,2) NOT NULL, 
	"PAYMENT_AMT" numeric(12,2) NOT NULL, 
	"PHONE_NO" VARCHAR(17) NOT NULL, 
	"AGENT_CODE" CHAR(6) NOT NULL REFERENCES AGENTS
);   

CREATE TABLE  "ORDERS" 
   (
        "ORD_NUM" int NOT NULL PRIMARY KEY, 
	"ORD_AMOUNT" numeric(12,2) NOT NULL, 
	"ADVANCE_AMOUNT" numeric(12,2) NOT NULL, 
	"ORD_DATE" DATE NOT NULL, 
	"CUST_CODE" VARCHAR(6) NOT NULL REFERENCES CUSTOMER, 
	"ORD_DESCRIPTION" VARCHAR(60) NOT NULL
   );
CREATE OR ALTER FUNCTION dbo.GetJoinCols (@parent_object_id int, @child_object_id int, @fk_object_id int)
RETURNS TABLE
AS RETURN
SELECT
  JoinCols = 
    STRING_AGG(
      CAST(
        'c.' + QUOTENAME(cChild.name) + ' = p.' + QUOTENAME(cParent.name)
        AS nvarchar(max)
      ),
      ' AND '
    )
FROM sys.foreign_key_columns fkc
JOIN sys.columns cParent ON cParent.object_id = @parent_object_id AND cParent.column_id = fkc.referenced_column_id
JOIN sys.columns cChild ON cChild.object_id = @child_object_id AND cChild.column_id = fkc.parent_column_id
WHERE fkc.constraint_object_id = @fk_object_id;
CREATE OR ALTER PROC DeleteWithFK
    @tableName sysname, -- name of the table where rows are to be deleted
    @Criteria nvarchar(1000), -- criteria used to delete the rows required
    @RowsAffected int = NULL OUTPUT, -- number of records affected by the delete /
    @schemaName sysname = 'dbo'
As
SET NOCOUNT ON;

DECLARE @sql nvarchar(max);

WITH cte AS (
    SELECT
      Level = 0,
      t.object_id,
      TableToDelete = QUOTENAME('cte' + t.name),  --must be the CTE name from below
      TablesAsCte = CONCAT(
        QUOTENAME('cte' + t.name),
        ' AS (
  SELECT *
  FROM ',
        QUOTENAME(s.name),
        '.',
        QUOTENAME(t.name),
        '
  WHERE ',
        CAST(@Criteria AS nvarchar(max)),
        '
)'
      )
    FROM sys.tables t
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = @schemaName
      AND t.name = @tableName

    UNION ALL
  
    SELECT
      cte.Level + 1,
      tChild.object_id,
      QUOTENAME('cte' + tChild.name),  --must be the CTE name from below
      CONCAT(
        cte.TablesAsCte,
        ',
',
        QUOTENAME('cte' + tChild.name),
        ' AS (
  SELECT c.*
  FROM ',
        QUOTENAME(sChild.name),
        '.',
        QUOTENAME(tChild.name),
        ' c
  WHERE EXISTS (SELECT 1
      FROM ',
        cte.TableToDelete,
        ' p WHERE ',
        cols.JoinCols,
        '
    )
)'
      )
    FROM cte
    JOIN sys.foreign_keys fk ON fk.referenced_object_id = cte.object_id
    JOIN sys.tables tChild ON tChild.object_id = fk.parent_object_id
    JOIN sys.schemas sChild ON sChild.schema_id = tChild.schema_id
    CROSS APPLY dbo.GetJoinCols(cte.object_id, tChild.object_id, fk.object_id) cols
)

SELECT
  @sql = STRING_AGG(
    CONCAT(
      'WITH ',
      cte.TablesAsCte,
      '
DELETE ',
      TableToDelete
    ),
    ';

'
  ) WITHIN GROUP (ORDER BY cte.Level DESC)
FROM cte;

SET @sql += ';

SET @RowsAffected = @@ROWCOUNT;
';

PRINT @sql;  -- your friend

EXEC sp_executesql @sql,
    N'@RowsAffected int OUTPUT',
    @RowsAffected = @RowsAffected OUTPUT;
EXEC DeleteWithFK 'Agents', '1=1';
WITH [cteAGENTS] AS (
  SELECT *
  FROM [dbo].[AGENTS]
  WHERE 1=1
),
[cteCUSTOMER] AS (
  SELECT c.*
  FROM [dbo].[CUSTOMER] c
  WHERE EXISTS (SELECT 1
      FROM [cteAGENTS] p WHERE c.[AGENT_CODE] = p.[CODE]
    )
),
[cteORDERS] AS (
  SELECT c.*
  FROM [dbo].[ORDERS] c
  WHERE EXISTS (SELECT 1
      FROM [cteCUSTOMER] p WHERE c.[CUST_CODE] = p.[CODE]
    )
)
DELETE [cteORDERS];

WITH [cteAGENTS] AS (
  SELECT *
  FROM [dbo].[AGENTS]
  WHERE 1=1
),
[cteCUSTOMER] AS (
  SELECT c.*
  FROM [dbo].[CUSTOMER] c
  WHERE EXISTS (SELECT 1
      FROM [cteAGENTS] p WHERE c.[AGENT_CODE] = p.[CODE]
    )
)
DELETE [cteCUSTOMER];

WITH [cteAGENTS] AS (
  SELECT *
  FROM [dbo].[AGENTS]
  WHERE 1=1
)
DELETE [cteAGENTS];

SET @RowsAffected = @@ROWCOUNT;