萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

自訂函數取得資料表結構:欄位名、類型、長度與是否允許 NULL

動機

在動態 SQL 或程式碼產生器中,常需依據資料表名稱取得其結構定義 (欄位名稱、資料類型、長度、是否允許 NULL)。SQL Server 提供系統表 sysobjects、syscolumns (或新版 sys.columns、sys.types) 供查詢,封裝為「傳回資料表變數的多陳述式函數」可方便重複使用。

函數類型簡介

  • 純量函數:回傳單一值。
  • 資料列集函數:回傳 TABLE 型別。
    • 內嵌資料列集函數:單一 SELECT 陳述式。
    • 多陳述式資料列集函數:包含多個陳述式、邏輯控制,本文採用此類型。

函數語法架構

CREATE FUNCTION function_name (@parameter_name datatype)
RETURNS @return_variable TABLE (column_definitions)
AS
BEGIN
    -- function_body
    RETURN
END

實作範例:tablestructure

輸入資料表名稱 (支援 schema 限定,如 dbo.Orders),回傳欄位清單。

CREATE FUNCTION dbo.tablestructure (
    @objname NVARCHAR(111)  -- 目標資料表名稱
)
RETURNS @table TABLE (
    fieldname VARCHAR(50),   -- 欄位名稱
    type VARCHAR(16),        -- 資料類型名稱
    length INT,              -- 長度 (位元組/字元)
    nullable VARCHAR(3)      -- 'yes' 或 'no'
)
AS
BEGIN
    DECLARE @objid INT

    -- 1. 從 sysobjects 取得物件 ID
    SELECT @objid = id FROM sysobjects WHERE id = OBJECT_ID(@objname)

    -- 2. 從 syscolumns 取得欄位資訊 (number=0 排除計算欄位等)
    INSERT @table
    SELECT
        'fieldname' = name,
        'type'      = TYPE_NAME(xusertype),  -- 取得使用者定義類型名稱
        'length'    = CONVERT(INT, length),
        'nullable'  = CASE WHEN isnullable = 0 THEN 'no' ELSE 'yes' END
    FROM syscolumns
    WHERE id = @objid AND number = 0
    ORDER BY colid

    RETURN
END

使用範例

SELECT * FROM dbo.tablestructure('dbo.Orders')
/* 結果範例
fieldname   type        length  nullable
----------- ----------- ------- --------
OrderID     int         4       no
CustomerID  nchar       10      no
OrderDate   datetime    8       yes
... */

進階擴充建議

  • 加入 is_identity、is_computed、default_value 等欄位。
  • 支援 sys.types、sys.columns (SQL Server 2005+) 以取得更完整中繼資料 (精度、小數位、排序規則等)。
  • 加入 @schema_name 參數避免同名表衝突。

注意事項

  • 舊版系統表 syscolumns 在未來版本可能移除,新專案建議改用 sys.columns + sys.types。
  • 函數執行計畫不會快取,高頻呼叫時考慮改用預存程序或快取結果。
copyright © 萬盛學電腦網 all rights reserved