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 }; });