达梦数据库 - 获取表结构的SQL脚本

--当前用户Schema的表结构
SELECT * FROM USER_TAB_COLS WHERE TABLE_NAME='_Demo_Customer';
SELECT * FROM USER_COL_COMMENTS  WHERE TABLE_NAME='_Demo_Customer';
SELECT * FROM USER_TAB_COMMENTS  WHERE TABLE_NAME='_Demo_Customer';
SELECT * FROM USER_CONSTRAINTS  WHERE TABLE_NAME='_Demo_Customer';
SELECT * FROM USER_CONS_COLUMNS  WHERE TABLE_NAME='_Demo_Customer';

--所有用户Schema的表结构
SELECT * FROM ALL_TAB_COLS WHERE OWNER='CSFrameworkV6_Normal' AND TABLE_NAME='_Demo_Customer';
SELECT * FROM ALL_COL_COMMENTS  WHERE OWNER='CSFrameworkV6_Normal' AND TABLE_NAME='_Demo_Customer';
SELECT * FROM ALL_TAB_COMMENTS  WHERE OWNER='CSFrameworkV6_Normal' AND TABLE_NAME='_Demo_Customer';
SELECT * FROM ALL_CONSTRAINTS  WHERE OWNER='CSFrameworkV6_Normal' AND TABLE_NAME='_Demo_Customer';
SELECT * FROM ALL_CONS_COLUMNS  WHERE OWNER='CSFrameworkV6_Normal' AND TABLE_NAME='_Demo_Customer';

SELECT    
	t1.Table_Name AS "表名称",      
	t3.Comments AS "表说明",    
	t1.column_id AS "顺序",   
	t1.Column_Name AS "字段名称",    
	t1.Data_Type AS "数据类型",    
	t1.Data_Length AS "长度",    
	t1.DATA_PRECISION AS "精度",   
	t1.DATA_SCALE AS "小数位",   
	t1.Nullable AS "是否为空",    
	t2.Comments AS "字段说明",    
	t1.Data_Default "默认值",    
CASE WHEN tt.COLUMN_NAME IS NULL THEN '' ELSE 'Y' END AS "主键"  
FROM ALL_TAB_COLS t1  
LEFT JOIN ALL_COL_COMMENTS t2 on t1.OWNER=t2.OWNER and t1.TABLE_NAME=t2.TABLE_NAME and t1.COLUMN_NAME=t2.COLUMN_NAME  
LEFT JOIN ALL_TAB_COMMENTS t3 on t1.OWNER=t3.OWNER and t1.TABLE_NAME=t3.TABLE_NAME  
LEFT JOIN (SELECT cols.OWNER, cols.TABLE_NAME, cols.COLUMN_NAME FROM ALL_CONSTRAINTS cons, ALL_CONS_COLUMNS cols WHERE cons.OWNER = cols.OWNER AND cons.CONSTRAINT_TYPE = 'P' AND cons.CONSTRAINT_NAME = cols.CONSTRAINT_NAME) tt on t1.OWNER=tt.OWNER and t1.TABLE_NAME=tt.TABLE_NAME and t1.COLUMN_NAME=tt.COLUMN_NAME  
WHERE t1.OWNER='CSFrameworkV6_Normal' AND t1.TABLE_NAME='_Demo_Customer' 
AND NOT EXISTS ( SELECT t4.OBJECT_NAME FROM USER_OBJECTS t4 WHERE t4.OBJECT_TYPE='TABLE' AND t4.TEMPORARY='Y' AND t4.OBJECT_NAME=t1.TABLE_NAME )  
ORDER BY t1.TABLE_NAME, t1.COLUMN_ID ;