MySQL、Oracle、SQL server数据字典查询

1、mysql

---mysql 数据字典   view & sql
select
    a.ORDINAL_POSITION as ordinalPosition,
	a.TABLE_NAME as tableName,
	b.TABLE_COMMENT  as tableComment,
	a.COLUMN_NAME as columnName,
	a.COLUMN_TYPE as columnType,
	a.COLUMN_COMMENT as columnComment,
	case when k.COLUMN_NAME  is not NULL  then '✓' else null end as ispk
from information_schema.COLUMNS a
left join information_schema.TABLES b 
       on a.TABLE_NAME = b.TABLE_NAME
left join information_schema.KEY_COLUMN_USAGE k
       on k.TABLE_NAME  = a.TABLE_NAME 
      and k.COLUMN_NAME  = a.COLUMN_NAME 
      and k.CONSTRAINT_NAME = 'PRIMARY'
where a.TABLE_SCHEMA = '？'
  and a.TABLE_NAME in ('###',
'###',
'###',
'###',
'###',
'###')
order by b.TABLE_NAME,a.ORDINAL_POSITION;

2、Oracle

--oracle
SELECT 
     c.COLUMN_ID
     ,c.table_name
     ,d.COMMENTS  table_cname
     ,c.COLUMN_NAME 
     ,CASE WHEN c.DATA_TYPE in ('VARCHAR2','CHAR') THEN c.DATA_TYPE ||'('||c.data_length||')'
           WHEN c.DATA_TYPE = 'NUMBER' AND c.DATA_PRECISION IS NOT NULL AND c.DATA_SCALE IS NOT NULL 
                THEN c.DATA_TYPE ||'('||c.DATA_PRECISION||','||c.DATA_SCALE ||')'
           WHEN c.DATA_TYPE = 'NUMBER' AND c.DATA_PRECISION IS NOT NULL AND c.DATA_SCALE IS  NULL 
                THEN c.DATA_TYPE ||'('||c.DATA_PRECISION ||')'
           ELSE c.DATA_TYPE END AS DATA_TYPE
      , e.COMMENTS col_cname   
      ,CASE WHEN s.constraint_name IS NOT NULL THEN 'Y' ELSE NULL END AS ispk    
FROM ALL_TAB_COLUMNS c
INNER JOIN ALL_TAB_COMMENTS d
ON c.OWNER = d.OWNER 
AND c.TABLE_NAME  = d.TABLE_NAME 
INNER JOIN ALL_COL_COMMENTS e
ON c.OWNER = e.OWNER 
AND c.TABLE_NAME  = e.TABLE_NAME 
AND c.COLUMN_NAME  = e.COLUMN_NAME 
LEFT JOIN (select a.TABLE_NAME, a.constraint_name,  a.column_name 
from user_cons_columns a, user_constraints b 
where a.constraint_name = b.constraint_name 
  and b.constraint_type = 'P' ) s
  ON c.TABLE_NAME  = s.TABLE_NAME
  AND c.COLUMN_NAME  = s.column_name
WHERE 1=1
  AND c.OWNER = 'ODSS'
  AND c.TABLE_NAME = 'REP_TRANSACTIONS'
 ORDER BY 2,1 ;

3、SQL server

--sqlserver ddl
SELECT
c.colid colid,
s.name AS table_name,
'' AS table_Cname, 
c.name AS col_name,
'' AS col_cname,
case when t.name like '%char%' or t.name in ('binary','raw') then t.name + '(' + cast(c.[LENGTH] as varchar(10)) + ')'
     when t.name in ('numeric','decimal') then t.name + '(' + cast(c.xprec as varchar(10)) +',' + cast(c.xscale as varchar(10)) + ')'
     else t.name 
end as date_type,
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 = c.id AND colid = c.colid))) THEN ' √ ' ELSE '' 
END AS ispk 
FROM syscolumns c -- 数据表字段
INNER JOIN sysobjects s-- 数据对象
ON s.id = c.id
INNER JOIN systypes t -- 数据类型
ON c.xtype = t.xtype
WHERE (s.xtype = 'U')
order by s.id,c.colid;