您的位置 首页 java

真的！纯SQL就能查询MySQL、Oracle元数据，收藏一下

智云科技发布于 2023年4月21日评论关闭阅读(105)

1、 MySQL

表查询： 

 SELECT 
  '' AS TABLE_SCHEMA,
  TABLE_NAME,
  TABLE_COMMENT FROMinformation_schema.TABLES 
WHERE TABLE_SCHEMA = '${db}'

字段查询： 

 SELECT 
  '' AS TABLE_SCHEMA,
  c.TABLE_NAME,
  c.COLUMN_NAME,
  c.COLUMN_TYPE,
  '' AS COLUMN_LENGTH,
  IF(c.COLUMN_KEY = 'PRI', 1, 0) COLUMN_KEY,
  IF(c.IS_NULLABLE = 'NO', 1, 0) AS NULLABLE,
  c.COLUMN_COMMENT 
FROM
  information_schema.COLUMNS c 
WHERE c.TABLE_SCHEMA = '${db}'

2、 Oracle

表查询： 

 SELECT 
  OWNER AS TABLE_SCHEMA,
  TABLE_NAME AS TABLE_NAME,
  COMMENTS AS TABLE_COMMENT 
from
  all_tab_comments 
WHERE OWNER NOT IN (
    'ANONYMOUS',
    'APEX_030200',
    'APEX_PUBLIC_USER',
    'APPQOSSYS',
    'AURORA$JIS$UTILITY$',
    'AURORA$ORB$UNAUTHENTICATED',
    'BI',
    'CTXSYS',
    'DBSNMP',
    'DIP',
    'DMSYS',
    'DVF',
    'DVSYS',
    'EXFSYS',
    'FLOWS_040100',
    'FLOWS_FILES',
    'HR',
    'IX',
    'LBACSYS',
    'MDDATA',
    'MDSYS',
    'MGMT_VIEW',
    'OAS_PUBLIC',
    'ODM',
    'ODM_MTR',
    'OE',
    'OLAPSYS',
    'ORACLE_OCM',
    'ORDDATA',
    'ORDPLUGINS',
    'ORDSYS',
    'OSE$HTTP$ADMIN',
    'OUTLN',
    'OWBSYS',
    'OWBSYS_AUDIT',
    'PERFSTAT',
    'PM',
    'QS',
    'QS_ADM',
    'QS_CB',
    'QS_CBADM',
    'QS_CS',
    'QS_ES',
    'QS_OS',
    'QS_WS',
    'REPADMIN',
    'RMAN',
    'SCOTT',
    'SH',
    'SI_INFORMTN_SCHEMA',
    'SPATIAL_CSW_ADMIN_USR',
    'SPATIAL_WFS_ADMIN_USR',
    'SYS',
    'SYSMAN',
    'SYSTEM',
    'TRACESVR',
    'TSMSYS',
    'WEBSYS',
    'WKPROXY',
    'WKSYS',
    'WKUSER',
    'WK_TEST',
    'WMSYS',
    'XDB',
    'XS$NULL'
  )

字段查询： 

 SELECT 
  tc.OWNER AS TABLE_SCHEMA,
  tc.TABLE_NAME AS TABLE_NAME,
  tc.COLUMN_NAME,
  tc.DATA_TYPE AS COLUMN_TYPE,
  tc.DATA_LENGTH AS COLUMN_LENGTH,
  NVL2 (ic.COLUMN_POSITION, 'YES', '') AS COLUMN_KEY,
  tc.NULLABLE,
  cc.COMMENTS AS COLUMN_COMMENT 
FROM
  all_tab_columns tc 
   LEFT JOIN  all_col_comments cc 
    ON cc.OWNER = tc.OWNER 
    AND cc.TABLE_NAME = tc.TABLE_NAME 
    AND cc.COLUMN_NAME = tc.COLUMN_NAME 
  LEFT JOIN ALL_IND_COLUMNS ic 
    ON ic.TABLE_OWNER = tc.OWNER 
    AND ic.TABLE_NAME = tc.TABLE_NAME 
    AND ic.COLUMN_NAME = tc.COLUMN_NAME

3、 SqlServer

表查询： 

 SELECT 
  '' AS TABLE_SCHEMA,
  a.name AS TABLE_NAME,
  CONVERT(
    NVARCHAR (255),
    ISNULL(g.[ VALUE ], '-')
  ) AS TABLE_COMMENT 
FROM
  sys.tables a 
  LEFT JOIN sys.extended_properties g 
    ON (
      a.object_id = g.major_id 
      AND g.minor_id = 0
    )

字段查询： 

 SELECT 
  '' AS TABLE_SCHEMA,
  d.name AS TABLE_NAME,
  a.name AS COLUMN_NAME,
  b.name AS COLUMN_TYPE,
  COLUMNPROPERTY (a.id, a.name, 'PRECISION') AS COLUMN_LENGTH,
  CASE
    WHEN EXISTS 
    (SELECT 
      1 
    FROM
      ..sysobjects 
    WHERE xtype = 'PK' 
      AND parent_obj = a.id 
      AND NAME IN 
      (SELECT 
        NAME 
      FROM
        sysindexes 
      WHERE indid IN 
        (SELECT 
          indid 
        FROM
          sysindexkeys 
        WHERE id = a.id 
          AND colid = a.colid))) 
    THEN '1' 
    ELSE '0' 
  END AS COLUMN_KEY,
  CASE
    WHEN a. ISNULL able = 1 
    THEN '1' 
    ELSE '0' 
  END AS NULLABLE,
  CONVERT(NVARCHAR (255), ISNULL(g.[ VALUE ], '')) AS COLUMN_COMMENT 
FROM
  syscolumns a 
  LEFT JOIN systypes b 
    ON a.xusertype = b.xusertype 
  INNER JOIN ..sysobjects d 
    ON a.id = d.id 
    AND d.xtype = 'U' 
  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

4、SqlServer_low（低版本）

表查询： 

 SELECT 
  '' AS TABLE_SCHEMA,
  a.name AS TABLE_NAME,
  CONVERT(NVARCHAR (255), ISNULL(b.[ VALUE ], '')) AS TABLE_COMMENT 
FROM
  ..sysobjects a 
  LEFT JOIN sysproperties b 
    ON a.id = b.id 
    AND b.smallid = 0 
WHERE a.xtype = 'U'

字段查询： 

 SELECT 
  '' AS TABLE_SCHEMA,
  d.name AS TABLE_NAME,
  a.name AS COLUMN_NAME,
  b.name AS COLUMN_TYPE,
  COLUMNPROPERTY (a.id, a.name, 'PRECISION') AS COLUMN_LENGTH,
  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 '1' 
    ELSE '0' 
  END AS COLUMN_KEY,
  CASE
    WHEN a.isnullable = 1 
    THEN '1' 
    ELSE '0' 
  END AS NULLABLE,
  CONVERT(NVARCHAR (255), ISNULL(g.[ VALUE ], '')) AS COLUMN_COMMENT 
FROM
  syscolumns a 
  LEFT OUTER JOIN systypes b 
    ON a.xtype = b.xusertype 
   INNER JOIN  sysobjects d 
    ON a.id = d.id 
    AND d.xtype = 'U' 
   LEFT  OUTER JOIN syscomments e 
    ON a.cdefault = e.id 
  LEFT OUTER JOIN sysproperties g 
    ON a.id = g.id 
    AND a.colid = g.smallid

5、 PostgreSQL

表查询： 

 WITH tmp_tab AS 
(SELECT 
  pc.oid AS ooid,
  pn.nspname,
  pc.* 
FROM
  pg_class pc 
  LEFT OUTER JOIN pg_namespace pn 
    ON pc.relnamespace = pn.oid 
WHERE pc.relkind IN ('r') 
  AND pn.nspname NOT IN (
    'pg_catalog',
    'information_schema'
  ) 
  AND pn.nspname NOT LIKE 'pg_toast%' 
  AND pc.oid NOT IN 
  (SELECT 
    inhrelid 
  FROM
    pg_inherits) 
  AND pc.relname NOT LIKE '%peiyb%' 
ORDER BY pc.relname),
tmp_desc AS 
(SELECT 
  pd.* 
FROM
  pg_description pd 
WHERE pd.objsubid = 0) 
SELECT 
  tab.nspname AS TABLE_SCHEMA,
  tab.relname AS TABLE_NAME,
  de.description AS TABLE_COMMENT 
FROM
  tmp_tab tab 
  LEFT OUTER JOIN tmp_desc de 
    ON tab.ooid = de.objoid

字段查询： 

 WITH tmp_tab AS 
(SELECT 
  pc.oid AS ooid,
  pn.nspname,
  pc.* 
FROM
  pg_class pc 
  LEFT OUTER JOIN pg_namespace pn 
    ON pc.relnamespace = pn.oid 
WHERE pc.relkind IN ('r') 
  AND pn.nspname NOT IN (
    'pg_catalog',
    'information_schema'
  ) 
  AND pn.nspname NOT LIKE 'pg_toast%' 
  AND pc.oid NOT IN 
  (SELECT 
    inhrelid 
  FROM
    pg_inherits) 
  AND pc.relname NOT LIKE '%peiyb%'),
tmp_col AS 
(SELECT 
  pa.*,
  pt.typname 
FROM
  pg_attribute pa 
  LEFT JOIN pg_type pt 
    ON pa.atttypid = pt.oid 
WHERE pa.attnum > 0),
tmp_desc AS 
(SELECT 
  pd.* 
FROM
  pg_description pd 
WHERE pd.objsubid <> 0) 
SELECT 
  tab.nspname AS TABLE_SCHEMA,
  tab.relname AS TABLE_NAME,
  tc.attname AS COLUMN_NAME,
  CASE
    WHEN tc.attlen < 0 
    THEN tc.atttypmod - 4 
    ELSE tc.attlen 
  END COLUMN_LENGTH,
  tc.typname AS COLUMN_TYPE,
  '' AS COLUMN_KEY,
  CASE
    WHEN tc.attnotnull = 't' 
    THEN '1' 
    ELSE '0' 
  END NULLABLE,
  de.description AS COLUMN_COMMENT 
FROM
  tmp_tab tab 
  LEFT OUTER JOIN tmp_col tc 
    ON tab.ooid = tc.attrelid 
  LEFT OUTER JOIN tmp_desc de 
    ON tc.attrelid = de.objoid 
    AND tc.attnum = de.objsubid 
WHERE tc.attisdropped = FALSE

6、 Vertica

表查询： 

 SELECT 
  schema_name AS TABLE_SCHEMA,
  table_name AS TABLE_NAME,
  remarks AS TABLE_COMMENT 
FROM
  all_tables 
WHERE table_type = 'TABLE'

字段查询： 

 SELECT 
  a.table_schema AS TABLE_SCHEMA,
  a.table_name AS TABLE_NAME,
  a.column_name AS COLUMN_NAME,
  a.data_type AS COLUMN_TYPE,
  '' AS COLUMN_LENGTH,
  CASE
    WHEN a.is_identity = 'true' 
    THEN '1' 
    ELSE '0' 
  END AS COLUMN_KEY,
  CASE
    WHEN a.is_nullable = 'true' 
    THEN '1' 
    ELSE '0' 
  END AS NULLABLE,
  b.comment AS COLUMN_COMMENT 
FROM
  COLUMNS a 
  LEFT JOIN comments b 
    ON a.table_name || '_super.' || a.column_name = b.object_name 
WHERE a.is_system_table = 'false' 
  AND b.object_type = 'COLUMN'

7、DM7

表查询： 

 SELECT 
  a.OWNER AS TABLE_SCHEMA,
  a.TABLE_NAME,
  NVL (b.COMMENT $, '') AS TABLE_COMMENT 
FROM
  ALL_TABLES a 
  LEFT JOIN SYSTABLECOMMENTS b 
    ON a.OWNER = b.SCHNAME 
    AND a.TABLE_NAME = b.TVNAME 
WHERE a.STATUS = 'VALID'

字段查询： 

 SELECT 
  a.OWNER AS TABLE_SCHEMA,
  a.TABLE_NAME,
  a.COLUMN_NAME,
  a.DATA_TYPE AS COLUMN_TYPE,
  a.DATA_LENGTH AS COLUMN_LENGTH,
   DECODE (d. CONSTRAINT _TYPE, 'P', 'YES', '') AS COLUMN_KEY,
  a.NULLABLE,
  NVL (b.COMMENT $, '') AS COLUMN_COMMENT 
FROM
  ALL_TAB_COLUMNS a 
  LEFT JOIN SYSCOLUMNCOMMENTS b 
    ON a.OWNER = b.SCHNAME 
    AND a.TABLE_NAME = b.TVNAME 
    AND a.COLUMN_NAME = b.COLNAME 
  LEFT JOIN ALL_CONS_COLUMNS c 
    ON b.SCHNAME = c.OWNER 
    AND b.TVNAME = c.TABLE_NAME 
    AND b.COLNAME = c.COLUMN_NAME 
  LEFT JOIN ALL_CONSTRAINTS d 
    ON c.CONSTRAINT_NAME = d.CONSTRAINT_NAME 
    AND c.OWNER = d.OWNER 
    AND c.TABLE_NAME = d.TABLE_NAME

8、GBase

表查询： 

 SELECT 
  '' AS TABLE_SCHEMA,
  TABLE_NAME,
  TABLE_COMMENT 
FROM
  information_schema.TABLES 
WHERE TABLE_SCHEMA = '${db}'

字段查询： 

 SELECT 
  '' AS TABLE_SCHEMA,
  c.TABLE_NAME,
  c.COLUMN_NAME,
  c.COLUMN_TYPE,
  '' AS COLUMN_LENGTH,
  IF(c.COLUMN_KEY = 'PRI', 1, 0) COLUMN_KEY,
  IF(c.IS_NULLABLE = 'NO', 1, 0) AS NULLABLE,
  c.COLUMN_COMMENT 
FROM
  information_schema.COLUMNS c 
WHERE c.TABLE_SCHEMA = '${db}'
标签:
打赏 点赞(1)

文章来源：智云一二三科技

文章标题：真的！纯SQL就能查询MySQL、Oracle元数据，收藏一下

文章地址：https://www.zhihuclub.com/197381.shtml

关于作者: 智云科技

相关文章

热门文章

1分享新浪图床上传接口源码

点赞(934)阅读(61,062)

2PHP简单实现路由Route功能

点赞(1.15K)阅读(35,959)

3Tideways、xhprof 和 xhgui 打造 PHP 非侵入式监控平台

点赞(359)阅读(4,285)

4centos系统如何查看是否安装了mysql

点赞(533)阅读(3,783)

5curl 工具简述

点赞(505)阅读(2,973)
网站地图