达梦数据库获取表结构、表注释、索引定义的方法

达梦数据库获取表结构、表注释、索引定义的方法

CREATE or REPLACE FUNCTION FN_GET_TABLE_DDL( DM_table_name   varchar2(200)) RETURN  VARCHAR2(10000) AS 
DM_SCH_name  VARCHAR(200); 
DM_TAB_name  VARCHAR(200);
DM_SQL  VARCHAR2(10000);
BEGIN
DM_table_name:=replace(DM_table_name,'"','');
if(instr(DM_table_name,'.')<2) then
RETURN '输入表名, 需带模式名, 如sysdba.Table001';
end if;
SELECT 
    REGEXP_SUBSTR(DM_table_name, '[^.]+', 1, 1) AS SCH,
    REGEXP_SUBSTR(DM_table_name, '[^.]+', 1, 2) AS TABLENAME into  DM_SCH_name, DM_TAB_name   from dual;

--查询表结构、注释、索引定义返回
select LISTAGG(CC,chr(13)) as TSQL into DM_SQL from (
select TABLEDEF(DM_SCH_name, DM_TAB_name ) as CC union all
select 'COMMENT ON TABLE '||SCHNAME||'.'||TVNAME||' IS '''||COMMENT$||''';' as CC  from SYSTABLECOMMENTS 
where TABLE_TYPE='TABLE' 
and SCHNAME=DM_SCH_name and TVNAME=DM_TAB_name  union all
select 'COMMENT ON COLUMN '||SCHNAME||'.'||TVNAME||'.'||COLNAME||' IS '''||COMMENT$||''';' as CC  from SYSCOLUMNCOMMENTS 
where TABLE_TYPE='TABLE' 
and SCHNAME=DM_SCH_name and TVNAME=DM_TAB_name  union all 
select indexdef(idx_obj.id, 1) as CC from sysobjects idx_obj, sysindexes idx where idx_obj.id = idx.id 
and idx_obj.pid = 
(SELECT id from sysobjects where name=DM_TAB_name  
and schid in(SELECT id from sysobjects where name=DM_SCH_name  
and type$='SCH') and subtype$='UTAB') 
and idx_obj.subtype$ = 'INDEX' and idx.flag & 0x01 = 0);

return DM_SQL;
END