Oracle数据库
测试平台 - Database 11gR2/12cR2
--数据字典表级信息
SELECT USER,T.TABLE_NAME,TC.COMMENTS,DECODE(C.CONSTRAINT_NAME,NULL,'N','Y') PK,
I.INDEX_COLS UNIQUE_INDEXES
FROM USER_TABLES T,
USER_TAB_COMMENTS TC,
USER_CONSTRAINTS C,
(SELECT TABLE_NAME, UNIQUENESS, LISTAGG(INDEX_COLS, ';') WITHIN GROUP (ORDER BY INDEX_NAME ) INDEX_COLS
FROM
(SELECT I.TABLE_NAME, I.UNIQUENESS, I.INDEX_NAME, I.INDEX_NAME||'('||
(LISTAGG(IC.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY IC.COLUMN_POSITION))||')' I NDEX_COLS
FROM USER_INDEXES I,
USER_IND_COLUMNS IC
WHERE I.INDEX_NAME = IC.INDEX_NAME
AND I.UNIQUENESS = 'UNIQUE'
GROUP BY I.TABLE_NAME, I.INDEX_NAME, I.UNIQUENESS
)
GROUP BY TABLE_NAME, UNIQUENESS
) I
WHERE T.TABLE_NAME = TC.TABLE_NAME(+)
AND T.TABLE_NAME = C.TABLE_NAME(+)
AND C.CONSTRAINT_TYPE(+) = 'P'
AND T.TABLE_NAME = I.TABLE_NAME(+)
ORDER BY TABLE_NAME
;
--字段级信息
SELECT USER SCHEMA_NAME, T.TABLE_NAME, C.COMMENTS, TC.COLUMN_ID, TC.COLUMN_NAME, CC.COMMENTS,
CASE
WHEN TC.DATA_TYPE IN ('CHAR','VARCHAR2','NVARCHAR2') THEN
TC.DATA_TYPE||'('||TC.CHAR_LENGTH||')'
WHEN TC.DATA_TYPE IN ('NUMBER') THEN
TC.DATA_TYPE||DECODE(DATA_PRECISION,NULL,NULL,'('||DATA_PRECISION||
DECODE(DATA_SCALE,NULL,NULL,0,NULL,','||DATA_SCALE)||')')
ELSE
TC.DATA_TYPE
END DATA_TYPE,
DECODE(PK_COL.COLUMN_NAME,NULL,'N','Y') PK_COLS, TC.NULLABLE
FROM USER_TABLES T,
USER_TAB_COMMENTS C,
USER_TAB_COLS TC,
USER_COL_COMMENTS CC,
(SELECT CON.TABLE_NAME, CONC.COLUMN_NAME
FROM USER_CONSTRAINTS CON,
USER_CONS_COLUMNS CONC
WHERE CON.CONSTRAINT_TYPE = 'P'
AND CON.CONSTRAINT_NAME = CONC.CONSTRAINT_NAME
) PK_COL
WHERE T.TABLE_NAME = TC.TABLE_NAME
AND T.TABLE_NAME = C.TABLE_NAME(+)
AND TC.TABLE_NAME = CC.TABLE_NAME(+)
AND TC.COLUMN_NAME = CC.COLUMN_NAME(+)
AND TC.TABLE_NAME = PK_COL.TABLE_NAME(+)
AND TC.COLUMN_NAME = PK_COL.COLUMN_NAME(+)
ORDER BY 2,4;
SQL Server
测试平台 - SQL Server 2005
--获取表字典信息
SELECT C.NAME SchemaName,
A.NAME TableName,
ISNULL(EP1.[VALUE],'') TableComment,
CASE WHEN pk.NAME IS NOT NULL THEN 'Y'
ELSE 'N'
END AS HavePK
FROM sys.sysobjects A
JOIN sys.sysusers C
ON A.UID = C.UID AND C.NAME = 'dbo' AND A.xtype = 'U'
LEFT JOIN sys.sysobjects pk
ON A.ID = pk.parent_obj AND pk.xtype = 'PK'
LEFT JOIN SYS.extended_properties EP1
ON A.ID = EP1.MAJOR_ID AND EP1.MINOR_ID = 0
ORDER BY A.NAME
;
--获取表字典信息加唯一索引
WITH pk_uq as
(
SELECT pk.parent_obj,
pk.NAME,
keys.colid,
col.name colname,
pk.xtype
FROM sys.sysobjects pk
JOIN sys.sysindexes ind
ON pk.name = ind.name AND pk.xtype in ('PK','UQ')
JOIN sys.sysindexkeys keys
ON ind.indid = keys.indid AND ind.id = keys.id
JOIN sys.syscolumns col
ON keys.colid = col.colid AND col.id = pk.parent_obj
),
pk_uq_inds as
(
SELECT parent_obj, name+'('+STUFF(
( SELECT ','+colname
FROM pk_uq b
WHERE b.parent_obj = a.parent_obj and a.name = b.name
FOR XML PATH('')),1 ,1, '')+')' UQ_IND
from pk_uq a
group by parent_obj,name
),
uq_group as
(
SELECT parent_obj id, STUFF(
( SELECT ';'+ UQ_IND
FROM pk_uq_inds c
WHERE c.parent_obj = d.parent_obj
FOR XML PATH('')),1 ,1, '') UQ_GROUP
from pk_uq_inds d
group by parent_obj
)
SELECT C.NAME SchemaName,
A.NAME TableName,
ISNULL(EP1.[VALUE],'') TableComment,
CASE WHEN pk.NAME IS NOT NULL THEN 'Y'
ELSE 'N'
END AS ColumnType,
uq.UQ_GROUP
FROM sys.sysobjects A
JOIN sys.sysusers C
ON A.UID = C.UID AND C.NAME = 'dbo' AND A.xtype = 'U'
LEFT JOIN sys.sysobjects pk
ON A.ID = pk.parent_obj AND pk.xtype = 'PK'
LEFT JOIN SYS.extended_properties EP1
ON A.ID = EP1.MAJOR_ID AND EP1.MINOR_ID = 0
LEFT JOIN uq_group uq
ON a.id = uq.id
ORDER BY A.NAME
;
--获取字段信息
SELECT C.NAME SchemaName,
A.NAME TableName,
ISNULL(EP1.[VALUE],'') TableComment,
B.COLORDER ColumnId,
B.NAME ColumnName,
ISNULL(EP2.[VALUE],'') ColumnComment,
CASE
WHEN tp.NAME IN ('char','varchar','nvarchar') THEN tp.NAME+'('+CAST(B.prec AS VARCHAR)+')'
WHEN tp.NAME IN ('numeric','decimal') THEN tp.NAME+'('+CAST(B.xprec AS VARCHAR)+','+CAST(B.xscale AS VARCHAR)+')'
ELSE tp.NAME
END AS ColumnType,
CASE
WHEN pk.NAME IS NOT NULL THEN 'Y'
ELSE 'N'
END AS PkColumn,
CASE WHEN B.isnullable = 0 THEN 'Y'
ELSE 'N'
END AS Nullable
FROM sys.sysobjects A
JOIN sys.syscolumns B
ON a.id=b.id AND A.xtype = 'U'
LEFT JOIN sys.sysusers C
ON A.UID = C.UID AND C.NAME = 'dbo'
LEFT JOIN sys.systypes tp
ON B.xtype = tp.xusertype
LEFT JOIN (SELECT pk.parent_obj,
pk.NAME,
keys.colid,
pk.xtype
FROM sys.sysobjects pk
JOIN sys.sysindexes ind
ON pk.name = ind.name AND pk.xtype = 'PK'
JOIN sys.sysindexkeys keys
ON ind.indid = keys.indid AND ind.id = keys.id
) pk
ON B.ID = pk.parent_obj AND B.colid = pk.colid
LEFT JOIN SYS.extended_properties EP1
ON A.ID = EP1.MAJOR_ID AND EP1.MINOR_ID = 0
LEFT JOIN sys.extended_properties EP2
ON B.ID = EP2.MAJOR_ID AND B.colid = EP2.MINOR_ID
ORDER BY A.NAME, B.COLID
;
MySQL
测试平台 - MySQL 5.7
--获取表字典信息
SELECT T.TABLE_SCHEMA,
T.TABLE_NAME,
T.TABLE_COMMENT,
CASE
WHEN C.CONSTRAINT_NAME IS NULL THEN 'N'
ELSE
'Y'
END AS PK_EXISTS,
UNI.TAB_UNI
FROM TABLES T
LEFT JOIN TABLE_CONSTRAINTS C
ON T.TABLE_NAME = C.TABLE_NAME AND C.CONSTRAINT_TYPE = 'PRIMARY KEY'
LEFT JOIN (SELECT d.CONSTRAINT_SCHEMA, d.TABLE_NAME,
group_concat(d.UNI_COL separator ';') TAB_UNI
FROM (
SELECT C2.CONSTRAINT_SCHEMA, C2.TABLE_NAME, concat(C2.CONSTRAINT_NAME,'(',
group_concat(KC.COLUMN_NAME order by KC.ORDINAL_POSITION),')') UNI_COL
FROM TABLE_CONSTRAINTS C2
JOIN KEY_COLUMN_USAGE KC
ON C2.CONSTRAINT_NAME = KC.CONSTRAINT_NAME AND C2.CONSTRAINT_TYPE IN ('PRIMARY KEY','UNIQUE')
AND C2.TABLE_NAME = KC.TABLE_NAME
AND C2.CONSTRAINT_SCHEMA = KC.CONSTRAINT_SCHEMA
GROUP BY C2.CONSTRAINT_SCHEMA, C2.TABLE_NAME, C2.CONSTRAINT_NAME
) d
GROUP BY d.CONSTRAINT_SCHEMA, d.TABLE_NAME
) UNI
ON T.TABLE_NAME = UNI.TABLE_NAME
AND C.CONSTRAINT_SCHEMA = UNI.CONSTRAINT_SCHEMA
WHERE T.TABLE_SCHEMA = 'xxxx'
;
--获取表列字典信息
SELECT
C.TABLE_SCHEMA,
C.TABLE_NAME,
T.TABLE_COMMENT,
C.ORDINAL_POSITION,
C.COLUMN_NAME,
C.COLUMN_COMMENT,
C.COLUMN_TYPE,
CASE
WHEN PK.COLUMN_NAME IS NULL THEN 'N'
ELSE
'Y'
END AS PK_COL,
SUBSTR(C.IS_NULLABLE,1,1) IS_NULL
FROM COLUMNS C
JOIN TABLES T
ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
LEFT JOIN (SELECT C2.CONSTRAINT_SCHEMA, C2.TABLE_NAME, KC.COLUMN_NAME
FROM TABLE_CONSTRAINTS C2
JOIN KEY_COLUMN_USAGE KC
ON C2.CONSTRAINT_NAME = KC.CONSTRAINT_NAME AND C2.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND C2.TABLE_NAME = KC.TABLE_NAME -- AND C2.TABLE_SCHEMA = 'dblife'
AND C2.CONSTRAINT_SCHEMA = KC.CONSTRAINT_SCHEMA
) PK
ON C.TABLE_SCHEMA = PK.CONSTRAINT_SCHEMA
AND C.TABLE_NAME = PK.TABLE_NAME
AND C.COLUMN_NAME = PK.COLUMN_NAME
WHERE T.TABLE_SCHEMA = 'xxxx'
ORDER BY T.TABLE_NAME,C.ORDINAL_POSITION;
