list-spanner-tables

Lists all tables in a Google Cloud Spanner database to help users understand database structure and manage data organization.

Input Schema

TableJSON Schema

Name	Required	Description	Default
`instanceId`	No	Spanner instance ID (defaults to SPANNER_INSTANCE env var)
`databaseId`	No	Spanner database ID (defaults to SPANNER_DATABASE env var)

Implementation Reference

src/services/spanner/tools.ts:136-205 (handler)

Handler function that executes the list-spanner-tables tool. It connects to the Spanner database using provided or default instanceId and databaseId, queries the information_schema.tables to list all tables along with their column counts, formats the results as a Markdown table, and includes resource links for further exploration.

async ({ instanceId, databaseId }, _extra) => {
  try {
    const projectId = await getProjectId();
    const config = await getSpannerConfig(
      Array.isArray(instanceId) ? instanceId[0] : instanceId,
      Array.isArray(databaseId) ? databaseId[0] : databaseId
    );
    
    const spanner = await getSpannerClient();
    console.log(`Using Spanner client with project ID: ${spanner.projectId} for execute-spanner-query`);
    const instance = spanner.instance(config.instanceId);
    const database = instance.database(config.databaseId);
    
    // Query for tables
    // Execute query to list tables
    const [tablesResult] = await database.run({
      sql: `SELECT t.table_name, 
                (SELECT COUNT(1) FROM information_schema.columns 
                 WHERE table_name = t.table_name) as column_count
          FROM information_schema.tables t
          WHERE t.table_catalog = '' AND t.table_schema = ''
          ORDER BY t.table_name`
    });
    
    if (!tablesResult || tablesResult.length === 0) {
      return {
        content: [{
          type: 'text',
          text: `# Spanner Tables\n\nProject: ${projectId}\nInstance: ${config.instanceId}\nDatabase: ${config.databaseId}\n\nNo tables found in the database.`
        }]
      };
    }
    
    let markdown = `# Spanner Tables\n\nProject: ${projectId}\nInstance: ${config.instanceId}\nDatabase: ${config.databaseId}\n\n`;
    
    // Table header
    markdown += '| Table Name | Column Count |\n';
    markdown += '|------------|-------------|\n';
    
    // Table rows
    for (const row of tablesResult) {
      // Access the row properties directly
      
      // Extract table name and column count
      const tableName = (row as any).table_name as string || 'unknown';
      const columnCount = (row as any).column_count as number || 0;
      
      markdown += `| ${tableName} | ${columnCount} |\n`;
    }
    
    // Add resource links for further exploration
    markdown += '\n## Available Resources\n\n';
    markdown += `- Schema: \`gcp-spanner://${projectId}/${config.instanceId}/${config.databaseId}/schema\`\n`;
    
    for (const row of tablesResult) {
      const tableName = (row as any).table_name as string || 'unknown';
      markdown += `- Table Preview: \`gcp-spanner://${projectId}/${config.instanceId}/${config.databaseId}/tables/${tableName}/preview\`\n`;
    }
    
    return {
      content: [{
        type: 'text',
        text: markdown
      }]
    };
  } catch (error: any) {
    console.error('Error listing Spanner tables:', error);
    throw error;
  }
}

src/services/spanner/tools.ts:132-135 (schema)

Input schema for the list-spanner-tables tool using Zod validation, with optional instanceId and databaseId parameters.

{
  instanceId: z.string().optional().describe('Spanner instance ID (defaults to SPANNER_INSTANCE env var)'),
  databaseId: z.string().optional().describe('Spanner database ID (defaults to SPANNER_DATABASE env var)')
},

src/services/spanner/tools.ts:130-206 (registration)

Registration of the list-spanner-tables tool on the MCP server within the registerSpannerTools function.

server.tool(
  'list-spanner-tables',
  {
    instanceId: z.string().optional().describe('Spanner instance ID (defaults to SPANNER_INSTANCE env var)'),
    databaseId: z.string().optional().describe('Spanner database ID (defaults to SPANNER_DATABASE env var)')
  },
  async ({ instanceId, databaseId }, _extra) => {
    try {
      const projectId = await getProjectId();
      const config = await getSpannerConfig(
        Array.isArray(instanceId) ? instanceId[0] : instanceId,
        Array.isArray(databaseId) ? databaseId[0] : databaseId
      );
      
      const spanner = await getSpannerClient();
      console.log(`Using Spanner client with project ID: ${spanner.projectId} for execute-spanner-query`);
      const instance = spanner.instance(config.instanceId);
      const database = instance.database(config.databaseId);
      
      // Query for tables
      // Execute query to list tables
      const [tablesResult] = await database.run({
        sql: `SELECT t.table_name, 
                  (SELECT COUNT(1) FROM information_schema.columns 
                   WHERE table_name = t.table_name) as column_count
            FROM information_schema.tables t
            WHERE t.table_catalog = '' AND t.table_schema = ''
            ORDER BY t.table_name`
      });
      
      if (!tablesResult || tablesResult.length === 0) {
        return {
          content: [{
            type: 'text',
            text: `# Spanner Tables\n\nProject: ${projectId}\nInstance: ${config.instanceId}\nDatabase: ${config.databaseId}\n\nNo tables found in the database.`
          }]
        };
      }
      
      let markdown = `# Spanner Tables\n\nProject: ${projectId}\nInstance: ${config.instanceId}\nDatabase: ${config.databaseId}\n\n`;
      
      // Table header
      markdown += '| Table Name | Column Count |\n';
      markdown += '|------------|-------------|\n';
      
      // Table rows
      for (const row of tablesResult) {
        // Access the row properties directly
        
        // Extract table name and column count
        const tableName = (row as any).table_name as string || 'unknown';
        const columnCount = (row as any).column_count as number || 0;
        
        markdown += `| ${tableName} | ${columnCount} |\n`;
      }
      
      // Add resource links for further exploration
      markdown += '\n## Available Resources\n\n';
      markdown += `- Schema: \`gcp-spanner://${projectId}/${config.instanceId}/${config.databaseId}/schema\`\n`;
      
      for (const row of tablesResult) {
        const tableName = (row as any).table_name as string || 'unknown';
        markdown += `- Table Preview: \`gcp-spanner://${projectId}/${config.instanceId}/${config.databaseId}/tables/${tableName}/preview\`\n`;
      }
      
      return {
        content: [{
          type: 'text',
          text: markdown
        }]
      };
    } catch (error: any) {
      console.error('Error listing Spanner tables:', error);
      throw error;
    }
  }
);

src/index.ts:140-140 (registration)
Top-level registration call for all Spanner tools, including list-spanner-tables, in the main server setup.
```
registerSpannerTools(server);
```

Google Cloud MCP Server