MSSQL MCP Server

Instructions

Implementation Reference

• src/index.ts:279-350 (handler)

  Executes the 'describe-table' tool: validates connection and database, sanitizes input names, fetches columns via MSSQLManager, formats and returns the table structure as a text list.

  async ({ tableName, schemaName }) => {
    try {
      if (!mssqlManager.isConnected()) {
        return {
          content: [
            {
              type: 'text' as const,
              text: '錯誤: 尚未連接到資料庫伺服器。請先使用 connect-database 工具建立連接。'
            }
          ]
        }
      }
  
      const currentDb = mssqlManager.getCurrentDatabase()
      if (!currentDb) {
        return {
          content: [
            {
              type: 'text' as const,
              text: '錯誤: 尚未選擇資料庫。請先使用 switch-database 工具選擇資料庫。'
            }
          ]
        }
      }
  
      const sanitizedTableName = sanitizeTableName(tableName)
      const sanitizedSchemaName = sanitizeTableName(schemaName)
  
      const columns = await mssqlManager.getTableColumns(sanitizedTableName, sanitizedSchemaName)
  
      if (columns.length === 0) {
        return {
          content: [
            {
              type: 'text' as const,
              text: `找不到資料表 ${sanitizedSchemaName}.${sanitizedTableName}。`
            }
          ]
        }
      }
  
      const columnList = columns.map(col => {
        let typeInfo = col.data_type
        if (col.character_maximum_length) {
          typeInfo += `(${col.character_maximum_length})`
        } else if (col.numeric_precision && col.numeric_scale !== null) {
          typeInfo += `(${col.numeric_precision},${col.numeric_scale})`
        }
  
        const nullable = col.is_nullable === 'YES' ? 'NULL' : 'NOT NULL'
        return `- ${col.column_name}: ${typeInfo} ${nullable}`
      }).join('\n')
  
      return {
        content: [
          {
            type: 'text' as const,
            text: `資料表 ${sanitizedSchemaName}.${sanitizedTableName} 的欄位結構:\n${columnList}`
          }
        ]
      }
    } catch (error) {
      return {
        content: [
          {
            type: 'text' as const,
            text: `查看資料表結構失敗: ${error instanceof Error ? error.message : String(error)}`
          }
        ]
      }
    }
  }

• src/index.ts:271-278 (schema)

  Zod input schema defining parameters for tableName (required string) and schemaName (optional string, default 'dbo').

  {
    title: '查看表格結構',
    description: '查看指定資料表的欄位結構',
    inputSchema: {
      tableName: z.string().describe('資料表名稱'),
      schemaName: z.string().optional().default('dbo').describe('結構描述名稱 (預設: dbo)'),
    }
  },

• src/index.ts:269-351 (registration)

  Registers the 'describe-table' tool on the MCP server with title, description, input schema, and handler function.

  server.registerTool(
    'describe-table',
    {
      title: '查看表格結構',
      description: '查看指定資料表的欄位結構',
      inputSchema: {
        tableName: z.string().describe('資料表名稱'),
        schemaName: z.string().optional().default('dbo').describe('結構描述名稱 (預設: dbo)'),
      }
    },
    async ({ tableName, schemaName }) => {
      try {
        if (!mssqlManager.isConnected()) {
          return {
            content: [
              {
                type: 'text' as const,
                text: '錯誤: 尚未連接到資料庫伺服器。請先使用 connect-database 工具建立連接。'
              }
            ]
          }
        }
  
        const currentDb = mssqlManager.getCurrentDatabase()
        if (!currentDb) {
          return {
            content: [
              {
                type: 'text' as const,
                text: '錯誤: 尚未選擇資料庫。請先使用 switch-database 工具選擇資料庫。'
              }
            ]
          }
        }
  
        const sanitizedTableName = sanitizeTableName(tableName)
        const sanitizedSchemaName = sanitizeTableName(schemaName)
  
        const columns = await mssqlManager.getTableColumns(sanitizedTableName, sanitizedSchemaName)
  
        if (columns.length === 0) {
          return {
            content: [
              {
                type: 'text' as const,
                text: `找不到資料表 ${sanitizedSchemaName}.${sanitizedTableName}。`
              }
            ]
          }
        }
  
        const columnList = columns.map(col => {
          let typeInfo = col.data_type
          if (col.character_maximum_length) {
            typeInfo += `(${col.character_maximum_length})`
          } else if (col.numeric_precision && col.numeric_scale !== null) {
            typeInfo += `(${col.numeric_precision},${col.numeric_scale})`
          }
  
          const nullable = col.is_nullable === 'YES' ? 'NULL' : 'NOT NULL'
          return `- ${col.column_name}: ${typeInfo} ${nullable}`
        }).join('\n')
  
        return {
          content: [
            {
              type: 'text' as const,
              text: `資料表 ${sanitizedSchemaName}.${sanitizedTableName} 的欄位結構:\n${columnList}`
            }
          ]
        }
      } catch (error) {
        return {
          content: [
            {
              type: 'text' as const,
              text: `查看資料表結構失敗: ${error instanceof Error ? error.message : String(error)}`
            }
          ]
        }
      }
    }
  )

• src/database.ts:158-178 (helper)

  MSSQLManager method that queries INFORMATION_SCHEMA.COLUMNS to retrieve column information for the specified table and schema.

  async getTableColumns(tableName: string, schemaName: string = 'dbo'): Promise<ColumnInfo[]> {
    if (!this.currentDatabase) {
      throw new Error('尚未選擇資料庫')
    }
  
    const query = `
      SELECT 
        COLUMN_NAME as column_name,
        DATA_TYPE as data_type,
        IS_NULLABLE as is_nullable,
        CHARACTER_MAXIMUM_LENGTH as character_maximum_length,
        NUMERIC_PRECISION as numeric_precision,
        NUMERIC_SCALE as numeric_scale
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_NAME = '${tableName}' AND TABLE_SCHEMA = '${schemaName}'
      ORDER BY ORDINAL_POSITION
    `
    
    const result = await this.executeQuery(query)
    return result.recordset as ColumnInfo[]
  }

• src/types.ts:44-51 (schema)

  TypeScript interface defining the structure of column information returned by getTableColumns.

  export interface ColumnInfo {
    column_name: string
    data_type: string
    is_nullable: string
    character_maximum_length: number | null
    numeric_precision: number | null
    numeric_scale: number | null
  }