利用SQL語法把資料庫Schema匯出，包含註解全部顯示出來

tags: 資料庫

SQLServer或Oracle都可以使用，語法稍微不一樣

附一張查詢的圖

資料來源會是這兩個Table，分別是資料行和資料表

select * from INFORMATION_SCHEMA.COLUMNS
select * from INFORMATION_SCHEMA.TABLES

如果要取資料表的描述，要使用以下的sql

SELECT a.Table_schema +'.'+a.Table_name   as 表格名稱   
           ,c.COLUMN_NAME                     as 主鍵欄位
           ,b.COLUMN_NAME                     as 欄位名稱   
       ,b.DATA_TYPE                       as 資料型別   
       ,isnull(b.CHARACTER_MAXIMUM_LENGTH,'') as 長度   
       ,isnull(b.COLUMN_DEFAULT,'')           as 預設值   
       ,b.IS_NULLABLE                         as 是否允許空值

       ,( SELECT value   
          FROM fn_listextendedproperty (NULL, 'schema', a.Table_schema, 'table', a.TABLE_NAME, 'column', default)   
          WHERE name='MS_Description' and objtype='COLUMN'    
          and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME   
        ) as 欄位描述   
FROM INFORMATION_SCHEMA.TABLES  a   
 LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME = b.TABLE_NAME   
 LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON b.TABLE_NAME = c.TABLE_NAME   

WHERE TABLE_TYPE='BASE TABLE'
ORDER BY a.TABLE_NAME , b.ORDINAL_POSITION 

以下是Oracle的版本

     SELECT
      C.OWNER, C.TABLE_NAME, C.COLUMN_ID, C.COLUMN_NAME, 
      DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_DEFAULT, 
      NULLABLE, COMMENTS
    FROM
      ALL_TAB_COLUMNS C 
    JOIN ALL_TABLES T ON 
      C.OWNER = T.OWNER AND C.TABLE_NAME = T.TABLE_NAME
    LEFT JOIN ALL_COL_COMMENTS R ON
      C.OWNER = R.Owner AND 
      C.TABLE_NAME = R.TABLE_NAME AND 
      C.COLUMN_NAME = R.COLUMN_NAME 
      ORDER BY C.TABLE_NAME, C.COLUMN_ID

前面的語法是全撈，如果只想匯出特定的資料庫，可以給資料庫名稱，只要加上where條件式如下

    WHERE  C.OWNER  = 'SCHEMA_NAME'

參考文獻

https://dotblogs.com.tw/puma/2008/06/18/4326