DMV Query Reference¶
When to Use DMV Queries¶
Use DMV queries (via the datamodel tool with execute-dmv action) when you need metadata that is NOT accessible through regular datamodel actions:
| Use Case | DMV to Use |
|---|---|
| List all DAX measures with their formulas | TMSCHEMA_MEASURES |
| Discover all relationships (including hidden) | TMSCHEMA_RELATIONSHIPS |
| Impact analysis — what depends on a measure/column | DISCOVER_CALC_DEPENDENCY |
| List all available DMV views on this workbook | DISCOVER_SCHEMA_ROWSETS |
Do NOT use DMV queries for: - Reading regular worksheet data → use range tool - Listing Power Query queries → use powerquery list - Reading PivotTable data → use pivottable tool
SYNTAX: SELECT * FROM $SYSTEM.<SchemaRowset>
LIMITATIONS: - ONLY SELECT * works — specific column selection fails - Some TMSCHEMA views return empty results in Excel's embedded AS
Working DMV Queries (verified)¶
| Query | Returns |
|---|---|
SELECT * FROM $SYSTEM.TMSCHEMA_MEASURES | All DAX measures with formulas |
SELECT * FROM $SYSTEM.TMSCHEMA_RELATIONSHIPS | All relationships between tables |
SELECT * FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY | Calculation dependencies (impact analysis) |
SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS | Database/catalog metadata |
SELECT * FROM $SYSTEM.DISCOVER_SCHEMA_ROWSETS | List all available DMVs |
May Return Empty in Excel¶
TMSCHEMA_TABLES, TMSCHEMA_COLUMNS, TMSCHEMA_PARTITIONS
Full TMSCHEMA Catalog¶
| Category | DMVs |
|---|---|
| Structure | TMSCHEMA_MODEL, TMSCHEMA_TABLES, TMSCHEMA_COLUMNS, TMSCHEMA_HIERARCHIES, TMSCHEMA_LEVELS |
| Measures | TMSCHEMA_MEASURES, TMSCHEMA_KPIS, TMSCHEMA_FORMAT_STRING_DEFINITIONS |
| Relationships | TMSCHEMA_RELATIONSHIPS |
| Security | TMSCHEMA_ROLES, TMSCHEMA_ROLE_MEMBERSHIPS, TMSCHEMA_TABLE_PERMISSIONS, TMSCHEMA_COLUMN_PERMISSIONS |
| Partitions | TMSCHEMA_PARTITIONS, TMSCHEMA_DATA_SOURCES |
| Metadata | TMSCHEMA_ANNOTATIONS, TMSCHEMA_EXTENDED_PROPERTIES, TMSCHEMA_CULTURES, TMSCHEMA_OBJECT_TRANSLATIONS |
| Perspectives | TMSCHEMA_PERSPECTIVES, TMSCHEMA_PERSPECTIVE_TABLES, TMSCHEMA_PERSPECTIVE_COLUMNS, TMSCHEMA_PERSPECTIVE_MEASURES |
| Calculations | TMSCHEMA_CALCULATION_GROUPS, TMSCHEMA_CALCULATION_ITEMS, TMSCHEMA_EXPRESSIONS |
Reference: Microsoft DMV Docs