Last active
September 27, 2022 12:51
-
-
Save mlongoria/a9a0bff0f51a5e9c200b9c8b378d79da to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.