@bluspace/mssql-mcp-server NPM

Microsoft SQL Server MCP Server

A comprehensive Node.js-based Model Context Protocol (MCP) server for Microsoft SQL Server databases. This tool enables AI agents and MCP-compatible clients to interact with SQL Server databases through a rich set of tools for schema exploration, data manipulation, and advanced database operations.

Features

🔍 Schema Exploration

Comprehensive Discovery: List schemas, tables, views, functions, stored procedures, triggers, indexes, and constraints
Detailed Metadata: Get row counts, column information, relationships, and more
Pattern Matching: Filter database objects using SQL wildcards

📊 Data Operations

CRUD Operations: Create, read, update, and delete rows with full transaction support
Query Builder: Build complex SQL queries programmatically without writing raw SQL
Bulk Operations: Efficient bulk insert, update, delete, and upsert operations
Transaction Management: Execute multiple operations atomically with isolation level control

🚀 Advanced Features

Stored Procedures: Execute procedures with input/output parameters
Performance: Connection pooling and optimized batch operations
Security: Permission-based access control and parameterized queries to prevent SQL injection
Easy Deployment: Simple npm/npx installation

Installation

You can run this tool directly via npx:

npx -y @bluspace/mssql-mcp-server \
  --user dbuser --password secret \
  --server localhost --port 1433 --database mydb \
  --permissions all

Usage

The CLI accepts the following parameters:

--user <string> (required): Database user
--password <string> (required): Database password
--server <string> (required): SQL Server hostname or IP address
--port <number> (optional, default: "1433"): Database port
--database <string> (required): Database name
--permissions <read|write|all> (optional, default: "all"): Allowed operations

Available Tools

Schema Exploration Tools (available with `read` permissions)

listSchemas: List all schema names in the database
readSchema: Get detailed table and column information for schemas
listTables: List all tables with metadata (row counts, column counts)
listViews: List all views in the database
listFunctions: List user-defined functions (scalar and table-valued)
listStoredProcedures: List stored procedures with filtering options
listIndexes: Get detailed index information for a table
listConstraints: List constraints (PK, FK, CHECK, UNIQUE, DEFAULT)
listTriggers: List triggers with timing and event information
getTableSchema: Get complete table structure with all column details, keys, and metadata

Query Tools (available with `read` permissions)

queryBuilder: Build and execute complex SQL queries programmatically
executeStoredProcedure: Execute stored procedures with parameters
getStoredProcedureInfo: Get detailed information about a stored procedure

Data Manipulation Tools (available with `write` or `all` permissions)

createRow: Insert a new row into a table
readRows: Read rows with filtering (supports up to 100 rows by default)
updateRow: Update existing rows by primary key
deleteRow: Delete rows by primary key

Bulk Operations (available with `write` or `all` permissions)

bulkInsert: Efficiently insert multiple rows with batching
bulkUpdate: Update multiple rows in batches
bulkDelete: Delete multiple rows in batches
bulkUpsert: Insert or update using SQL Server MERGE

Transaction Tools (available with `write` or `all` permissions)

executeTransaction: Execute multiple operations in a single transaction
beginTransaction: Start a long-running transaction
commitTransaction: Commit an active transaction
rollbackTransaction: Rollback an active transaction

Examples

Query Builder

// Complex query with joins and aggregations
await mcp.call("queryBuilder", {
  select: ["c.CustomerName", "COUNT(o.OrderID) as OrderCount", "SUM(o.Total) as TotalSpent"],
  from: { table: "Customers", alias: "c" },
  joins: [{
    type: "LEFT",
    table: "Orders o",
    on: "c.CustomerID = o.CustomerID"
  }],
  where: [
    { column: "o.OrderDate", operator: ">=", value: "2024-01-01" },
    { column: "c.Country", operator: "IN", values: ["USA", "Canada"] }
  ],
  groupBy: ["c.CustomerID", "c.CustomerName"],
  having: "COUNT(o.OrderID) > 5",
  orderBy: [{ column: "TotalSpent", direction: "DESC" }],
  limit: 10
});

Bulk Operations

// Bulk insert with validation
await mcp.call("bulkInsert", {
  table: "Products",
  rows: productData, // Array of product objects
  batchSize: 1000,
  validateSchema: true
});

Schema Exploration

// Discover foreign key relationships
await mcp.call("listConstraints", {
  table: "Orders",
  type: "FOREIGN KEY"
});

// Find all tables with 'Customer' in the name
await mcp.call("listTables", {
  pattern: "%Customer%"
});

Development

Clone the repository
Install dependencies: npm install
Build the project: npm run build
Run the CLI: npm start -- [options]

Version History

1.1.1

Added getTableSchema tool for comprehensive table structure information
Shows column details, data types, nullability, defaults, identity columns, computed columns
Includes primary keys, foreign keys, and table metadata (size, row count)

1.1.0

Added comprehensive schema exploration tools (tables, views, functions, indexes, constraints, triggers)
Implemented Phase 1 developer features:
- Query Builder for complex SQL queries
- Stored Procedure support with input/output parameters
- Transaction Management with isolation levels
- Bulk Operations (insert, update, delete, upsert)
Enhanced default behaviors (dbo schema, increased row limits)
Improved error handling and logging