如何快速查詢資料庫檢視表(View)的欄位資訊

今天遇到長官要求提供所需的資料訊息，因為要求的資料欄位是由多個資料表組合而成，要一個一個去查看原始資料表相當費時。本文介紹如何用 SQL 直接查出檢視表的欄位資訊。

各資料庫系統的查詢方式

Microsoft SQL Server

SELECT 
    c.name AS ColumnName,
    t.name AS DataType,
    c.max_length AS MaxLength,
    c.precision AS NumericPrecision,
    c.scale AS NumericScale,
    c.is_nullable AS IsNullable,
    ISNULL(dc.definition, '') AS DefaultValue
FROM sys.views v
INNER JOIN sys.columns c ON v.object_id = c.object_id
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
WHERE v.name = '檢視表名稱'
ORDER BY c.column_id;

注意： max_length 回傳的是 byte 數，不是字元數。nvarchar(100) 會顯示 200，換算時除以 2 才是實際字元長度。

MySQL/MariaDB

SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    NUMERIC_PRECISION,
    NUMERIC_SCALE,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '資料庫名稱'
AND TABLE_NAME = '檢視表名稱';

Oracle

SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    DATA_LENGTH,
    DATA_PRECISION,
    DATA_SCALE,
    NULLABLE,
    DATA_DEFAULT
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = '檢視表名稱'
ORDER BY COLUMN_ID;

注意： 若同一資料庫有同名 View 分屬不同 schema，需加 AND OWNER = '擁有者名稱' 避免撈到錯誤的那張。

PostgreSQL

SELECT 
    a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
    a.attnotnull AS is_not_null,
    pg_catalog.pg_get_expr(d.adbin, d.adrelid) AS default_value
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum)
WHERE a.attrelid = '檢視表名稱'::regclass
AND a.attnum > 0 
AND NOT a.attisdropped
ORDER BY a.attnum;

查詢結果說明

欄位	說明
Column Name	欄位名稱
Data Type	資料型態，如 varchar、int、decimal
Max Length	文字型態的最大長度
Numeric Precision	數值型態的精確度
Numeric Scale	小數位數
Is Nullable	是否允許 NULL
Default Value	欄位預設值