SQL Server MCP

Instructions

Args:
    table: Table name (can include schema: 'dbo.Users' or 'Users')
    id: Primary key value of the row to delete

Returns:
    Dictionary with:
    - status: 'deleted' or error
    - table: Full table name
    - id: The deleted row's ID
    - rows_affected: Number of rows deleted (should be 1)

Implementation Reference

• src/mssql_mcp/tools/crud.py:254-307 (handler)

  The main handler function for the 'delete_row' tool, decorated with @mcp.tool() for automatic registration with the MCP server. It deletes a specified row from a table using its primary key, handles errors, checks read-only mode, and returns a status dictionary.

  @mcp.tool()
  def delete_row(table: str, id: Any) -> dict[str, Any]:
      """Delete a row by primary key.
  
      Args:
          table: Table name (can include schema: 'dbo.Users' or 'Users')
          id: Primary key value of the row to delete
  
      Returns:
          Dictionary with:
          - status: 'deleted' or error
          - table: Full table name
          - id: The deleted row's ID
          - rows_affected: Number of rows deleted (should be 1)
      """
      try:
          manager = get_connection_manager()
          config = manager.config
  
          # Check read-only mode
          if config.read_only:
              return {"error": "Delete operations disabled in read-only mode"}
  
          schema, table_name = parse_table_name(table)
  
          # Get primary key column
          pk_cols = _get_primary_key_columns(schema, table_name)
          if not pk_cols:
              return {"error": f"No primary key found for table {schema}.{table_name}"}
  
          # Build DELETE statement
          query = f"DELETE FROM [{schema}].[{table_name}] WHERE [{pk_cols[0]}] = %s"
  
          affected = manager.execute_non_query(query, (id,))
  
          if affected == 0:
              return {
                  "error": f"No row found with {pk_cols[0]} = {id}",
                  "table": f"{schema}.{table_name}",
              }
  
          return {
              "status": "deleted",
              "table": f"{schema}.{table_name}",
              "id": id,
              "rows_affected": affected,
          }
  
      except QueryError as e:
          logger.error(f"Error deleting row from {table}: {e}")
          return {"error": str(e)}
      except Exception as e:
          logger.error(f"Unexpected error deleting row from {table}: {e}")
          return {"error": str(e)}

• src/mssql_mcp/tools/crud.py:14-39 (helper)

  Helper function used by delete_row (and other CRUD tools) to retrieve the primary key column names for a given table, essential for constructing the WHERE clause in the DELETE statement.

  def _get_primary_key_columns(schema: str, table: str) -> list[str]:
      """Get primary key column(s) for a table.
  
      Args:
          schema: Schema name
          table: Table name
  
      Returns:
          List of primary key column names
      """
      manager = get_connection_manager()
  
      query = """
          SELECT c.COLUMN_NAME
          FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
          JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
              ON tc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
              AND tc.TABLE_SCHEMA = c.TABLE_SCHEMA
              AND tc.TABLE_NAME = c.TABLE_NAME
          WHERE tc.TABLE_SCHEMA = %s
              AND tc.TABLE_NAME = %s
              AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
          ORDER BY c.ORDINAL_POSITION
      """
      rows = manager.execute_query(query, (schema, table))
      return [row["COLUMN_NAME"] for row in rows]

• src/mssql_mcp/tools/__init__.py:4-4 (registration)

  The __init__.py file imports the crud module, which triggers the execution of the @mcp.tool() decorators in crud.py, thereby registering the delete_row tool with the MCP server.

  from . import crud, databases, export, query, stored_procs, tables