declare
_object_id decimal;
_schema_name varchar := '模式名';
_table_name varchar := '表名';
begin
select top 1 object_id into _object_id
from all_objects
where owner = _schema_name
and object_name = _table_name
and object_type in ('TABLE', 'VIEW');
select case when "IsPrimaryKey" = 1 then '¡Ì' else null end "PrimaryKey",
NAME "ColName",
COL.TYPE$ "TypeName",
NULLABLE$ "IsNullable",
DEFVAL "DefaultValue",
(select COMMENT$ from SYSCOLUMNCOMMENTS where SCHNAME=_schema_name and TVNAME=_table_name and COLNAME=COL.NAME and TABLE_TYPE='TABLE') "ColDesc" ,
case when seq = -1 then null else seq end "IndexNo" ,
sf_get_column_size(type$, cast (length$ as int), cast (scale as int)) "Prec",
scale "Scale"
from (select SF_GET_INDEX_KEY_SEQ(INDS.KEYNUM, INDS.KEYINFO, A.COLID) SEQ,
SF_COL_IS_IDX_KEY(INDS.KEYNUM, INDS.KEYINFO, A.COLID) "IsPrimaryKey",
A.name,
A.id,
A.colid,
CASE WHEN B.INFO1 IS NULL OR (((B.INFO1>>2) & 0x01)=0 AND ((B.INFO1>>3) & 0x01)=0) THEN A.TYPE$
WHEN (B.INFO2 & 0xFF) = 0 THEN 'NUMBER'
WHEN ((B.INFO1>>3) & 0x01)=1 THEN 'DATE'
ELSE 'FLOAT'
END AS TYPE$,
CASE WHEN B.INFO1 IS NULL OR ((B.INFO1>>2) & 0x01)=0 THEN A.SCALE
WHEN (B.INFO2 & 0xFF) = 0 THEN 0
ELSE 129
END AS SCALE,
CASE WHEN B.INFO1 IS NULL OR ((B.INFO1>>2) & 0x01)=0 THEN A.LENGTH$
ELSE (B.INFO2 & 0xFF)
END AS LENGTH$,
A.NULLABLE$,
A.DEFVAL,
A.INFO1,
A.INFO2
from SYSCOLUMNS A
left join SYSCOLINFOS B
on A.ID=B.ID and A.COLID=B.COLID
left join SYSCONS CONS
on CONS.TABLEID = A.ID and CONS.TYPE$ = 'P' and CONS.VALID = 'Y'
left join SYSINDEXES INDS
on INDS.ID = CONS.INDEXID and SF_COL_IS_IDX_KEY(INDS.KEYNUM, INDS.KEYINFO, A.COLID) = 1
where A.ID =_object_id) COL;
end;
在达梦管理工具中执行DESC来获取表结构信息,实际是转换成了下面匿名块来插叙相关系统视图获取信息,不是数据库或驱动本身的能力,如需要,可参考如下内容处理。