SQL_Procedure.sql

DECLARE
sql_stmt VARCHAR(1000);
csr SYS_REFCURSOR;
table_name VARCHAR(31);
n_cols DOUBLE PRECISION;
n_fks DOUBLE PRECISION;
n_rows DOUBLE PRECISION;
BEGIN
FOR x IN (SELECT INST FROM 
    (SELECT TABLE_NAME, 'SELECT b.TABLE_NAME NOMBRETABLA, NVL(NUMFK, 0), NUMCOLS, NUMFILAS FROM
        (
            SELECT TABLE_NAME, count(*) NUMFK
            FROM ALL_CONS_COLUMNS
            WHERE OWNER = ''PARRANDEROS'' AND CONSTRAINT_NAME LIKE ''FK%'' 
            AND TABLE_NAME = '''||TABLE_NAME||'''
            GROUP BY TABLE_NAME
        ) a
        RIGHT OUTER JOIN
        (
            SELECT TABLE_NAME, count(*) NUMCOLS
            FROM ALL_TAB_COLS
            WHERE OWNER=''PARRANDEROS''AND TABLE_NAME = '''||TABLE_NAME||'''
            GROUP BY TABLE_NAME
        ) b
        ON a.TABLE_NAME=b.TABLE_NAME
        LEFT OUTER JOIN
        (
            SELECT '''||TABLE_NAME||''' TABLE_NAME, count(*) NUMFILAS
            FROM PARRANDEROS.'||TABLE_NAME||'
            GROUP BY '''||TABLE_NAME||'''
        ) c
        ON b.TABLE_NAME = c.TABLE_NAME' INST
    FROM ALL_TABLES
    WHERE OWNER='PARRANDEROS' AND TABLE_NAME >= 'J%' AND TABLE_NAME < 'T%'))
    LOOP
    sys.dbms_output.put_line(x.inst);
    OPEN csr FOR x.inst;
    FETCH csr INTO table_name, n_cols, n_fks, n_rows;
    CLOSE csr;
    sys.dbms_output.put_line('Resultado: ' || table_name||' '|| n_cols ||' '|| n_fks ||' '|| n_rows);
END LOOP;
END;