如何在db2数据库中打印每个列/表的详细信息（表名，列名，数据类型）？ [英] how can I print details (table name, column name, data type) of each column/table in my db2 database?

问题描述

在我之前的问题中 Mark为显示数据库中每个表的计数提出了一个很好的答案。我想扩展此过程，并-而不是计数-显示有关数据库中每列的特定信息（TABLE_SCHEMA，TABLE_NAME，COLUMN_NAME，DATA_TYPE）。

In my previous question Mark suggested a good answer for displaying count on every table in my database. I would like to expand this procedure and - instead of counts - display the specific info (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE) about each column in the database.

到目前为止，我有以下命令：

I so far have the following command:

--#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())@

但是现在当我运行它时：

but now when I run it:

db2 -ntd~ -f export_schemas.sql > dump.csv

我遇到了错误：

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL20019N  The result type returned from the function body cannot be assigned
to the data type defined in the RETURNS clause.  LINE NUMBER=17.
SQLSTATE=42866

请您帮我一下，让我知道这是怎么回事。我该如何解决？谢谢！

Could you please help me and let me know what is wrong here and how could I fix it? Thanks!

推荐答案

如果您将Db2用于LUW，则不应使用 SYSIBM 模式。请使用 SYSCAT 。

If you use Db2 for LUW, then you shouldn't use SYSIBM schema in your queries on the system catalog. Use SYSCAT instead.

您无需使用任何功能即可在此处获得想要的东西。而是使用以下查询：

You don't have to use any functions to get what you want here. Use the following query instead:

SELECT TABSCHEMA, TABNAME, COLNAME, TYPENAME
FROM SYSCAT.COLUMNS
ORDER BY TABSCHEMA, TABNAME, COLNO;

至于您的日常活动。文本中存在许多错误。

As for your routine. There is a number of errors in the text.

1）如果要使用 SET 语句分配多个值，则必须使用相应数量的参数标记在语句中：

1) if you want to assign multiple values with SET statement, you must use the corresponding number of parameter markers in the statement:

SET (?, ..., ?) = (SELECT COL1, ..., COLn FROM ...);
PREPARE S FROM L_STMT;
EXECUTE S INTO L_V1, ..., L_Vn;

2）返回表（...）和 PIPE（...）必须具有相同的列数

2) RETURNS TABLE (...) and PIPE(...) must have the same number of columns

这篇关于如何在db2数据库中打印每个列/表的详细信息（表名，列名，数据类型）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！