Skip to content

Instantly share code, notes, and snippets.

@mlongoria
Last active September 27, 2022 12:51
Show Gist options
  • Save mlongoria/a9a0bff0f51a5e9c200b9c8b378d79da to your computer and use it in GitHub Desktop.
Save mlongoria/a9a0bff0f51a5e9c200b9c8b378d79da to your computer and use it in GitHub Desktop.
//list available DMVs
Select * from $SYSTEM.DBSCHEMA_TABLES where table_type = 'Schema' order by table_name
//Useful DMVs for 2016 SSAS Tabular Models
Select * from $SYSTEM.TMSCHEMA_ATTRIBUTE_HIERARCHY_STORAGES //distinct data count for each column
Select * from $SYSTEM.TMSCHEMA_ATTRIBUTE_HIERARCHIES //ties hierarchy id to column
SELECT * from $SYSTEM.TMSCHEMA_COLUMN_STORAGES //has order by column, row count is inaccurate
Select * from $SYSTEM.TMSCHEMA_COLUMNS //column name, ID for table, data type, category, hidden, iskey, isunique, is nullable, summarize by, expression for calc columns, hierarchy id, refresh time, modify time. source provider type, display folder
SELECT * from $SYSTEM.TMSCHEMA_DATA_SOURCES //connection string, account, impersonation mode, name
Select * from $SYSTEM.TMSCHEMA_HIERARCHIES //hierarchy name, display folder
Select * from $SYSTEM.TMSCHEMA_HIERARCHY_STORAGES //user hierarchy definitions
Select * from $SYSTEM.TMSCHEMA_KPIS //KPI definition
Select * from $SYSTEM.TMSCHEMA_LEVELS //hierarchy level and model source column
Select * from $SYSTEM.TMSCHEMA_MEASURES //measure and expressions, formatt, hidden, display folder
Select * from $SYSTEM.TMSCHEMA_MODEL //name of each model
Select * from $SYSTEM.TMSCHEMA_PARTITIONS //source queries for each table
Select * from $SYSTEM.TMSCHEMA_PERSPECTIVE_COLUMNS //perspective table to perspective column maps
Select * from $SYSTEM.TMSCHEMA_PERSPECTIVE_HIERARCHIES // perspective table to hierarchy id map
Select * from $SYSTEM.TMSCHEMA_PERSPECTIVE_MEASURES // perspective table id to measure id map
Select * from $SYSTEM.TMSCHEMA_PERSPECTIVE_TABLES //perspective to table map
Select * from $SYSTEM.TMSCHEMA_PERSPECTIVES //list of perspectives
Select * from $SYSTEM.TMSCHEMA_RELATIONSHIPS //active , type, crossfilter, table, colmn, cardinality
Select * from $SYSTEM.TMSCHEMA_ROLE_MEMBERSHIPS //member name, ID, Modified time, role id,
Select * from $SYSTEM.TMSCHEMA_ROLES //model, name, description, permission
Select * from $SYSTEM.TMSCHEMA_TABLE_PERMISSIONS // role, rable, filter expression, modified time
Select * from $SYSTEM.TMSCHEMA_TABLES //tables, description, hidden,
Select * from $SYSTEM.DBSCHEMA_CATALOGS //Catalog name, description, compatibility level, type, database id, version
//Other DMVs
Select * from $SYSTEM.TMSCHEMA_ANNOTATIONS
Select * from $SYSTEM.TMSCHEMA_COLUMN_PARTITION_STORAGES
Select * from $SYSTEM.TMSCHEMA_CULTURES
Select * from $SYSTEM.TMSCHEMA_DICTIONARY_STORAGES
Select * from $SYSTEM.TMSCHEMA_LINGUISTIC_METADATA
Select * from $SYSTEM.TMSCHEMA_OBJECT_TRANSLATIONS
Select * from $SYSTEM.TMSCHEMA_PARTITION_STORAGES
Select * from $SYSTEM.TMSCHEMA_RELATIONSHIP_INDEX_STORAGES
Select * from $SYSTEM.TMSCHEMA_RELATIONSHIP_STORAGES
Select * from $SYSTEM.TMSCHEMA_SEGMENT_MAP_STORAGES
Select * from $SYSTEM.TMSCHEMA_SEGMENT_STORAGES
Select * from $SYSTEM.TMSCHEMA_STORAGE_FILES
Select * from $SYSTEM.TMSCHEMA_STORAGE_FOLDERS
Select * from $SYSTEM.TMSCHEMA_TABLE_STORAGES
Select * from $SYSTEM.DBSCHEMA_COLUMNS
@rudzimj
Copy link

rudzimj commented Feb 6, 2018

See Microsoft documentation at https://msdn.microsoft.com/en-us/library/mt719260(v=sql.105).aspx
Note that $SYSTEM.TMSCHEMA_COLUMN_STORAGES is reserved for internal use only. Two of the most useful columns on that table for row counts and discount column counts are incorrect and should not be used.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment