Oracle/SQL Server/MySQL获取表及字段数据字典

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,'('||DATA_PRECISION||DECODE(DATA_SCALE,NULL,N                                                            ULL,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

LEFT JOIN sys.sysusers C

ON A.UID = C.UID AND C.NAME = 'dbo'

LEFT JOIN sys.sysobjects pk

ON A.ID = pk.parent_obj

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

LEFT JOIN sys.sysusers C

ON A.UID = C.UID AND C.NAME = 'dbo'

LEFT JOIN sys.sysobjects pk

ON A.ID = pk.parent_obj

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;