达梦数据库操作

----【1】查看大小写是否敏感

（1）SELECT CASE_SENSITIVE();  （2）select SF_GET_CASE_SENSITIVE_FLAG()；

1表示大小写敏感
0表示大小写不敏感

-----【2】查看字符集

  （1）select SF_GET_UNICODE_FLAG();  （2）select UNICODE()；

0 代表数据库字符集编码为 GB18030
1 代表数据库字符集编码为 UTF-8
2 代表数据库字符集编码为韩文字符集 EUC-KR

--------【3】查看分页大小

   select SF_GET_PAGE_SIZE(); 

------【4】查看簇

   SELECT cast(SF_GET_EXTENT_SIZE() as varchar);

-------【5】长度是否以字符为单位:
select SF_GET_LENGTH_IN_CHAR()
1/Y -是；0/N-否

-----【6】是否需要开兼容性参数

SELECT SF_GET_PARA_VALUE(1, 'COMPATIBLE_MODE') FROM DUAL;
SELECT para_name, para_value FROM v$dm_ini WHERE para_name = 'COMPATIBLE_MODE';
0：不兼容。默认为 0
1：兼容 SQL92 标准
2：兼容 ORACLE
3：兼容 MS SQL SERVER
4：兼容 MYSQL
5：兼容 DM6
6：兼容 TERADATA
7：兼容 PG
设置兼容模式
使用 sp_set_para_value 函数设置
sp_set_para_value(0, 'COMPATIBLE_MODE', 4);

-------【7】查询DM数据库所有表的各种约束和索引

----【7.1】查询主键
SELECT a.OWNER as "模式名",a.TABLE_NAME as "表名",b.COLUMN_NAME as "列名", a.CONSTRAINT_NAME as "约束名"
from DBA_CONSTRAINTS a, ALL_CONS_COLUMNS b where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and CONSTRAINT_TYPE='P';

-----【7.2】查询外键
SELECT a.OWNER as "模式名",a.TABLE_NAME as "表名",b.COLUMN_NAME as "列名", a.CONSTRAINT_NAME as "约束名"
from DBA_CONSTRAINTS a, ALL_CONS_COLUMNS b where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and CONSTRAINT_TYPE='R';

----【7.3】查询唯一约束

SELECT a.OWNER as "模式名",a.TABLE_NAME as "表名",b.COLUMN_NAME as "列名", a.CONSTRAINT_NAME as "约束名"
from DBA_CONSTRAINTS a, ALL_CONS_COLUMNS b where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and CONSTRAINT_TYPE='U';

----【7.4】查询check约束

SELECT a.OWNER as "模式名",a.TABLE_NAME as "表名",b.COLUMN_NAME as "列名", a.CONSTRAINT_NAME as "约束名",SEARCH_CONDITION as "CHECK约束的条件"
from DBA_CONSTRAINTS a, ALL_CONS_COLUMNS b where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and CONSTRAINT_TYPE='C';

----【7.5】查询默认列
SELECT OWNER as "模式名",TABLE_NAME as "表名",COLUMN_NAME as "列名",DATA_TYPE as "列类型",DATA_DEFAULT as "默认值" FROM DBA_TAB_COLUMNS WHERE DATA_DEFAULT IS NOT NULL;

----【7.6】查询非空列
SELECT OWNER as "模式名",TABLE_NAME as "表名",COLUMN_NAME as "列名",DATA_TYPE as "列类型" FROM DBA_TAB_COLUMNS WHERE NULLABLE ='Y';

-----【7.7】查询索引
SELECT TABLE_OWNER as "模式名",TABLE_NAME as "表名",COLUMN_NAME as "列名",INDEX_NAME as "索引名" from DBA_IND_COLUMNS WHERE TABLE_OWNER='T1';