Microsoft SQL Server MCP (Model Context Protocol) Server - AI-powered database interaction tool
npm install @bluspace/mssql-mcp-serverA 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.
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
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
- listSchemas: List all schema names in the databasereadSchema
- : Get detailed table and column information for schemaslistTables
- : List all tables with metadata (row counts, column counts)listViews
- : List all views in the databaselistFunctions
- : List user-defined functions (scalar and table-valued)listStoredProcedures
- : List stored procedures with filtering optionslistIndexes
- : Get detailed index information for a tablelistConstraints
- : List constraints (PK, FK, CHECK, UNIQUE, DEFAULT)listTriggers
- : List triggers with timing and event informationgetTableSchema
- : Get complete table structure with all column details, keys, and metadata
- queryBuilder: Build and execute complex SQL queries programmaticallyexecuteStoredProcedure
- : Execute stored procedures with parametersgetStoredProcedureInfo
- : Get detailed information about a stored procedure
- createRow: Insert a new row into a tablereadRows
- : Read rows with filtering (supports up to 100 rows by default)updateRow
- : Update existing rows by primary keydeleteRow
- : Delete rows by primary key
- bulkInsert: Efficiently insert multiple rows with batchingbulkUpdate
- : Update multiple rows in batchesbulkDelete
- : Delete multiple rows in batchesbulkUpsert
- : Insert or update using SQL Server MERGE
- executeTransaction: Execute multiple operations in a single transactionbeginTransaction
- : Start a long-running transactioncommitTransaction
- : Commit an active transactionrollbackTransaction
- : Rollback an active transaction
- createTable: Create new tables with columns, constraints, and indexesalterTable
- : Modify table structure (add/drop columns, constraints)dropTable
- : Drop tables with optional IF EXISTS clausecreateIndex
- : Create indexes with various options (unique, clustered, filtered, included columns)dropIndex
- : Drop indexes from tablesexecuteQuery
- : Execute custom SQL queries with parameterized inputs (supports DDL, DML, and other operations)executeSqlBatch
- : Execute multiple SQL statements in sequence with automatic transaction support
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 informationMIT