/******************************************************************************
 * Programme : Administration SAS : Comment supprimer les clés étrangères orphelines des métadonnées
 * Reference : NETTOY4422
 * Source    : https://www.wearecas.eu/en/sampleCode/NETTOY4422
 ******************************************************************************/

/* --- BLOC 1 --- */
data _null_;
    /* Initialize variables. */
	length FK_id PT_id $17 type FK_uri PT_uri $50 PT_engine $64 PT_name $32 FK_name $60;
	call missing ( of _character_ );
  retain mode "&MODE";

  /* Define a query for ForeignKey FKobjects that do not have a PartnerUniqueKey association */
	FK_obj="omsobj:ForeignKey?ForeignKey[not(PartnerUniqueKey/*)]";

  /* Determine how many foreign key objects meet the criteria */
	FK_Count=metadata_resolve(FK_obj,type,FK_id);
	put "NOTE: Found " FK_Count "ForeignKey objects that do not have an associated PartnerUniqueKey";

  /* If any were found, iterate through each one to get the attributes and associations */
	if FK_Count > 0 then do i = 1 to FK_Count;
    /* Get the URI for the nth ForeignKey found */
		FK_rc=metadata_getnobj(FK_obj,i,FK_uri);

    /* Get the name and ID of the ForeignKey FKobject */
		FK_rc=metadata_getattr(FK_uri,"Name",FK_name);
    FK_rc=metadata_getattr(FK_uri,"Id",FK_id);
  
    /* get the table name and id this ForeignKey is associated with */
    PT_rc=metadata_getnasn(FK_uri,"Table",1,PT_uri);
		if PT_rc > 0 then do;
       /* Get the name and ID of the table. */
			PT_rc=metadata_getattr(PT_uri,"SASTableName",PT_name);
      PT_rc=metadata_getattr(PT_uri,"Id",PT_id);
    end;

    /* REPORT and DELETE */
    put "NOTE: Found foreign key: " FK_name "with id: " FK_id "for table: " PT_name "with id: " PT_id;
    if mode = "DELETE" then do;
      FK_rc = metadata_delobj(FK_uri);
      if FK_rc then put 'ERROR: metadata_delobj failed with return code ' FK_rc;
      else put 'NOTE: Foreign key successfully deleted'; 
    end;
	end;
run;