halopsa_list_columns

Retrieve column details for any HaloPSA database table, including data types, lengths, and nullable status to understand table structure and plan queries effectively.

Instructions

List columns for a specific table in the HaloPSA database using information_schema.columns. Returns detailed column information including data types, max length, and nullable status.

Input Schema

TableJSON Schema

Name	Required	Description	Default
`tableName`	Yes	Table name to get columns for. Example: FAULTS, USERS, SITE
`columnFilter`	No	Optional filter to search for specific column names. Example: "id", "name", "date"

Implementation Reference

src/index.ts:319-385 (handler)

The main execution handler for 'halopsa_list_columns' tool within the CallToolRequestSchema switch statement. Builds dynamic SQL query against INFORMATION_SCHEMA.COLUMNS using provided tableName and optional columnFilter, executes via HaloPSAClient, processes results into structured columnsByTable object, and returns formatted JSON response.

case 'halopsa_list_columns': {
  const { tableName, columnFilter } = args as any;
  
  if (!tableName) {
    throw new Error('Table name is required');
  }
  
  let sql = `
    SELECT 
      c.TABLE_NAME,
      c.COLUMN_NAME,
      c.DATA_TYPE,
      c.CHARACTER_MAXIMUM_LENGTH,
      c.IS_NULLABLE,
      c.COLUMN_DEFAULT
    FROM INFORMATION_SCHEMA.COLUMNS c
  `;
  
  const conditions: string[] = [];
  const escapedTable = tableName.replace(/'/g, "''");
  conditions.push(`LOWER(c.TABLE_NAME) = '${escapedTable.toLowerCase()}'`);
  
  if (columnFilter) {
    const escapedFilter = columnFilter.replace(/'/g, "''");
    conditions.push(`LOWER(c.COLUMN_NAME) LIKE '%${escapedFilter.toLowerCase()}%'`);
  }
  
  if (conditions.length > 0) {
    sql += ' WHERE ' + conditions.join(' AND ');
  }
  
  sql += ' ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION';
  
  result = await haloPSAClient.executeQuery(sql);
  
  const columnsByTable: Record<string, any[]> = {};
  if (result?.report?.rows && Array.isArray(result.report.rows)) {
    result.report.rows.forEach((row: any) => {
      const table = row.TABLE_NAME;
      if (!columnsByTable[table]) {
        columnsByTable[table] = [];
      }
      columnsByTable[table].push({
        name: row.COLUMN_NAME,
        type: row.DATA_TYPE,
        maxLength: row.CHARACTER_MAXIMUM_LENGTH,
        nullable: row.IS_NULLABLE === 'YES',
        default: row.COLUMN_DEFAULT
      });
    });
  }
  
  return {
    content: [{
      type: 'text',
      text: JSON.stringify({
        columnsByTable,
        totalTables: Object.keys(columnsByTable).length,
        totalColumns: result?.report?.rows?.length || 0,
        filters: {
          table: tableName || 'none',
          column: columnFilter || 'none'
        }
      }, null, 2)
    }]
  };
}

src/index.ts:56-73 (schema)

The tool definition and input schema for 'halopsa_list_columns', registered in the tools array used by ListToolsRequestSchema handler. Specifies name, description, and validation schema requiring tableName with optional columnFilter.

{
  name: 'halopsa_list_columns',
  description: 'List columns for a specific table in the HaloPSA database using information_schema.columns. Returns detailed column information including data types, max length, and nullable status.',
  inputSchema: {
    type: 'object',
    properties: {
      tableName: {
        type: 'string',
        description: 'Table name to get columns for. Example: FAULTS, USERS, SITE'
      },
      columnFilter: {
        type: 'string',
        description: 'Optional filter to search for specific column names. Example: "id", "name", "date"'
      }
    },
    required: ['tableName']
  }
},

src/index.ts:279-281 (registration)
General tool registration handler that returns the full tools array (including halopsa_list_columns) when ListToolsRequestSchema is called.
```
server.setRequestHandler(ListToolsRequestSchema, async () => {
  return { tools };
});
```

HaloPSA MCP Server