常用关系型数据元数据获取

MySQL

表元数据

-- 表
SELECT
 a.table_schema,
 a.table_name,
 a.table_type,
 a.table_comment COMMENTS,
 a.TABLE_ROWS,
 b.view_definition VIEW_DESC
 FROM information_schema.`TABLES` a
 LEFT JOIN information_schema.VIEWS b
 ON (a.table_name = b.table_name AND a.table_schema = b.table_schema)
 where a.table_schema not in (
 'information_schema',
 'mysql',
 'sys');

字段元数据

-- 字段
 SELECT 
 table_schema
 , table_name
 , '' TABLE_COMMENTS
 ,  ordinal_position COLUMN_ID
 , COLUMN_NAME
 , column_comment column_comments
 , data_type
 , NULL DATA_LENGTH
 , NULL DATA_PRECISION
 , NULL  DATA_SCALE
 , data_type DATA_TYPE_COMBINE
 , IS_NULLABLE NULLABLE
 , NULL IS_PK
 FROM
 information_schema.COLUMNS a
 WHERE a.table_schema not in (
 'information_schema',
 'mysql',
 'sys');

SqlServer

表元数据

SELECT '{table_schema}'  table_schema
, a.NAME TABLE_NAME
, case 
	when a.xtype ='U' then 'TABLE' 
   else 'VIEW' 
  END table_type
, cast(b.VALUE AS varchar(100) ) COMMENTS
, 0 TABLE_ROWS
, C.definition VIEW_DESC
FROM
	sys.sysobjects a
LEFT JOIN sys.extended_properties b ON ( a.id= b.major_id )
LEFT JOIN SYS.sql_modules C ON (A.ID=C.OBJECT_ID)
WHERE a.xtype IN ( 'U', 'V' );

字段元数据

SELECT '{dbname}' table_schema
, d.name  table_name
, '' TABLE_COMMENTS
, colid COLUMN_ID
, A.NAME COLUMN_NAME
, CAST(g.VALUE AS varchar(200)) column_comments
, b.name  data_type
, a.length  DATA_LENGTH
, '' DATA_PRECISION
, '' DATA_SCALE
, b.name  DATA_TYPE_COMBINE
, isnullable NULLABLE
, '' IS_PK
FROM sys.syscolumns a
LEFT JOIN sys.systypes b ON ( a.xusertype= b.xusertype )
INNER JOIN sys.sysobjects d ON a.id=d.id  and d.xtype IN ( 'U', 'V' )
LEFT JOIN sys.extended_properties g ON a.id=G.major_id and a.colid=g.minor_id;

Oracle

表元数据

SELECT a.owner TABLE_SCHEMA
, a.TABLE_NAME
, a.table_type
, a.COMMENTS
, NULL table_rows
, b.text VIEW_DESC
FROM  all_tab_comments a
left join all_views b
on (a.owner=b.owner and a.table_name=b.view_name)
WHERE a.OWNER NOT IN
('SYS',
'SYSTEM',
'OUTLN',
'DIP',
'ORACLE_OCM',
'DBSNMP',
'APPQOSSYS',
'WMSYS',
'XS$NULL',
'EXFSYS',
'CTXSYS',
'XDB',
'ANONYMOUS',
'ORDSYS',
'ORDDATA',
'ORDPLUGINS',
'SI_INFORMTN_SCHEMA',
'MDSYS',
'OLAPSYS',
'OWBSYS_AUDIT',
'MDDATA',
'SPATIAL_WFS_ADMIN_USR',
'SPATIAL_CSW_ADMIN_USR',
'SYSMAN',
'MGMT_VIEW',
'FLOWS_FILES',
'APEX_PUBLIC_USER',
'APEX_030200',
'OWBSYS',
'SCOTT');

字段元数据

SELECT  
     a.owner  table_schema
   , a.table_name
   , c.comments  table_comments
   , a.column_id column_id
   , a.column_name   column_name
   , b.comments  column_comments
   , data_type
   , data_length
   , data_precision
   , data_scale
   , (case
             when data_type='CHAR' then data_type||'('||data_length||')'
             when data_type='VARCHAR' then data_type||'('||data_length||')'
             when data_type='VARCHAR2' then data_type||'('||data_length||')'
             when data_type='NCHAR' then data_type||'('||data_length||')'
             when data_type='NVARCHAR' then data_type||'('||data_length||')'
             when data_type='NVARCHAR2' then data_type||'('||data_length||')'
             when data_type='RAW' then data_type||'('||data_length||')'
             when data_type='NUMBER' then
                (
                   case
                      when data_scale is null and data_precision is null then 'NUMBER'
                      when data_scale <> 0  then 'NUMBER('||NVL(DATA_PRECISION,38)||','||DATA_SCALE||')'
                      else 'NUMBER('||NVL(DATA_PRECISION,38)||')'
                   end
                )
             else
               (
                   case
                       when data_type_owner is not null then data_type_owner||'.'||data_type
                       else data_type
                   end
                )
       end
       )  data_type_combine
       , a.NULLABLE NULLABLE
       , constraint_type IS_PK
FROM all_tab_columns a   --字段元数据
LEFT JOIN all_col_comments b   --字段注释元数据
ON (a.table_name =  b.table_name and a.COLUMN_name = b.COLUMN_name and a.owner = b.owner)
LEFT JOIN  all_tab_comments c   --表注释
ON (a.table_name =  c.table_name and a.owner = c.owner)
LEFT join
(
   select a.owner ,a.table_name,column_name,constraint_type
   from  all_cons_columns  a
   join  all_constraints  b
   on (a.table_name=b.table_name and a.owner=b.owner and a.constraint_name=b.constraint_name)
   where b.constraint_type = 'P'
) d
ON (a.table_name =  d.table_name and a.owner = d.owner and a.COLUMN_name = d.COLUMN_name)
WHERE A.OWNER NOT IN
('SYS',
   'SYSTEM',
   'OUTLN',
   'DIP',
   'ORACLE_OCM',
   'DBSNMP',
   'APPQOSSYS',
   'WMSYS',
   'XS$NULL',
   'EXFSYS',
   'CTXSYS',
   'XDB',
   'ANONYMOUS',
   'ORDSYS',
   'ORDDATA',
   'ORDPLUGINS',
   'SI_INFORMTN_SCHEMA',
   'MDSYS',
   'OLAPSYS',
   'OWBSYS_AUDIT',
   'MDDATA',
   'SPATIAL_WFS_ADMIN_USR',
   'SPATIAL_CSW_ADMIN_USR',
   'SYSMAN',
   'MGMT_VIEW',
   'FLOWS_FILES',
   'APEX_PUBLIC_USER',
   'APEX_030200',
   'OWBSYS',
   'SCOTT');

对象元数据

SELECT * 
FROM all_objects
WHERE OWNER NOT IN
   ('SYS',
       'SYSTEM',
       'OUTLN',
       'DIP',
       'ORACLE_OCM',
       'DBSNMP',
       'APPQOSSYS',
       'WMSYS',
       'XS$NULL',
       'EXFSYS',
       'CTXSYS',
       'XDB',
       'ANONYMOUS',
       'ORDSYS',
       'ORDDATA',
       'ORDPLUGINS',
       'SI_INFORMTN_SCHEMA',
       'MDSYS',
       'OLAPSYS',
       'OWBSYS_AUDIT',
       'MDDATA',
       'SPATIAL_WFS_ADMIN_USR',
       'SPATIAL_CSW_ADMIN_USR',
       'SYSMAN',
       'MGMT_VIEW',
       'FLOWS_FILES',
       'APEX_PUBLIC_USER',
       'APEX_030200',
       'OWBSYS',
       'SCOTT');

同义词元数据

SELECT * 
FROM all_synonyms
WHERE OWNER NOT IN
    ('SYS',
        'SYSTEM',
        'OUTLN',
        'DIP',
        'ORACLE_OCM',
        'DBSNMP',
        'APPQOSSYS',
        'WMSYS',
        'XS$NULL',
        'EXFSYS',
        'CTXSYS',
        'XDB',
        'ANONYMOUS',
        'ORDSYS',
        'ORDDATA',
        'ORDPLUGINS',
        'SI_INFORMTN_SCHEMA',
        'MDSYS',
        'OLAPSYS',
        'OWBSYS_AUDIT',
        'MDDATA',
        'SPATIAL_WFS_ADMIN_USR',
        'SPATIAL_CSW_ADMIN_USR',
        'SYSMAN',
        'MGMT_VIEW',
        'FLOWS_FILES',
        'APEX_PUBLIC_USER',
        'APEX_030200',
        'OWBSYS',
        'SCOTT');