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

SQL Server 實用腳本集錦：結構文件化、資料字典匯出、INSERT 產生器

編輯：關於SqlServer

腳本一：完整資料字典查詢 (含主鍵、身分識別、預設值、說明)

SELECT
    CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
    a.colorder AS 欄位序號,
    a.name AS 欄位名,
    CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 識別欄,
    CASE WHEN EXISTS (
        SELECT 1 FROM sysobjects so
        JOIN sysindexes si ON so.id = si.id
        JOIN sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid
        WHERE so.xtype = 'PK' AND so.name = si.name
          AND sik.colid = a.colid AND sik.id = a.id
    ) THEN '√' ELSE '' END AS 主鍵,
    b.name AS 資料類型,
    a.length AS 位元組長度,
    COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度,
    ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小數位數,
    CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允許空值,
    ISNULL(e.text, '') AS 預設值,
    ISNULL(g.[value], '') AS 欄位說明
FROM syscolumns a
JOIN systypes b ON a.xtype = b.xusertype
JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sysproperties g ON a.id = g.id AND a.colid = g.smallid
ORDER BY a.id, a.colorder;

輸出至 Excel： 查詢分析器 → 結果網格右鍵 →「全部複製」→ 貼上 Excel；或用 `bcp` 匯出 (見腳本二)。

腳本二：全資料庫結構 + 範例資料匯出至 Excel (含最大長度、樣本值)

SET NOCOUNT ON;
SET ANSI_NULLS OFF;

-- 1. 收集所有使用者資料表欄位基本資訊
SELECT d.name AS TableName, a.name AS FieldName, b.name AS TypeName,
       a.length AS Length, a.isnullable AS IsNull
INTO #t
FROM syscolumns a
JOIN systypes b ON a.xtype = b.xusertype
JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U';

-- 2. 游標逐欄位計算最大實際長度與樣本值
DECLARE @tbl NVARCHAR(200), @fld NVARCHAR(200), @sql NVARCHAR(4000),
        @maxlen INT, @sample NVARCHAR(40);

DECLARE cur CURSOR FOR SELECT TableName, FieldName FROM #t;
OPEN cur;
FETCH NEXT FROM cur INTO @tbl, @fld;

-- 結果暫存表
SELECT TOP 1
    TableName, FieldName, TypeName, Length, IsNull,
    0 AS MaxLenUsed, '' AS Sample, '' AS Comment
INTO #tc FROM #t WHERE 1=0;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 最大長度
    SET @sql = N'SELECT @maxlen = MAX(LEN(CAST(' + QUOTENAME(@fld) + N' AS NVARCHAR(MAX)))) FROM ' + QUOTENAME(@tbl);
    EXEC sp_executesql @sql, N'@maxlen INT OUTPUT', @maxlen OUTPUT;

    -- 樣本值 (取最大長度那一列)
    IF @maxlen > 0
    BEGIN
        SET @sql = N'SELECT TOP 1 @sample = CAST(' + QUOTENAME(@fld) + N' AS NVARCHAR(40)) FROM ' + QUOTENAME(@tbl) +
                   N' WHERE LEN(CAST(' + QUOTENAME(@fld) + N' AS NVARCHAR(MAX))) = @maxlen';
        EXEC sp_executesql @sql, N'@maxlen INT, @sample NVARCHAR(40) OUTPUT', @maxlen, @sample OUTPUT;
    END
    ELSE SET @sample = '';

    INSERT INTO #tc VALUES (@tbl, @fld,
        (SELECT TypeName FROM #t WHERE TableName=@tbl AND FieldName=@fld),
        (SELECT Length FROM #t WHERE TableName=@tbl AND FieldName=@fld),
        (SELECT IsNull FROM #t WHERE TableName=@tbl AND FieldName=@fld),
        @maxlen, @sample, '');

    FETCH NEXT FROM cur INTO @tbl, @fld;
END
CLOSE cur; DEALLOCATE cur;

-- 3. 匯出 Excel (需啟用 xp_cmdshell、具備權限)
DECLARE @db NVARCHAR(60) = DB_NAME(), @bcp NVARCHAR(1000);
SET @bcp = 'bcp "SELECT * FROM ##tx" queryout "C:\' + @db + '_Dict.xls" -w -C936 -T -S' + @@SERVERNAME;
-- 先將 #tc 複製到全域暫存表 ##tx
SELECT * INTO ##tx FROM #tc ORDER BY TableName, FieldName;
EXEC master..xp_cmdshell @bcp;

DROP TABLE #t, #tc, ##tx;
SET ANSI_NULLS ON;

注意： `xp_cmdshell` 預設停用，生產環境建議改用 SSIS/BCP 工具/應用程式匯出。

腳本三：根據現有資料產生 INSERT 陳述式 (spGenInsertSQL)

CREATE PROCEDURE dbo.spGenInsertSQL @tablename NVARCHAR(256)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX) = N'', @vals NVARCHAR(MAX) = N'';

    SELECT @sql = @sql + QUOTENAME(name) + N',',
           @vals = @vals +
               CASE
                   WHEN xtype IN (48,52,56,59,60,62,104,106,108,122,127) -- 數值/日期/二進位
                       THEN N'CASE WHEN ' + QUOTENAME(name) + N' IS NULL THEN ''NULL'' ELSE CAST(' + QUOTENAME(name) + N' AS NVARCHAR(MAX)) END'
                   WHEN xtype IN (58,61) -- datetime/smalldatetime
                       THEN N'CASE WHEN ' + QUOTENAME(name) + N' IS NULL THEN ''NULL'' ELSE '''''''' + CONVERT(NVARCHAR(23), ' + QUOTENAME(name) + N', 121) + '''''''' END'
                   WHEN xtype IN (167,175) -- varchar/char
                       THEN N'CASE WHEN ' + QUOTENAME(name) + N' IS NULL THEN ''NULL'' ELSE '''''''' + REPLACE(' + QUOTENAME(name) + N', '''''''', '''''''''''') + '''''''' END'
                   WHEN xtype IN (231,239) -- nvarchar/nchar
                       THEN N'CASE WHEN ' + QUOTENAME(name) + N' IS NULL THEN ''NULL'' ELSE N'''''''' + REPLACE(' + QUOTENAME(name) + N', '''''''', '''''''''''') + '''''''' END'
                   ELSE N'''NULL'''
               END + N','
    FROM syscolumns
    WHERE id = OBJECT_ID(@tablename) AND xtype NOT IN (34,35,36,189); -- 排除 image/text/ntext/timestamp

    SET @sql = N'SELECT ''INSERT INTO ' + QUOTENAME(@tablename) + N' (' + LEFT(@sql, LEN(@sql)-1) + N') VALUES (' + LEFT(@vals, LEN(@vals)-1) + N')'' FROM ' + QUOTENAME(@tablename);
    EXEC sp_executesql @sql;
END
GO

-- 使用：EXEC dbo.spGenInsertSQL 'MyTable';