取得Table Schema

以下將利用系統表格取得Table Schema，如Owner(Schema)、Table Name、Column Name、Data Type、Nullable及欄位說明等幾項重要項目。

ORACLE相對簡單許多，僅需查詢

1.       ALL_TAB_COLUMNS (欄位相關屬性-名稱、型態等)

2.       ALL_COL_COMMENTS(欄位說明)

MSSQL則相對較為複雜，建議優先採用2005版本上開始提供的系統表格，如

1.       sys.tables (資料表)

2.       sys.columns (欄位)

3.       sys.types (資料型別)

4.       sys.extended_properties (延伸屬性-取得欄位說明)

	SQL
ORACLE	SELECT COL.OWNER      , COL.TABLE_NAME      , COL.COLUMN_ID      , COL.COLUMN_NAME      , COL.DATA_TYPE          || CASE WHEN COL.DATA_TYPE IN ('NUMBER', 'DECIMAL') THEN                               ' (' || COL.DATA_PRECISION || ', ' || COL.DATA_SCALE || ')'                     WHEN INSTR(COL.DATA_TYPE, 'CHAR')>0  THEN                             ' (' || COL.DATA_LENGTH || ') '             END DATA_TYPE      , DECODE(COL.NULLABLE, 'Y' ,'', COL.NULLABLE)  NULLABLE      , CMT.COMMENTS             FROM ALL_TAB_COLUMNS COL, ALL_COL_COMMENTS CMT WHERE 1=1        AND COL.COLUMN_NAME = CMT.COLUMN_NAME        AND COL.TABLE_NAME  = CMT.TABLE_NAME        AND COL.OWNER       = CMT.OWNER       AND COL.OWNER       = 'HR'
MSSQL	SELECT OBJECT_SCHEMA_NAME(tab.object_id)   Sch_Name , tab.name                                                                     Table_Name , col.column_id                                                             column_id , col.name                                                                     Column_name , typ.name 　  + CASE WHEN typ.name IN ('numeric','decimal') THEN               --(1).數值型態                                  CONCAT(' (', col.PRECISION, ', ', col.scale, ')')                       WHEN CHARINDEX('char', typ.name)>0  THEN          --(2).字元型態                                    ' ('                                    +　CASE WHEN col.max_length =-1 THEN 'max'  --, 長度-1為max                                    　　　            WHEN typ.name LIKE 'n%' THEN  CAST(col.max_length/2  AS varchar(10))                                                      ELSE CAST(col.max_length AS varchar(10))                                            END 　　　　　               + ')'                    ELSE ''                                                                                    --(3). 其他型態 　　END Data_Type , CASE WHEN col.is_nullable=0 THEN '' ELSE 'Y' END  Nullable  , Comments , tab.object_id --, col.max_length FROM sys.tables tab                -- 1. 資料表                 INNER JOIN sys.columns AS col  -- 2. 欄位                                 ON tab.object_id = col.object_id                 INNER JOIN sys.types AS typ    -- 3. 型態                                 ON col.user_type_id=typ.user_type_id                 OUTER APPLY               -- 4. 欄位說明                                 (                                 SELECT CAST(sep.value AS VARCHAR(1000)) Fld_Comments                                 FROM  sys.extended_properties sep                                 WHERE 1=1                                                 AND tab.object_id = sep.major_id                                                 AND col.column_id = sep.minor_id                                                 AND sep.name    　= 'MS_Description'                                 ) fc (Comments)

以MSSQL為例

(1)    建立資料表

CREATE TABLE CUST

(

Cust_Id              int IDENTITY(1, 1)        NOT NULL,  --未使用，暫先建立

ID_No               varchar(10)                                     NOT NULL,

Cust_Name       nvarchar(20)                                   NOT NULL,

Addr                  nvarchar(200)                                          NULL,

Phone                varchar(20)                                               NULL

CONSTRAINT PK_CUST PRIMARY KEY CLUSTERED (ID_No)

)

EXEC sp_addextendedproperty  N'MS_Description','客戶代碼', N'SCHEMA', N'dbo', N'table','CUST', N'COLUMN','Cust_Id'

EXEC sp_addextendedproperty  N'MS_Description','身份證號', N'SCHEMA', N'dbo', N'table','CUST', N'COLUMN','ID_No'

EXEC sp_addextendedproperty  N'MS_Description','客戶姓名', N'SCHEMA', N'dbo', N'table','CUST', N'COLUMN','Cust_Name'

EXEC sp_addextendedproperty  N'MS_Description','地址', N'SCHEMA', N'dbo', N'table','CUST', N'COLUMN','Addr'

EXEC sp_addextendedproperty  N'MS_Description','電號號碼', N'SCHEMA', N'dbo', N'table','CUST', N'COLUMN','Phone'

(2)    產出結果