如何快速查詢資料庫檢視表(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;

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;

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;