MSSQL 工具包

使用 sqlcmd 查询和探索 Microsoft SQL Server 数据库。

设置

环境变量

请在你的 OpenClaw .env 文件中设置以下内容：

MSSQL_HOST=your-server,port    # 例如：10.0.0.1,1433
MSSQL_USER=your_username
MSSQL_PASSWORD=your_password
MSSQL_DB=your_database

sqlcmd 路径

如果 sqlcmd 已在系统 PATH 中，请直接使用。若安装在非标准位置，请设置：

MSSQL_SQLCMD=/opt/mssql-tools18/bin/sqlcmd

如果未设置 MSSQL_SQLCMD，将默认使用 sqlcmd。

连接命令

${MSSQL_SQLCMD:-sqlcmd} -S $MSSQL_HOST -U $MSSQL_USER -P $MSSQL_PASSWORD -d $MSSQL_DB -C -W -Q "YOUR_QUERY"

必需参数

• -C 信任服务器证书（现代 MSSQL 必须使用）
• -Q 执行查询后退出
• -W 去除尾部空白字符

有用参数

• -s "," 使用逗号分隔列（类似 CSV 输出）
• -w 999 宽输出（防止换行）
• -h -1 隐藏列标题（适用于脚本）

模式检查

列出所有模式

${MSSQL_SQLCMD:-sqlcmd} -S $MSSQL_HOST -U $MSSQL_USER -P $MSSQL_PASSWORD -d $MSSQL_DB -C -W -Q "SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_SCHEMA"

列出所有表（含模式）

${MSSQL_SQLCMD:-sqlcmd} -S $MSSQL_HOST -U $MSSQL_USER -P $MSSQL_PASSWORD -d $MSSQL_DB -C -W -Q "SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME"

按关键词搜索表

${MSSQL_SQLCMD:-sqlcmd} -S $MSSQL_HOST -U $MSSQL_USER -P $MSSQL_PASSWORD -d $MSSQL_DB -C -W -Q "SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME LIKE '%KEYWORD%' ORDER BY TABLE_SCHEMA, TABLE_NAME"

描述表字段

${MSSQL_SQLCMD:-sqlcmd} -S $MSSQL_HOST -U $MSSQL_USER -P $MSSQL_PASSWORD -d $MSSQL_DB -C -W -Q "SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='SCHEMA' AND TABLE_NAME='TABLE' ORDER BY ORDINAL_POSITION"

统计行数

${MSSQL_SQLCMD:-sqlcmd} -S $MSSQL_HOST -U $MSSQL_USER -P $MSSQL_PASSWORD -d $MSSQL_DB -C -W -Q "SELECT COUNT(*) AS row_count FROM schema_name.table_name"

获取样本数据

${MSSQL_SQLCMD:-sqlcmd} -S $MSSQL_HOST -U $MSSQL_USER -P $MSSQL_PASSWORD -d $MSSQL_DB -C -W -Q "SELECT TOP 5 * FROM schema_name.table_name"

列出索引

${MSSQL_SQLCMD:-sqlcmd} -S $MSSQL_HOST -U $MSSQL_USER -P $MSSQL_PASSWORD -d $MSSQL_DB -C -W -Q "SELECT i.name AS index_name, i.type_desc, STRING_AGG(c.name, ', ') AS columns FROM sys.indexes i JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE i.object_id = OBJECT_ID('schema_name.table_name') GROUP BY i.name, i.type_desc"

查询模式

聚合查询

SELECT department, COUNT(*) AS cnt, SUM(amount) AS total
FROM schema_name.table_name
GROUP BY department
HAVING SUM(amount) > 1000
ORDER BY total DESC;

CTE（公共表表达式）

WITH monthly AS (
    SELECT
        YEAR(created_at) AS yr,
        MONTH(created_at) AS mo,
        SUM(amount) AS total
    FROM schema_name.table_name
    GROUP BY YEAR(created_at), MONTH(created_at)
)
SELECT * FROM monthly ORDER BY yr, mo;

窗口函数

SELECT *,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM schema_name.table_name;

日期过滤

-- 今天
WHERE CAST(date_column AS DATE) = CAST(GETDATE() AS DATE)

-- 本月
WHERE YEAR(date_column) = YEAR(GETDATE()) AND MONTH(date_column) = MONTH(GETDATE())

-- 近7天
WHERE date_column >= DATEADD(DAY, -7, GETDATE())

-- 日期范围
WHERE date_column BETWEEN '2026-01-01' AND '2026-01-31'

数据透视（PIVOT）

SELECT *
FROM (
    SELECT category, region, revenue
    FROM schema_name.table_name
) AS src
PIVOT (
    SUM(revenue) FOR region IN ([North], [South], [East], [West])
) AS pvt;

安全规则

1. 默认只读 — 仅执行 SELECT 操作，除非用户明确要求修改数据
2. 始终使用 TOP 或 OFFSET-FETCH — 对大表避免执行无限制的 SELECT *
3. 绝不暴露凭据 — 不打印、不输出或不查看任何环境变量值或连接字符串
4. 未获用户确认前不执行 DROP、DELETE、TRUNCATE 或 ALTER 操作
5. 写入操作必须使用事务：BEGIN TRAN ... COMMIT / ROLLBACK
6. 使用 TOP 时始终包含 ORDER BY，确保结果可预测
7. 表名必须显式指定模式 — 使用 schema_name.table_name，而非仅 table_name