SQL Server 實用腳本精選：建表、存儲過程、資料庫管理與重命名

1. 建立資料表並用存儲過程批次插入 100 筆測試資料

-- 資料表：身分欄位種子 10000、步長 100
CREATE TABLE teachers (
    id INT PRIMARY KEY NOT NULL IDENTITY(10000,100),
    tea_name VARCHAR(50),
    tea_age INT DEFAULT 20
);

-- 存儲過程：迴圈插入 100 筆
CREATE PROCEDURE insert1000teachers
AS
BEGIN
    DECLARE @tea_name VARCHAR(50), @tea_age INT, @idx INT = 1, @cnt INT = 100;
    WHILE @idx <= @cnt
    BEGIN
        SET @tea_age = 30 * RAND();
        SET @tea_name = 'teacher' + CAST(@idx AS VARCHAR);
        INSERT INTO teachers (tea_name, tea_age) VALUES (@tea_name, @tea_age);
        SET @idx = @idx + 1;
    END
END;
GO

EXEC insert1000teachers;

2. 建立資料庫（指定檔案群組、初始大小、成長模式）

CREATE DATABASE test1
ON PRIMARY (
    NAME = t1_dat,
    FILENAME = 'D:\test_database\1\t1_dat.mdf',
    SIZE = 1MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 15%
),
(
    NAME = t2_dat,
    FILENAME = 'D:\test_database\1\t2_dat.ndf',
    SIZE = 1MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1MB
)
LOG ON (
    NAME = t_log,
    FILENAME = 'D:\test_database\1\t_log.ldf',
    SIZE = 1MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1MB
);

3. 動態調整資料庫檔案與檔案群組

-- 新增資料檔到預設檔案群組
ALTER DATABASE test1 ADD FILE (
    NAME = t3_dat,
    FILENAME = 'D:\test_database\1\t3_dat.ndf',
    SIZE = 1MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1MB
);

-- 新增檔案群組
ALTER DATABASE test1 ADD FILEGROUP filegroup1;

-- 新增資料檔到指定檔案群組
ALTER DATABASE test1 ADD FILE (
    NAME = t4_dat,
    FILENAME = 'D:\test_database\1\t4_dat.ndf'
) TO FILEGROUP filegroup1;

4. 物件重命名系統預存程序

-- 重命名欄位
EXEC sp_rename 'teachers.tea_name', 'tea_name1', 'COLUMN';

-- 重命名資料表
EXEC sp_rename 'dbo.teachers', 'teachers1';

5. 查看存儲過程原始碼

EXEC sp_helptext 'insert1000teachers';

6. 依關鍵字搜尋存儲程序定義

CREATE PROCEDURE getProcedureByBodyWords
    @patpro VARCHAR(100),  -- 要搜尋的關鍵字
    @proname VARCHAR(50) = NULL  -- 選擇性限定程序名稱
AS
BEGIN
    SELECT name
    FROM sysobjects
    WHERE xtype = 'P'
      AND (@proname IS NULL OR name LIKE '%' + @proname + '%')
      AND OBJECT_DEFINITION(id) LIKE '%' + @patpro + '%';
END;

實用提示

• FILEGROWTH 建議使用固定 MB 而非百分比，避免大檔案成長時 I/O 凍結過久。
• 生產環境資料與記錄檔**務必置於不同磁碟/磁碟陣列**，分離隨機與循序 I/O。
• sp_rename 僅修改中繼資料，相依物件（視圖、預存程序、外鍵）需手動同步更新。