我有以下查詢，它回傳模式中每個表的行數。
這也可以修改為回傳每個表的列數嗎？
CREATE table stats (
table_name VARCHAR2(128),
num_rows NUMBER,
num_cols NUMBER
);
/
DECLARE
val integer;
BEGIN
for i in (SELECT table_name FROM all_tables WHERE owner = 'Schema')
LOOP
EXECUTE IMMEDIATE 'SELECT count(*) from ' || i.table_name INTO val;
INSERT INTO stats VALUES (i.table_name,val);
END LOOP;
END;
/
uj5u.com熱心網友回復：
您可以使用ALL_TAB_COLS字典表：
DECLARE
val integer;
BEGIN
FOR i IN (
SELECT table_name,
COUNT(*) AS num_cols
FROM all_tab_cols
WHERE owner = 'Schema'
GROUP BY table_name
)
LOOP
EXECUTE IMMEDIATE 'SELECT count(*) from ' || i.table_name INTO val;
INSERT INTO stats VALUES (i.table_name,val, i.num_cols);
END LOOP;
END;
/
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/371580.html