MSSQL MCP Server

Instructions

Implementation Reference

• src/index.ts:100-148 (handler)

  MCP tool handler for 'list-databases': validates connection, retrieves databases via MSSQLManager.getDatabases(), formats output as a text list with count, names, creation dates, and collations, handles errors.

  async () => {
    try {
      if (!mssqlManager.isConnected()) {
        return {
          content: [
            {
              type: 'text' as const,
              text: '錯誤: 尚未連接到資料庫伺服器。請先使用 connect-database 工具建立連接。'
            }
          ]
        }
      }
  
      const databases = await mssqlManager.getDatabases()
  
      if (databases.length === 0) {
        return {
          content: [
            {
              type: 'text' as const,
              text: '沒有找到使用者資料庫。'
            }
          ]
        }
      }
  
      const databaseList = databases.map(db =>
        `- ${db.name} (建立時間: ${new Date(db.create_date).toLocaleDateString()}, 定序: ${db.collation_name})`
      ).join('\n')
  
      return {
        content: [
          {
            type: 'text' as const,
            text: `找到 ${databases.length} 個資料庫:\n${databaseList}`
          }
        ]
      }
    } catch (error) {
      return {
        content: [
          {
            type: 'text' as const,
            text: `列出資料庫失敗: ${error instanceof Error ? error.message : String(error)}`
          }
        ]
      }
    }
  }

• src/types.ts:29-34 (schema)

  Type definition for DatabaseInfo, used in getDatabases() return type and tool output formatting.

  export interface DatabaseInfo {
    name: string
    database_id: number
    create_date: string
    collation_name: string
  }

• src/index.ts:94-149 (registration)

  Registers the 'list-databases' tool with the MCP server using server.registerTool, defining title, description (no inputSchema), and inline handler.

  server.registerTool(
    'list-databases',
    {
      title: '列出資料庫',
      description: '列出伺服器上的所有使用者資料庫'
    },
    async () => {
      try {
        if (!mssqlManager.isConnected()) {
          return {
            content: [
              {
                type: 'text' as const,
                text: '錯誤: 尚未連接到資料庫伺服器。請先使用 connect-database 工具建立連接。'
              }
            ]
          }
        }
  
        const databases = await mssqlManager.getDatabases()
  
        if (databases.length === 0) {
          return {
            content: [
              {
                type: 'text' as const,
                text: '沒有找到使用者資料庫。'
              }
            ]
          }
        }
  
        const databaseList = databases.map(db =>
          `- ${db.name} (建立時間: ${new Date(db.create_date).toLocaleDateString()}, 定序: ${db.collation_name})`
        ).join('\n')
  
        return {
          content: [
            {
              type: 'text' as const,
              text: `找到 ${databases.length} 個資料庫:\n${databaseList}`
            }
          ]
        }
      } catch (error) {
        return {
          content: [
            {
              type: 'text' as const,
              text: `列出資料庫失敗: ${error instanceof Error ? error.message : String(error)}`
            }
          ]
        }
      }
    }
  )

• src/database.ts:118-132 (helper)

  MSSQLManager.getDatabases() helper method: executes SQL query on sys.databases to list user databases (excludes system DBs), returns typed DatabaseInfo[] via executeQuery.

  async getDatabases(): Promise<DatabaseInfo[]> {
    const query = `
      SELECT 
        name,
        database_id,
        create_date,
        collation_name
      FROM sys.databases 
      WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
      ORDER BY name
    `
    
    const result = await this.executeQuery(query)
    return result.recordset as DatabaseInfo[]
  }