Chinaunix首页 | 论坛 | 博客
  • 博客访问: 387248
  • 博文数量: 58
  • 博客积分: 2096
  • 博客等级: 大尉
  • 技术积分: 608
  • 用 户 组: 普通用户
  • 注册时间: 2008-09-29 16:09
个人简介

专注于数据库技术研究和实践,目前就职于互联网金融企业,提供Oracle数据库技术支持和维护。 联系电话:18616803656

文章分类

全部博文(58)

文章存档

2020年(1)

2019年(4)

2018年(1)

2017年(3)

2015年(4)

2014年(7)

2012年(1)

2011年(27)

2010年(8)

2009年(2)

我的朋友

分类: Oracle

2019-03-06 12:40:00

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;


阅读(7838) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~