SQL Server 2005 SQLCMD 命令列工具完整實戰指南：互動、腳本、變數與自動化

前言

sqlcmd 是 SQL Server 2005 引入的新一代命令列工具，取代舊版 osql 與 isql，支援互動模式、腳本執行、變數替換、輸出導向、錯誤處理、效能追蹤等企業級自動化需求。本文系統整理核心用法與實戰技巧。

1. 互動模式基本操作

C:\> sqlcmd
1> SELECT name FROM sys.databases;
2> GO
Changed database context to 'master'.
name
------------------------------
master
tempdb
model
msdb
(4 rows affected)
1> :ServerList
SERVERS:
MYSERVER\INSTANCE1
MYSERVER\INSTANCE2
1> :Help
... 顯示所有內部命令 ...
1> QUIT

常用內部命令：

2. 執行腳本檔與輸出導向

sqlcmd -i script.sql -o output.txt


sqlcmd
1> :OUT results.txt
2> :r script.sql
3> GO

參數說明：

• -i：輸入腳本檔
• -o：輸出結果檔
• -e：回顯輸入腳本內容
• -h：標題列重複間隔列數
• -s：資料欄分隔字元 (預設空白)
• -w：螢幕寬度 (預設 80)
• -W：移除尾端空白
• -k：保留控制字元

3. 腳本變數：傳入與內部設定

3.1 命令列傳入變數 (-v)

SET NOCOUNT ON;
SELECT $(Cols) FROM $(TableName);
GO


sqlcmd -i script.sql -o out.txt -v Cols="name,object_id,create_date" TableName="sys.objects"

3.2 腳本內部設定 (:SETVAR)

USE master;
BACKUP DATABASE [$(db)] TO DISK='$(file)';


:SETVAR db msdb
:SETVAR file C:\Backup\msdb.bak
:r backuptemplate.sql

:LISTVAR 可檢視目前所有變數 (含環境變數衍生)。

4. 環境變數對應表 (可預設預設值)

5. 進階自動化腳本範例：每日備份輪替

:SETVAR dbname MyDB
:SETVAR bakpath D:\Backup
:SETVAR dateval $(ESCAPE_SQUOTE($(DATE)))  
USE master;
DECLARE @fn NVARCHAR(260) = '$(bakpath)\$(dbname)_$(dateval).bak';
BACKUP DATABASE [$(dbname)] TO DISK = @fn WITH INIT, COMPRESSION, CHECKSUM;
GO

搭配 Windows 排程器 / SQL Agent CmdExec 作業：

sqlcmd -S . -E -i daily_backup.sql -v dateval=%DATE:~-10,4%%DATE:~-4,2%%DATE:~-7,2%

6. 錯誤處理與結束代碼

• :ON ERROR EXIT：遇錯誤立即結束，回傳錯誤碼。
• :ON ERROR IGNORE：忽略錯誤繼續 (預設)。
• 結束代碼：0=成功，1=錯誤，其他=特定錯誤類別。
• 搭配 -b 參數：批次模式下遇錯誤立即結束並回傳錯誤碼。

7. 實務提醒

1. 編碼：腳本檔儲存為 UTF-8 with BOM 或 Unicode，避免中文亂碼；或使用 -f 65001 (UTF-8) / -f 1200 (Unicode) 指定代碼頁。
2. 安全性：避免在命令列明文傳遞密碼 (-P)，優先使用整合驗證 (-E) 或 sqlcmd -U user -P * 互動輸入。
3. 大量資料：搭配 BCP 或 BULK INSERT 效能更佳；sqlcmd 適合 DDL、維護、小量資料搬移。
4. 版本相容：sqlcmd 可連線 SQL Server 2000+，但部分功能 (如 :CONNECT MARS) 需 2005+。