create procedure getPrimaryKeys(
		in @table_name char(256),
		in @column_name char(256))
	result(table_name char(256),
		column_name char(256),
		key_seq unsigned integer)
begin
	DECLARE err_notfound EXCEPTION FOR SQLSTATE '02000';
	DECLARE mySV CHAR(256);
	DECLARE mySC CHAR(256);
	DECLARE myTN CHAR(256);
	DECLARE myCN CHAR(256);
	DECLARE myCI INT;
	DECLARE count INT;
	DECLARE myCursor CURSOR FOR
		select table_name,
		column_name,
		column_id
		from SYS.SYSTABLE as t,
		SYS.SYSCOLUMN as c where
		t.table_id = c.table_id and
		table_name like @table_name and
		pkey in( 'Y','M');
	OPEN myCursor;
	SET count=0;
	SET mySV=@table_name;
	SET mySC=@column_name;
	MyLoop:
	LOOP
		FETCH NEXT myCursor INTO myTN,myCN,myCI;
		IF SQLSTATE=err_notfound THEN
			LEAVE myLoop;
		END IF;
		SET count=count+1;
		INSERT INTO pkeys(
			table_name,		
			column_name,
			column_id)
		VALUES(
			myTN,
			myCN,
			myCI);
	END LOOP MyLoop;
	IF COUNT=0 THEN
		INSERT INTO pkeys(
			table_name,
			column_name,
			column_id)
		VALUES(
			mySV,
			mySC,
			0);
	END IF;
	CLOSE myCursor;
END;

CREATE PROCEDURE findKeys()
BEGIN
    DECLARE my_name CHAR(256);
	 DECLARE my_column CHAR(256);
    DECLARE err_notfound EXCEPTION FOR SQLSTATE '02000';
    DECLARE myCursor CURSOR FOR 
        SELECT pk_table, pk_column
        FROM migrate_remote_fks_list;
    OPEN myCursor;
    MyLoop:
    LOOP
        FETCH NEXT myCursor INTO my_name,my_column;
        IF SQLSTATE=err_notfound THEN   
            LEAVE MyLoop;
        END IF;
        CALL getPrimaryKeys(my_name,my_column);
    END LOOP MyLoop;
    CLOSE myCursor;
END;

CREATE PROCEDURE fixFKPKNames()
BEGIN
	DECLARE statement LONG VARCHAR;
	DECLARE err_notfound EXCEPTION FOR SQLSTATE '02000';
	DECLARE pkCol LONG VARCHAR;
	DECLARE fkCol LONG VARCHAR;
	DECLARE myID INTEGER;
	DECLARE myCursor CURSOR FOR
		SELECT 
			migrate_remote_fks_list.fk_id,
			migrate_remote_fks_list.pk_column,
			migrate_remote_fks_list.fk_column
		FROM
			migrate_remote_fks_list;
	OPEN myCursor;
	MyLoop:
	LOOP
		FETCH NEXT myCursor INTO myID,pkCol,fkCol;
		IF SQLSTATE=err_notfound THEN
			LEAVE MyLoop;
		END IF;
		set statement = 'UPDATE migrate_remote_fks_list SET '+
			migrate_remote_fks_list.pk_name='+
			pkCol+
			',migrate_remote_fks_list.fk_name='+
			fkCol;
		message statement type info to client;
		UPDATE 
			migrate_remote_fks_list 
		SET 
			migrate_remote_fks_list.pk_name=pkCol,
			migrate_remote_fks_list.fk_name=fkCol 
		WHERE 
			migrate_remote_fks_list.fk_id=myID;
	END LOOP MyLoop;
	CLOSE myCursor;
END;

create procedure myMigrateFKS(in i_table_owner varchar(128))
begin
	declare stmt long varchar;
	declare pk_col_list long varchar;
	declare fk_col_list long varchar;
	for 
		fk as fkc dynamic scroll cursor for
			select 
				ef.fk_id as o_fk_id,
				ef.pk_table as o_pk_table,
				ef.pk_name as o_pk_name,
				ef.fk_table as o_fk_table,
				ef.fk_name as o_fk_name 
			from
				dbo.migrate_remote_fks_list as ef,
				dbo.migrate_remote_table_list as et 
			where
				ef.fk_table = et.table_name AND
				ef.created <>1
			group by 
				ef.fk_id,
				ef.pk_table,
				ef.pk_name,
				ef.fk_table,
				ef.fk_name 
	do
	IF NOT exists(SELECT 1 FROM sys.sysforeignkey WHERE role=o_fk_name)then
		select 
			list(pk_column order by key_seq asc),
			list(fk_column order by key_seq asc) 
		into 
			pk_col_list,
			fk_col_list 
		from
			dbo.migrate_remote_fks_list 
		where
			fk_table = o_fk_table and
			pk_table = o_pk_table and
			fk_name = o_fk_name;
		set stmt='PRIMARY KEY LIST: '+pk_col_list;
		message stmt type info to client;
		set stmt='FOREIGN KEY LIST: '+fk_col_list;
		message stmt type info to client;
		set stmt='ALTER TABLE '+'"'+
			i_table_owner+
			'"'+
			'.'+
			'"'+
			o_fk_table+'"'+
			' ADD FOREIGN KEY "'+
			o_fk_name+
			'" ( "'+
			fk_col_list+
			'" ) REFERENCES '+
			'"'+
			i_table_owner+
			'"."'+
			o_pk_table+
			'" ( "'+
			pk_col_list+
			'" )';
			message stmt type info to client;
			execute immediate stmt;
		ELSE
			message 'Foreign key '+o_fk_name+
			' already exists -- Skipping.' type info to client;
		END IF;
		update 
			dbo.migrate_remote_fks_list 
		set
			created = 1 
		where
			fk_name = o_fk_name;
	end for
end;