In my previous question Mark建议在显示数据库中每个表的计数时给出一个很好的答案。我想扩展此过程,并显示有关数据库中每一列的特定信息(TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE),而不是计数。
到目前为止,我有以下命令:
userId
但是现在当我运行它时:
--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION EXPORT_SCHEMAS()
RETURNS TABLE (P_TABSCHEMA VARCHAR(128),P_TABNAME VARCHAR(128),P_COLUM_NNAME VARCHAR(128),P_DATA_TYPE VARCHAR(128))
BEGIN
DeclARE L_STMT VARCHAR(256);
DeclARE L_ROWS VARCHAR(256);
FOR V1 AS
SELECT TABSCHEMA,TABNAME
FROM SYSCAT.TABLES
WHERE TYPE = 'T'
ORDER BY 1,2
DO
SET L_STMT = 'SET ? = (SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM SYSIBM.COLUMNS where TABLE_NAME = "'||V1.TABNAME||'" AND TABLE_SCHEMA = "'||V1.TABSCHEMA||'")';
PREPARE S FROM L_STMT;
EXECUTE S INTO L_ROWS;
PIPE(L_ROWS);
END FOR;
RETURN;
END@
SELECT * FROM TABLE(EXPORT_SCHEMAS())@
我遇到了错误:
db2 -ntd~ -f export_schemas.sql > dump.csv
能否请您帮助我,让我知道这里出了什么问题,我该如何解决?谢谢!