Ki2ie0vN

1CREATE PROCEDURE `check_fk`( IN tbl_name varchar(64), IN id INT )
2BEGIN
3
4DECLARE salir     INT DEFAULT 0;
5DECLARE fk_table  VARCHAR(64);
6DECLARE fk_column VARCHAR(64);
7DECLARE cursor_tablas_fk CURSOR FOR
8SELECT kcu.table_name, kcu.column_name
9  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
10 WHERE kcu.TABLE_SCHEMA=DATABASE()
11   AND kcu.referenced_table_name = tbl_name;
12   
13DECLARE CONTINUE HANDLER FOR NOT FOUND
14BEGIN
15	SET salir = 1;
16END;
17
18CREATE TEMPORARY TABLE IF NOT EXISTS tablas_rompe(
19	tbl_name VARCHAR(64)
20);
21
22DELETE FROM tablas_rompe;
23
24OPEN cursor_tablas_fk;
25
26loop_tablas: LOOP
27
28	FETCH cursor_tablas_fk INTO fk_table, fk_column;
29	
30	IF salir THEN LEAVE loop_tablas; END IF;
31	
32	SET @sql_check_fk = CONCAT('SET @existe_id = EXISTS( SELECT *',
33											             ' FROM ',fk_table,
34											            ' WHERE ',fk_column,'=',id,' )');
35                                            
36	PREPARE check_fk_select FROM @sql_check_fk;
37	EXECUTE check_fk_select;
38	DEALLOCATE PREPARE check_fk_select;
39	
40    IF @existe_id THEN
41		INSERT INTO tablas_rompe(tbl_name)
42        VALUES(fk_table);
43    END IF;
44    
45END LOOP;
46
47CLOSE cursor_tablas_fk;
48
49END