一、前言

有时一些客户的生产环境并没有图形界面，不方便使用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

三、其他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生成的结果集批量删除模式下的表。