2016-1221

数据库字典生成SQL语句

作者: momy 分类: 数据库 0 Comment »
摘要:数据库字典生成SQL语句

SQL SERVER:

SELECT
  CASE
    WHEN A.COLORDER=1
    THEN D.NAME
    ELSE ''
  END AS 表名,
  CASE
    WHEN A.COLORDER=1
    THEN ISNULL(F.VALUE,'')
    ELSE ''
  END        AS 表说明,
  A.COLORDER AS 字段序号,
  A.NAME     AS 字段名,
  CASE
    WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1
    THEN '√'
    ELSE ''
  END AS 标识,
  CASE
    WHEN EXISTS
      (
        SELECT
          1
        FROM
          sysobjects
        WHERE
          xtype   ='PK'
        AND name IN
          (
            SELECT
              name
            FROM
              sysindexes
            WHERE
              indid IN
              (
                SELECT
                  INDID
                FROM
                  SYSINDEXKEYS
                WHERE
                  ID     = A.ID
                AND COLID=A.COLID
              )
          )
      )
    THEN '√'
    ELSE ''
  END                                           AS 主键,
  B.NAME                                        AS 类型,
  A.LENGTH                                      AS 占用字节数,
  COLUMNPROPERTY(A.ID,A.NAME,'PRECISION')       AS 长度,
  ISNULL(COLUMNPROPERTY(A.ID,A.NAME,'Scale'),0) AS 小数位数,
  CASE
    WHEN A.ISNULLABLE=1
    THEN '√'
    ELSE ''
  END                  AS 允许空,
  ISNULL(E.TEXT,'')    AS 默认值,
  isnull(g.[value],'') AS 字段说明
FROM
  syscolumns a
LEFT JOIN systypes b
ON
  a.xtype=b.xusertype
INNER JOIN sysobjects d
ON
  a.id     =d.id
AND d.xtype='U'
AND d.name<>'dtproperties'
LEFT JOIN syscomments e
ON
  a.cdefault=e.id
LEFT JOIN sys.extended_properties g
ON
  a.id     =g.major_id
AND a.colid=g.minor_id
LEFT JOIN sys.extended_properties f
ON
  d.id         =f.major_id
AND f.minor_id =0
  --where   d.name='要查询的表'         --如果只查询指定表,加上此条件
ORDER BY
  a.id,
  a.colorder


ORACLE:

SELECT
  CASE
    WHEN ROWNUM=1
    THEN A.TABLE_NAME
    ELSE ''
  END AS 表名,
  CASE
    WHEN ROWNUM=1
    THEN C.COMMENTS
    ELSE ''
  END           AS 表说明,
  A.COLUMN_ID   AS 字段序号,
  A.COLUMN_NAME AS 字段名,
  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 字段类型,
  A.DATA_DEFAULT AS 默认值,
  A.NULLABLE     AS 能否为空,
  B.COMMENTS     AS 备注
FROM
  SYS.ALL_TAB_COLUMNS A,
  SYS.DBA_COL_COMMENTS B,
  SYS.USER_TAB_COMMENTS C
WHERE
  A.OWNER         =B.OWNER
AND A.TABLE_NAME  =B.TABLE_NAME
AND A.COLUMN_NAME =B.COLUMN_NAME
AND A.TABLE_NAME  =C.TABLE_NAME
AND A.OWNER       ='KHCSK'
AND A.TABLE_NAME IN ( 'PHA_STO_RECIPE' )
ORDER BY
  A.TABLE_NAME,
  A.COLUMN_ID


标签: SQLSERVER ORACLE 阅读: 705
上一篇: Oracle环境变量NLS_LANG - 1301次
下一篇: Web开发者安全速查表 - 267次

向右滑动解锁留言