• 世卫将“游戏障碍”列为新疾病 哪些症状可被确诊? 2019-05-07
  • 多部门三令五申禁网售彩票,世界杯竞猜APP上仍热卖 2019-05-07
  • 退牧还草 为藏羚羊迁徙让路 2019-05-05
  • 候选企业:苏宁控股集团有限公司 2019-05-05
  • 这是全国精气最为集中的地方 被称为中国丹田所在 ——凤凰网房产 2019-05-04
  • 状态差被郎平退货?丁霞归队仍是国内最好二传 2019-05-01
  • 天津市环境保护突出问题边督边改公开信息 2019-05-01
  • 董明珠代表:建立保护企业人才机制 遏制“恶意挖人”现象 2019-04-30
  • 7月1日起铁路将实施新列车运行图 北京再增12.5对复兴号 2019-04-24
  • 国际国内--山西频道--人民网 2019-04-23
  • 奥迪在华“三十已立” 坐拥天时,指向人和 2019-04-23
  • 中考期间 太原27个公共自行车服务点有人值守 2019-04-10
  • 有人被逼作弊,有人收获爱情,和高考有关的60个瞬间 2019-04-10
  • 深圳2018年将开行3趟援疆旅游扶贫专列 2019-04-05
  • 警惕套路贷 放贷是幌子 建议增设非法放贷罪 2019-03-25
  • 山东十一选五专家杀号 Chinaunix首页 | 论坛 | 山东十一选五专家杀号
    • 博客访问: 182786
    • 博文数量: 57
    • 博客积分: 2096
    • 博客等级: 大尉
    • 技术积分: 573
    • 用 户 组: 普通用户
    • 注册时间: 2008-09-29 16:09
    个人简介

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

    文章分类

    全部博文(57)

    文章存档

    2019年(3)

    2018年(1)

    2017年(3)

    2015年(4)

    2014年(7)

    2012年(2)

    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;


    阅读(3211) | 评论(0) | 转发(0) |
    给主人留下些什么吧!~~
    评论热议
    请登录后评论。

    登录 注册

  • 世卫将“游戏障碍”列为新疾病 哪些症状可被确诊? 2019-05-07
  • 多部门三令五申禁网售彩票,世界杯竞猜APP上仍热卖 2019-05-07
  • 退牧还草 为藏羚羊迁徙让路 2019-05-05
  • 候选企业:苏宁控股集团有限公司 2019-05-05
  • 这是全国精气最为集中的地方 被称为中国丹田所在 ——凤凰网房产 2019-05-04
  • 状态差被郎平退货?丁霞归队仍是国内最好二传 2019-05-01
  • 天津市环境保护突出问题边督边改公开信息 2019-05-01
  • 董明珠代表:建立保护企业人才机制 遏制“恶意挖人”现象 2019-04-30
  • 7月1日起铁路将实施新列车运行图 北京再增12.5对复兴号 2019-04-24
  • 国际国内--山西频道--人民网 2019-04-23
  • 奥迪在华“三十已立” 坐拥天时,指向人和 2019-04-23
  • 中考期间 太原27个公共自行车服务点有人值守 2019-04-10
  • 有人被逼作弊,有人收获爱情,和高考有关的60个瞬间 2019-04-10
  • 深圳2018年将开行3趟援疆旅游扶贫专列 2019-04-05
  • 警惕套路贷 放贷是幌子 建议增设非法放贷罪 2019-03-25
  • 足球任选9场胜负彩 大乐透走势图最新最全 黑龙江时时彩开奖网址 北京93亿彩票大奖 湖南幸运赛车奖金设置 北京单场奖金封顶 排列5杀号定胆 排列五走势图带连线图 新时时彩财付通 15133双色球中彩网预测 极速飞艇计划破解版 广东十一选5走势图爱彩乐 北京28官网开奖结果 pk10技巧345678定位 顶呱刮好彩头中奖图片 山东时时彩开奖图