d365fo_execute_sql_query
Run SELECT queries against D365FO metadata to analyze data entities, environments, and versions for reporting and insights.
Instructions
Execute a SELECT query against the D365FO metadata database to get insights from cached metadata.
IMPORTANT SAFETY NOTES:
Only SELECT queries are allowed (no INSERT, UPDATE, DELETE, DROP, etc.)
Query results are limited to 1000 rows maximum
Queries timeout after 30 seconds
Some sensitive tables may be restricted
AVAILABLE TABLES AND THEIR PURPOSE:
metadata_environments: D365FO environments and their details
global_versions: Global version registry with hash and reference counts
environment_versions: Links between environments and global versions
data_entities: D365FO data entities metadata
public_entities: Public entity schemas and configurations
entity_properties: Detailed property information for entities
entity_actions: Available OData actions for entities
enumerations: System enumerations and their metadata
enumeration_members: Individual enumeration values and labels
metadata_search_v2: FTS5 search index for metadata
EXAMPLE QUERIES:
Get most used entities by category: SELECT entity_category, COUNT(*) as count FROM data_entities GROUP BY entity_category ORDER BY count DESC
Find entities with most properties: SELECT pe.name, COUNT(ep.id) as property_count FROM public_entities pe LEFT JOIN entity_properties ep ON pe.id = ep.entity_id GROUP BY pe.id ORDER BY property_count DESC LIMIT 10
Analyze environment versions: SELECT me.environment_name, gv.version_hash, ev.detected_at FROM metadata_environments me JOIN environment_versions ev ON me.id = ev.environment_id JOIN global_versions gv ON ev.global_version_id = gv.id
Use this tool to analyze metadata patterns, generate reports, and gain insights into D365FO structure.
Args: query: SQL SELECT query to execute. Must be a SELECT statement only. Query will be validated for safety before execution. limit: Maximum number of rows to return. Default is 100, maximum is 1000. format: Output format for results. 'table' for human-readable format, 'json' for structured data, 'csv' for spreadsheet-compatible format. profile: Configuration profile to use (optional - uses default profile if not specified)
Returns: Dictionary with query results
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| limit | No | ||
| query | Yes | ||
| format | No | table | |
| profile | No | default |