您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 取得資料表建立腳本 (DDL) 完整預存程序：sp_helptext 無法處理表物件的解決方案

編輯：關於SqlServer

`sp_helptext` 僅支援預存程序、檢視、函數、觸發器等**模組類物件**，無法取得資料表的 `CREATE TABLE` DDL。本文提供一支功能完整的預存程序 `SP_GET_TABLE_INFO`，能依據系統目錄產生包含**欄位定義 (型別、長度、精度、身分識別、NULL/NOT NULL、預設值)、主鍵、唯一索引、叢集/非叢集索引、IGNORE_DUP_KEY、ALLOW_DUP_ROW** 等完整 DDL 腳本，相容 SQL Server 2000/2005。

一、預存程式碼完整版


CREATE PROCEDURE SP_GET_TABLE_INFO
    @ObjName VARCHAR(128) -- 目標資料表名稱
AS
BEGIN
    SET NOCOUNT ON;

    -- 參數宣告
    DECLARE @Script VARCHAR(255), @ColName VARCHAR(30), @ColID TINYINT, @UserType SMALLINT,
            @TypeName SYSNAME, @Length TINYINT, @Prec TINYINT, @Scale TINYINT, @Status TINYINT,
            @cDefault INT, @Const_Key VARCHAR(255), @IndID SMALLINT, @IndStatus INT,
            @Index_Key VARCHAR(255), @DBName VARCHAR(30), @strPri_Key VARCHAR(255);

    -- 檢查表是否存在
    IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = @ObjName)
    BEGIN
        SELECT @DBName = DB_NAME();
        RAISERROR(15009, -1, -1, @ObjName, @DBName);
        RETURN 1;
    END

    -- 暫存表儲存產生的腳本行
    CREATE TABLE #spscript (id INT IDENTITY, Script VARCHAR(255) NOT NULL, LastLine TINYINT);

    -- ===== 1. 處理欄位定義 =====
    DECLARE Cursor_Column INSENSITIVE CURSOR FOR
    SELECT a.name, a.colid, a.usertype, b.name, a.length, a.prec, a.scale, a.status, a.cdefault,
           CASE a.cdefault WHEN 0 THEN '' ELSE (SELECT c.text FROM syscomments c WHERE a.cdefault = c.id) END
    FROM syscolumns a, systypes b
    WHERE OBJECT_NAME(a.id) = @ObjName AND a.usertype = b.usertype
    ORDER BY a.colid;

    SELECT @Script = 'CREATE TABLE ' + @ObjName + '(';
    INSERT INTO #spscript VALUES (@Script, 0);

    OPEN Cursor_Column;
    FETCH NEXT FROM Cursor_Column INTO @ColName, @ColID, @UserType, @TypeName, @Length, @Prec, @Scale, @Status, @cDefault, @Const_Key;

    WHILE @@FETCH_STATUS <> -1
    BEGIN
        IF @@FETCH_STATUS <> -2
        BEGIN
            SELECT @Script = @ColName + ' ' + @TypeName;

            -- 資料型別長度/精度
            IF @UserType IN (1,2,3,4) -- char, varchar, binary, varbinary
                SELECT @Script = @Script + '(' + CONVERT(CHAR(3), @Length) + ') ';
            ELSE IF @UserType IN (24) -- decimal, numeric
                SELECT @Script = @Script + '(' + CONVERT(CHAR(3), @Prec) + ',' + CONVERT(CHAR(3), @Scale) + ') ';
            ELSE
                SELECT @Script = @Script + ' ';

            -- IDENTITY
            IF (@Status & 0x80) > 0 SELECT @Script = @Script + 'IDENTITY(1,1) ';

            -- NULL / NOT NULL
            IF (@Status & 0x08) > 0 SELECT @Script = @Script + 'NULL ';
            ELSE SELECT @Script = @Script + 'NOT NULL ';

            -- DEFAULT
            IF @cDefault > 0 SELECT @Script = @Script + 'DEFAULT ' + @Const_Key;
        END

        FETCH NEXT FROM Cursor_Column INTO @ColName, @ColID, @UserType, @TypeName, @Length, @Prec, @Scale, @Status, @cDefault, @Const_Key;

        IF @@FETCH_STATUS = 0
        BEGIN
            SELECT @Script = @Script + ',';
            INSERT INTO #spscript VALUES (@Script, 0);
        END
        ELSE
        BEGIN
            INSERT INTO #spscript VALUES (@Script, 1); -- 最後一欄
            INSERT INTO #spscript VALUES (')', 0);     -- 結束括號
        END
    END
    CLOSE Cursor_Column; DEALLOCATE Cursor_Column;

    -- ===== 2. 處理索引與主鍵 =====
    DECLARE Cursor_Index INSENSITIVE CURSOR FOR
    SELECT name, indid, status
    FROM sysindexes
    WHERE OBJECT_NAME(id) = @ObjName AND indid > 0 AND indid <> 255 -- 255 = 文字/圖片資料
    ORDER BY indid;

    OPEN Cursor_Index;
    FETCH NEXT FROM Cursor_Index INTO @ColName, @IndID, @IndStatus;

    WHILE @@FETCH_STATUS <> -1
    BEGIN
        IF @@FETCH_STATUS <> -2
        BEGIN
            DECLARE @i TINYINT = 1, @thiskey VARCHAR(50), @IndDesc VARCHAR(68);

            -- 建構索引鍵欄位清單
            WHILE @i <= 16
            BEGIN
                SELECT @thiskey = INDEX_COL(@ObjName, @IndID, @i);
                IF @thiskey IS NULL BREAK;
                IF @i = 1 SELECT @Index_Key = @thiskey;
                ELSE SELECT @Index_Key = @Index_Key + ', ' + @thiskey;
                SELECT @i = @i + 1;
            END

            -- 索引類型前綴
            IF (@IndStatus & 0x02) > 0 SELECT @Script = 'CREATE UNIQUE ';
            ELSE SELECT @Script = 'CREATE ';

            IF @IndID = 1 SELECT @Script = @Script + 'CLUSTERED '; -- 叢集索引

            -- 主鍵處理 (indid=1 且 primary key)
            IF (@IndStatus & 0x800) > 0
                SELECT @strPri_Key = ' PRIMARY KEY (' + @Index_Key + ')';
            ELSE
                SELECT @strPri_Key = '';

            IF @IndID > 1 SELECT @Script = @Script + 'NONCLUSTERED ';

            SELECT @Script = @Script + 'INDEX ' + @ColName + ' ON ' + @ObjName + '(' + @Index_Key + ')';

            -- 索引選項
            IF (@IndStatus & 0x01) = 0x01 SELECT @IndDesc = @IndDesc + 'IGNORE_DUP_KEY,';
            -- IGNORE_DUP_ROW (0x04) SQL 2000+ 不支援，略過
            IF (@IndStatus & 0x40) = 0x40 SELECT @IndDesc = @IndDesc + 'ALLOW_DUP_ROW,';

            IF @IndDesc <> ''
            BEGIN
                SELECT @IndDesc = SUBSTRING(@IndDesc, 1, DATALENGTH(@IndDesc) - 1);
                SELECT @Script = @Script + ' WITH ' + @IndDesc;
            END
        END

        -- 輸出：主鑰合併到 CREATE TABLE 結尾，其他索引獨立行
        IF @strPri_Key = ''
            INSERT INTO #spscript VALUES (@Script, 0);
        ELSE
            UPDATE #spscript SET Script = Script + @strPri_Key WHERE LastLine = 1;

        FETCH NEXT FROM Cursor_Index INTO @ColName, @IndID, @IndStatus;
    END
    CLOSE Cursor_Index; DEALLOCATE Cursor_Index;

    -- ===== 3. 輸出結果 =====
    SELECT Script FROM #spscript ORDER BY id;
    SET NOCOUNT OFF;
    RETURN 0;
END
GO

二、使用範例與輸出


EXEC SP_GET_TABLE_INFO 'Orders';

輸出範例 (每行一筆 #spscript 記錄)：


CREATE TABLE Orders(
OrderID INT IDENTITY(1,1) NOT NULL,
CustomerID NCHAR(5) NULL,
EmployeeID INT NOT NULL,
OrderDate DATETIME NOT NULL DEFAULT GETDATE(),
...
)
CREATE UNIQUE CLUSTERED INDEX PK_Orders ON Orders(OrderID) PRIMARY KEY (OrderID)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON Orders(CustomerID)
...

三、關鍵技術點解析

系統表/函數	用途
`sysobjects`	驗證物件存在、取得類型 (U=使用者表)
`syscolumns` + `systypes`	欄位中繼資料：名稱、型別、長度、精度、狀態位元 (IDENTITY, NULLABLE)
`syscomments`	取得預設約束定義文字 (cdefault → id)
`sysindexes`	索引中繼資料：名稱、indid (1=叢集)、status 位元 (唯一、主鍵、選項)
`INDEX_COL()`	取得索引指定位置的欄位名稱
位元運算 (`&`)	解析 `status` 旗標：0x02=UNIQUE, 0x08=Nullable, 0x80=Identity, 0x800=Primary Key, 0x01=IGNORE_DUP_KEY, 0x40=ALLOW_DUP_ROW

四、現代替代方案 (SQL Server 2005+)

SMO `Scripter` 類別**：.NET 程式碼產生完整 DDL (含權限、擴充屬性、檢查約束、外鍵)，參考文章 20453。
SSMS 右鍵 → 產生腳本**：GUI 操作、支援多物件、依賴關係排序。
`sp_help` / `sp_helpindex` / `sp_helpconstraint`**：快速查看單一物件結構。
查詢 `INFORMATION_SCHEMA.COLUMNS` / `INDEXES`**：ANSI 標準視圖，跨平台相容性較好。

五、總結

`SP_GET_TABLE_INFO` 展示了**直接操作系統目錄產生 DDL** 的經典技巧，對理解 SQL Server 中繼資料結構、維護舊版系統 (SQL 2000/2005) 極有參考價值。新專案強烈建議使用 **SMO** 或 **SSMS 產生腳本** 功能，功能更完整、維護成本更低。