一条SQL生成数据字典
有个字典表并定期维护,对DBA和开发很重要,终于把他们整合在一起了,看有没问题?
一条SQL生成数据字典,包含所有OPEN用户、表名、字段名、字段序号、字段属性、默认值、是否非空、字段意思、主键标识、外键标识、主键表名、主键字段名、外键表名、外键字段名、外键名、外键标识、外键表用户
其中联合外键会出现字段列出现多行,TIMESTAMP(6)(,6)需要手工处理
CREATE TABLE DICT_ZGY_20180814 AS
SELECT S.OWNER,
S.TABLE_NAME,
S.COLUMN_ID,
S.COLUMN_NAME,
S.COLTYPE,
TO_LOB(S.DEFAULTVAL) AS DEFAULTVAL,
S.NULLYN,
S.COMMENTSS,
CASE
WHEN PK.COLUMN_POSITION > 0 THEN
'√'
ELSE
''
END AS PKYN,
CASE
WHEN FK.CONSTRAINT_TYPE = 'R' THEN
'√'
ELSE
''
END AS FKYN,
FK.*
FROM (SELECT A.OWNER,
A.TABLE_NAME,
A.COLUMN_NAME,
A.COLUMN_ID,
DECODE(A.CHAR_LENGTH,
0,
DECODE(A.DATA_SCALE,
NULL,
A.DATA_TYPE,
A.DATA_TYPE || '(' || A.DATA_PRECISION || ',' ||
A.DATA_SCALE || ')'),
A.DATA_TYPE || '(' || A.CHAR_LENGTH || ')') AS COLTYPE,
A.DATA_DEFAULT AS DEFAULTVAL,
CASE
WHEN A.NULLABLE = 'Y' THEN
'√'
ELSE
''
END AS NULLYN,
B.COMMENTS AS COMMENTSS
FROM SYS.ALL_TAB_COLUMNS A, SYS.DBA_COL_COMMENTS B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME) S,
(SELECT AA.INDEX_OWNER,
AA.TABLE_NAME,
AA.COLUMN_POSITION,
AA.COLUMN_NAME
FROM ALL_IND_COLUMNS AA, ALL_CONSTRAINTS BB
WHERE BB.CONSTRAINT_TYPE = 'P'
AND AA.TABLE_NAME = BB.TABLE_NAME
AND AA.INDEX_NAME = BB.CONSTRAINT_NAME
AND AA.INDEX_OWNER = BB.OWNER) PK,
(SELECT BB2.TABLE_NAME PTABLE_NAME,
BB2.COLUMN_NAME PCOLUMN_NAME,
AA1.TABLE_NAME FTABLE_NAME,
AA1.COLUMN_NAME FCOLUMN_NAME,
AA1.CONSTRAINT_NAME,
AA1.CONSTRAINT_TYPE,
AA1.OWNER FOWNER
FROM (SELECT A1.CONSTRAINT_NAME,
B1.TABLE_NAME,
B1.COLUMN_NAME,
A1.R_CONSTRAINT_NAME,
A1.CONSTRAINT_TYPE,
A1.OWNER
FROM ALL_CONSTRAINTS A1, ALL_CONS_COLUMNS B1
WHERE A1.CONSTRAINT_TYPE = 'R'
AND A1.CONSTRAINT_NAME = B1.CONSTRAINT_NAME
AND A1.OWNER = B1.OWNER) AA1,
(SELECT DISTINCT A2.R_CONSTRAINT_NAME,
B2.TABLE_NAME,
B2.COLUMN_NAME
FROM ALL_CONSTRAINTS A2, ALL_CONS_COLUMNS B2
WHERE A2.CONSTRAINT_TYPE = 'R'
AND A2.R_CONSTRAINT_NAME = B2.CONSTRAINT_NAME) BB2
WHERE AA1.R_CONSTRAINT_NAME = BB2.R_CONSTRAINT_NAME) FK,
DBA_USERS U
WHERE S.OWNER = PK.INDEX_OWNER(+)
AND S.TABLE_NAME = PK.TABLE_NAME(+)
AND S.COLUMN_NAME = PK.COLUMN_NAME(+)
AND S.OWNER = FK.FOWNER(+)
AND S.TABLE_NAME = FK.FTABLE_NAME(+)
AND S.COLUMN_NAME = FK.FCOLUMN_NAME(+)
AND S.OWNER = U.USERNAME(+)
AND U.ACCOUNT_STATUS = 'OPEN'
AND U.USERNAME NOT IN ('SYS', 'SYSTEM')
ORDER BY S.OWNER, S.TABLE_NAME, S.COLUMN_ID;
查询索引字段,并导出字典表成xlsx格式
SELECT T.TABLE_OWNER,
T.TABLE_NAME,
T.INDEX_OWNER,
T.INDEX_NAME,
T.COLUMN_NAME,
T.COLUMN_POSITION
FROM ALL_IND_COLUMNS T, DBA_USERS U
WHERE T.INDEX_OWNER = U.USERNAME
AND U.ACCOUNT_STATUS = 'OPEN'
AND U.USERNAME NOT IN ('SYS', 'SYSTEM')
ORDER BY T.TABLE_OWNER, T.TABLE_NAME, T.INDEX_NAME, T.COLUMN_POSITION;
推荐阅读
-
通过T_sql语句向其中一次填入一条数据或一次填入多条数据的方式填充数据
-
SQL语句分组获取记录的第一条数据的方法
-
获取MSSQL数据字典的SQL语句
-
SQL Server把某个字段的数据用一条语句转换成字符串
-
php将mysql数据库整库导出生成sql文件的具体实现
-
利用ROW_NUMBER() OVER函数给SQL数据库中每一条记录分配行号的方法
-
SQL Server 关于 Table 字典数据的查询SQL
-
对python插入数据库和生成插入sql的示例讲解
-
删除一个表中的重复数据同时保留第一次插入那一条以及sql优化
-
EFCore 通过实体Model生成创建SQL Server数据库表脚本