达梦数据库如何查看索引和表的DDL

查看某张表上的所有索引

 select * from all_indexes  where owner='NTSICR' and table_name='DATA_SMALL_POOL';

1）查看索引DDL，先查看索引的objectID,再查询该objectid的DDL
SQL> select * from dba_objects where object_name='IDX_INTERFACE_DATA_SYNC_FLAG';

LINEID     OWNER  OBJECT_NAME                  SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED                    LAST_DDL_TIME
---------- ------ ---------------------------- -------------- --------- -------------- ----------- -------------------------- --------------------------
           TIMESTAMP                  STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
           -------------------------- ------ --------- --------- --------- --------- ------------
1          NTSICR IDX_INTERFACE_DATA_SYNC_FLAG NULL           33555859  NULL           INDEX       2023-11-20 17:04:18.510814 2023-11-20 17:04:18.000000
           2023-11-20 17:04:18.000000 VALID  N         N         NULL      NULL      NULL

used time: 19.661(ms). Execute id is 421834100.
SQL> select * from dba_objects where object_name='INTERFACE_DATA_RECEIVE';

LINEID     OWNER  OBJECT_NAME            SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED                    LAST_DDL_TIME
---------- ------ ---------------------- -------------- --------- -------------- ----------- -------------------------- --------------------------
           TIMESTAMP                  STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
           -------------------------- ------ --------- --------- --------- --------- ------------
1          NTSICR INTERFACE_DATA_RECEIVE NULL           1059      NULL           TABLE       2023-07-04 12:22:51.748000 2023-11-20 17:04:18.000000
           2023-11-20 17:04:18.000000 VALID  N         N         NULL      NULL      NULL

used time: 14.517(ms). Execute id is 421834101.
SQL> select indexdef(33555859,1);

LINEID     indexdef(33555859,1)
---------- ----------------------------------------------------------------------------------------------------------------------------------------------
1          CREATE  INDEX "IDX_INTERFACE_DATA_SYNC_FLAG" ON "NTSICR"."INTERFACE_DATA_RECEIVE"("SYNC_FLAG" ASC) STORAGE(ON "INDEXTBS_NTSICR", CLUSTERBTR) ;

used time: 1.789(ms). Execute id is 421834102.

2)查看表DDL
SQL> call sp_tabledef('NTSICR','INTERFACE_DATA_RECEIVE');

LINEID     COLUMN_VALUE
---------- ------------------------------------------------------------------------------------------------------------------------------------------------
1          CREATE TABLE "NTSICR"."INTERFACE_DATA_RECEIVE"  (  "ID" BIGINT IDENTITY(1, 1) NOT NULL,  "BUSINESS_TYPE" VARCHAR(20) NOT NULL,  "CAMPAIGN_ID" VARCHAR(20) NOT NULL,  "BATCH_ID" VARCHAR(100),  "SALEMANAGECOM" VARCHAR(2),  "CITY_ID" VARCHAR(10),  "MPHONE" VARCHAR(30) NOT NULL,  "CUST_ID" VARCHAR2(50),  "SHOULD_RECOVER_TIME" TIMESTAMP(6),  "SYNC_FLAG" INT DEFAULT 0,  "CREATE_TIME" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,  "LASTUPDATETIME" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,  "OTHERS" CLOB,  CONST
2          RAINT "PK_INTERFACE_DATA_RECEIVE" NOT CLUSTER PRIMARY KEY("ID")) STORAGE(ON "DATATBS_NTSICR", CLUSTERBTR) ;

used time: 1.771(ms). Execute id is 421834103.
SQL>