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

$3

- 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

$3

- 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

$3

- 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:

``bash
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 (required): Database user
- --password (required): Database password
- --server (required): SQL Server hostname or IP address
- --port (optional, default: "1433"): Database port
- --database (required): Database name
- --permissions (optional, default: "all"): Allowed operations

Available Tools

$3

- 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

$3

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

$3

- 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

$3

- 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

$3

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

$3

- createTable: Create new tables with columns, constraints, and indexes
- alterTable: Modify table structure (add/drop columns, constraints)
- dropTable: Drop tables with optional IF EXISTS clause
- createIndex: Create indexes with various options (unique, clustered, filtered, included columns)
- dropIndex: Drop indexes from tables
- executeQuery: Execute custom SQL queries with parameterized inputs (supports DDL, DML, and other operations)
- executeSqlBatch: Execute multiple SQL statements in sequence with automatic transaction support

Examples

$3


`javascript
// 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
});
`

$3

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

$3

`javascript
// 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%"
});
`

$3


`javascript
// Create a new table with various column types and constraints
await mcp.call("createTable", {
table: "Products",
columns: [
{
name: "ProductID",
type: "int",
nullable: false,
identity: { seed: 1, increment: 1 },
primaryKey: true
},
{
name: "ProductName",
type: "nvarchar(100)",
nullable: false
},
{
name: "Price",
type: "decimal(10,2)",
nullable: false,
check: "Price > 0"
},
{
name: "CategoryID",
type: "int",
nullable: true,
references: {
table: "Categories",
column: "CategoryID",
onDelete: "SET NULL"
}
},
{
name: "CreatedDate",
type: "datetime2",
nullable: false,
default: "GETDATE()"
}
],
constraints: {
uniqueKeys: [{
columns: ["ProductName"]
}],
checks: [{
expression: "LEN(ProductName) > 0",
name: "CHK_ProductName_NotEmpty"
}]
}
});

// Create an index with included columns
await mcp.call("createIndex", {
indexName: "IX_Products_CategoryPrice",
table: "Products",
columns: [
{ name: "CategoryID", direction: "ASC" },
{ name: "Price", direction: "DESC" }
],
include: ["ProductName"],
where: "Price > 10"
});

// Execute custom DDL using executeQuery
await mcp.call("executeQuery", {
query: "CREATE VIEW vw_ExpensiveProducts AS SELECT * FROM Products WHERE Price > 100"
});
`

$3


`javascript
// Execute multiple statements in a transaction (default behavior)
await mcp.call("executeSqlBatch", {
statements: [
"DELETE FROM OrderItems WHERE OrderID = 123",
"DELETE FROM Orders WHERE OrderID = 123"
]
});

// Execute with parameterized queries
await mcp.call("executeSqlBatch", {
statements: [
{
sql: "INSERT INTO Categories (Name) VALUES (@name)",
parameters: { name: "Electronics" }
},
{
sql: "INSERT INTO Products (Name, CategoryID, Price) VALUES (@name, @catId, @price)",
parameters: { name: "Laptop", catId: 1, price: 999.99 }
}
]
});

// Execute without transaction for independent operations
await mcp.call("executeSqlBatch", {
statements: [
"UPDATE Statistics SET LastUpdated = GETDATE()",
"EXEC sp_UpdateCache",
"DELETE FROM TempData WHERE CreatedDate < DATEADD(day, -7, GETDATE())"
],
useTransaction: false,
stopOnError: false // Continue even if one statement fails
});

// Complex migration with DDL and DML
await mcp.call("executeSqlBatch", {
statements: [
"ALTER TABLE Users ADD Email nvarchar(255)",
"UPDATE Users SET Email = Username + '@example.com' WHERE Email IS NULL",
"ALTER TABLE Users ALTER COLUMN Email nvarchar(255) NOT NULL",
"CREATE UNIQUE INDEX IX_Users_Email ON Users(Email)"
],
isolationLevel: "SERIALIZABLE" // Use highest isolation for schema changes
});
`

Development

1. Clone the repository
2. Install dependencies: npm install
3. Build the project: npm run build
4. Run the CLI: npm start -- [options]

Version History

$3


- Added comprehensive DDL (Data Definition Language) tools:
- createTable: Create tables with full constraint and column options
- alterTable: Modify table structure (add/drop columns, constraints)
- dropTable: Drop tables with IF EXISTS support
- createIndex: Create indexes with advanced options (filtered, included columns)
- dropIndex: Drop indexes safely
- executeQuery: Execute custom SQL queries with parameterized inputs
- executeSqlBatch: Execute multiple SQL statements with automatic transaction support
- Fixed queryBuilder limitation - DDL operations now supported via dedicated tools
- Fixed transaction tool complexity - new executeSqlBatch provides simple multi-statement execution

$3

- 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)

$3

- 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

$3

- Initial release with basic CRUD operations and schema inspection

License

MIT