在動態 SQL 或程式碼產生器中,常需依據資料表名稱取得其結構定義 (欄位名稱、資料類型、長度、是否允許 NULL)。SQL Server 提供系統表 sysobjects、syscolumns (或新版 sys.columns、sys.types) 供查詢,封裝為「傳回資料表變數的多陳述式函數」可方便重複使用。
SELECT 陳述式。CREATE FUNCTION function_name (@parameter_name datatype)
RETURNS @return_variable TABLE (column_definitions)
AS
BEGIN
-- function_body
RETURN
END
輸入資料表名稱 (支援 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。