本文于 401 天前发布，最后更新于 2 天前
一、前言
有时一些客户的生产环境并没有图形界面，不方便使用DM管理工具，那么我们就需要使用SQL方式查看表定义，从disql直接查看表定义也非常方便我们平时的操作。
二、查看表定义
方法一：DBMS_METADATA.GET_DDL
FUNCTION GET_DDL(
OBJECT_TYPE IN VARCHAR(30),
NAME IN VARCHAR(128),
SCHNAME IN VARCHAR(128) DEFAULT NULL
) RETURN CLOB- OBJECT_TYPE
- 对象类型。包括表、视图、物化视图、索引、全文索引、存储过程、函数、包、目录等，详情请见 OPEN 参数详解。其中，OBJECT_TYPE 只能为大写。
- NAME
- 对象名称，区分大小写。
- SCHEMA
- 模式，默认是当前用户模式。
返回值
以 DDL 返回对象元数据中的 DDL 语句。
格式：
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TABLENAME','SCHEMA');例如：
--查看表定义
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','STUDENT','TEST');
--查看索引定义
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','IDX_T1_OBJECT_ID_DESC','SJL');方法二：SP_TABLEDEF
格式：
SP_TABLEDEF('SCHEMA','TABLENAME');示例：
SP_TABLEDEF('TEST','STUDENT');方法三：DESC
格式：
DESC SCHEMA.TABLENAME;示例：
SQL> DESC TEST.STUDENT;
行号 NAME TYPE$ NULLABLE
---------- ----- ----------- --------
1 ID INTEGER N
2 NAME VARCHAR(20) Y
3 SEX INTEGER Y
4 SCORE INTEGER Y
已用时间: 44.623(毫秒). 执行号:80600.
SQL> 注意：如果查询时报错：字符串截断。可以在执行前配置参数解决。例如：
SET LONG 10000
另外，DESC方式目前管理工具已经支持，代码中不支持，代码中可用下方的匿名块代替执行
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;方法四：自定义存储过程
CREATE OR REPLACE PROCEDURE SP_SHOW_TABLE_DEFINE ( SCH_NAME VARCHAR2 (500),
TAB_NAME VARCHAR2 (1000) )
as
V_INX_ID VARCHAR2(200);
/*索引编号传参*/
V_SQL VARCHAR2(1000);
/*索引定义*/
V_SQL_ALL VARCHAR2(1000);
/*打印的结果*/
v_sql1 varchar2(8000);
/*表结构+主键+外键+约束*/
v_sql2 varchar2(8000);
/*表及字段注释*/
V_CUR
CURSOR;
BEGIN
v_sql1 = TABLEDEF(SCH_NAME, TAB_NAME);
/*表结构+主键+外键+约束*/
/*表及字段注释*/
select (SELECT LISTAGG(COMMENTS_1, char(13))
from ( SELECT 'COMMENT ON TABLE ' || T.SCHNAME || '.' || T.TVNAME || ' IS ''' || REPLACE(T.COMMENT$, '''', '''''') || ''';' AS COMMENTS_1
FROM SYSTABLECOMMENTS T
WHERE SCHNAME = SCH_NAME
AND TVNAME = TAB_NAME
UNION
SELECT 'COMMENT ON COLUMN ' || C.SCHNAME || '.' || C.TVNAME || '.' || C.COLNAME || ' IS ''' || REPLACE(C.COMMENT$, '''', '''''') || ''';'
FROM SYSCOLUMNCOMMENTS C
WHERE SCHNAME = SCH_NAME
AND TVNAME = TAB_NAME ))
INTO v_sql2
FROM DUAL;
/*表及字段注释*/
/*获取索引编号传参*/
OPEN V_CUR FOR
SELECT T2.OBJECT_ID
FROM ALL_INDEXES T1,
ALL_OBJECTS T2
WHERE T1.OWNER = T2.OWNER
AND T1.INDEX_NAME = T2.OBJECT_NAME
AND T2.GENERATED = 'N'
AND T1.OWNER = SCH_NAME
AND T1.TABLE_NAME = TAB_NAME;
/*获取索引编号传参*/
LOOP
/*索引编号传参*/
FETCH V_CUR INTO V_INX_ID;
EXIT WHEN V_CUR%NOTFOUND;
--PRINT V_INX_ID;
--DBMS_OUTPUT.PUT_LINE(V_INX_ID);
/*索引定义*/
SELECT ((SELECT *
FROM (SELECT INDEXDEF(V_INX_ID, 1) AS INDEX_1)
WHERE INDEX_1 <> '索引不存在'
and INDEX_1 <> '禁止查看系统定义的索引信息' ))
INTO V_SQL
FROM DUAL ;
/*索引定义*/
/*打印结果*/
V_SQL_ALL = V_SQL_ALL || '' || char(13) || V_SQL;
/*打印结果V_SQL*/
END LOOP;
/*打印结果*/
V_SQL_ALL = v_sql1 || '' || V_SQL_ALL || '' || char(13) || v_sql2;
/*或者查询结果v_sql1+v_sql2*/
select V_SQL_ALL;
END;
/调用，查看表定义
call "SP_SHOW_TABLE_DEFINE"('SCHNAME', 'TABNAME');三、其他SQL
（1）导出所有用户下表定义
SELECT DISTINCT
'SELECT DBMS_METADATA.GET_DDL(' || CHR(39) || 'TABLE' || CHR(39) || ',' || CHR(39) || TABLE_NAME || CHR(39) || ',' || CHR(39) || OWNER || CHR(39) || ') FROM DUAL UNION'
FROM
DBA_TABLES
WHERE
DBA_TABLES.OWNER NOT IN (
'SYS',
'SYSAUDITOR',
'SYSSSO',
'CTISYS',
'SYSDBA',
'SYSJOB'
);使用上方SQL生成的结果集批量查询表定义，以导出所有的表定义。
注：若报错 UNION 无法比较的数据类型问题，是因为大字段类型默认不让比较，需要调整参数 ENABLE_BLOB_CMP_FLAG。
SP_SET_PARA_VALUE(1, ‘ENABLE_BLOB_CMP_FLAG’, 1);
–或者
ALTER SYSTEM SET ‘ENABLE_BLOB_CMP_FLAG’ = 1 BOTH;
便能解决。
另外，新版本的管理工具添加了导出表定义的功能：
管理工具-manager——选中某一模式名——右键——查看对象关系图——菜单栏最后会有一个导出按钮，就可将表结构导出到 Excel。
（2）删除用户下的表
SELECT 'DROP TABLE '||TABLE_NAME||';' FROM DBA_TABLES WHERE OWNER = 'SCHEMA';使用上述SQL生成的结果集批量删除模式下的表。
