halopsa_table_info
Retrieve table structure details including columns, data types, nullable fields, and relationships to understand HaloPSA database schema for effective query planning.
Instructions
Get detailed information about a specific HaloPSA table including all columns, data types, nullable fields, and relationship suggestions. Use this to understand table structure before writing queries.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| tableName | Yes | Name of the table to inspect. Example: FAULTS, USERS, SITE, ACTIONS, REQUESTTYPE |
Implementation Reference
- src/index.ts:403-459 (handler)The handler function for 'halopsa_table_info' tool. It validates the tableName input, constructs a SQL query to fetch column information from INFORMATION_SCHEMA.COLUMNS, processes the results into a structured format including position, name, type, maxLength, nullable, and default values. Adds hardcoded common relationships for popular tables and returns formatted JSON with an example query.
case 'halopsa_table_info': { const { tableName } = args as any; if (!tableName) { throw new Error('Table name is required'); } const escapedTable = tableName.replace(/'/g, "''"); const sql = ` SELECT c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, c.IS_NULLABLE, c.COLUMN_DEFAULT, c.ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS c WHERE LOWER(c.TABLE_NAME) = '${escapedTable.toLowerCase()}' ORDER BY c.ORDINAL_POSITION `; result = await haloPSAClient.executeQuery(sql); const columns: any[] = []; if (result?.report?.rows && Array.isArray(result.report.rows)) { result.report.rows.forEach((row: any) => { columns.push({ position: row.ORDINAL_POSITION, name: row.COLUMN_NAME, type: row.DATA_TYPE, maxLength: row.CHARACTER_MAXIMUM_LENGTH, nullable: row.IS_NULLABLE === 'YES', default: row.COLUMN_DEFAULT }); }); } const commonRelationships: Record<string, string[]> = { 'FAULTS': ['USERS (via userid)', 'SITE (via siteid)', 'ACTIONS (via faultid)'], 'USERS': ['FAULTS (via userid)', 'SITE (via siteid)'], 'SITE': ['CLIENT (via clientid)', 'FAULTS (via siteid)', 'USERS (via siteid)'], 'ACTIONS': ['FAULTS (via faultid)', 'USERS (via whoagentid)'], 'CLIENT': ['SITE (via clientid)'] }; return { content: [{ type: 'text', text: JSON.stringify({ table: tableName, columns, columnCount: columns.length, possibleRelationships: commonRelationships[tableName.toUpperCase()] || [], exampleQuery: `SELECT TOP 10 * FROM ${tableName}` }, null, 2) }] }; } - src/index.ts:93-106 (registration)Tool registration entry in the MCP tools array. Defines the name, description, and input schema (requiring 'tableName' parameter) for the 'halopsa_table_info' tool, used by the ListTools handler.
{ name: 'halopsa_table_info', description: 'Get detailed information about a specific HaloPSA table including all columns, data types, nullable fields, and relationship suggestions. Use this to understand table structure before writing queries.', inputSchema: { type: 'object', properties: { tableName: { type: 'string', description: 'Name of the table to inspect. Example: FAULTS, USERS, SITE, ACTIONS, REQUESTTYPE' } }, required: ['tableName'] } }, - src/index.ts:96-105 (schema)Input schema definition for the halopsa_table_info tool, specifying an object with a required 'tableName' string property.
inputSchema: { type: 'object', properties: { tableName: { type: 'string', description: 'Name of the table to inspect. Example: FAULTS, USERS, SITE, ACTIONS, REQUESTTYPE' } }, required: ['tableName'] }