Properties for In-Memory and Temporal Tables

Couple of days ago I hit a problem with restoring AdwentureWorks2016 because it contains In-Memory tables.
There was an immediate question: which tables are created In-Memory and how big they are?
Unfortunately, I couldn't answer that question in one click. So, I decided that it is a time to upgrade my "SQL on your fingertips" script and include there properties of new SQL Server objects.

I've found a DMV "sys.dm_db_xtp_memory_consumers", which provides information about Memory allocations of In-Memory tables and indexes.

And here is the script I came up with:

;WITH InMemory as (
 SELECT object_id
  , SUM(CASE memory_consumer_type WHEN 2 THEN allocation_count ELSE 0 END) as row_count
  , SUM(CASE memory_consumer_type WHEN 2 THEN allocated_bytes ELSE 0 END)/8192. as Alloc_Pages 
  , SUM(CASE memory_consumer_type WHEN 2 THEN used_bytes ELSE 0 END)/8192. as Used_Pages
  , SUM(CASE memory_consumer_type WHEN 2 THEN 0 ELSE allocated_bytes END)/8192. as Index_Alloc_Pages
  , SUM(CASE memory_consumer_type WHEN 2 THEN 0 ELSE used_bytes END)/8192. as Index_Used_Pages
  , SUM(allocated_bytes)/8192. as Total_Alloc_Pages
  , SUM(used_bytes)/8192. as Total_Used_Pages
 FROM sys.dm_db_xtp_memory_consumers
 WHERE object_id > 0 
 GROUP BY object_id
 )
 SELECT CASE WHEN t.name Is Null THEN 'View' ELSE 'Table' END as Type,
 OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id) as ObjectName,
 CASE WHEN t.is_memory_optimized > 0 THEN mc.row_count ELSE SUM(CASE WHEN st.index_id < 2 THEN st.row_count ELSE 0 END) END as row_count,
 ROUND(CAST(CASE WHEN t.is_memory_optimized > 0 THEN mc.Alloc_Pages ELSE SUM(CASE WHEN st.index_id < 2 THEN st.reserved_page_count ELSE 0 END) END AS float)/128.,3) as Data_Size_MB,
 ROUND(CAST(CASE WHEN t.is_memory_optimized > 0 THEN mc.Used_Pages ELSE SUM(CASE WHEN st.index_id < 2 THEN st.used_page_count ELSE 0 END) END AS float)/128.,3) as Used_Data_Space_MB,
 ROUND(CAST(CASE WHEN t.is_memory_optimized > 0 THEN mc.Index_Alloc_Pages ELSE SUM(CASE WHEN st.index_id > 1 THEN st.reserved_page_count ELSE 0 END) END AS float)/128.,3) as Index_Size_MB,
 ROUND(CAST(CASE WHEN t.is_memory_optimized > 0 THEN mc.Index_Used_Pages ELSE SUM(CASE WHEN st.index_id > 1 THEN st.used_page_count ELSE 0 END) END AS float)/128.,3)as Used_Index_Space_MB,
 ROUND(CAST(CASE WHEN t.is_memory_optimized > 0 THEN mc.Total_Alloc_Pages ELSE SUM(st.reserved_page_count) END AS float)/128.,3) as Full_Size_MB,
 ROUND(CAST(CASE WHEN t.is_memory_optimized > 0 THEN mc.Total_Used_Pages ELSE SUM(st.used_page_count) END AS float)/128.,3) as Full_Used_Space_MB,
 t.max_column_id_used as MaxColumn,
 CASE WHEN t.is_memory_optimized > 0 THEN 'Yes' ELSE '' END as 'In-Memory',
 CASE WHEN t.temporal_type > 0 THEN t.temporal_type_desc ELSE '' END as Temporal, 
 t.lock_escalation_desc as 'Lock Escalation'
FROM sys.dm_db_partition_stats st 
LEFT JOIN InMemory as mc ON mc.object_id = st.object_id
LEFT JOIN sys.tables as t ON t.object_id = st.object_id
WHERE OBJECT_SCHEMA_NAME(st.object_id) != 'sys'
GROUP BY OBJECT_SCHEMA_NAME(st.object_id), OBJECT_NAME(st.object_id), t.name
 , t.max_column_id_used, t.lock_escalation_desc, t.is_memory_optimized, mc.row_count, mc.Alloc_Pages, mc.Used_Pages
 , mc.Index_Alloc_Pages, mc.Index_Used_Pages, mc.Total_Alloc_Pages, mc.Total_Used_Pages, t.temporal_type
 , t.temporal_type_desc
ORDER BY Full_Size_MB DESC, row_count DESC
OPTION (RECOMPILE);

Here are the results from AdventureWorks2016 DB I've got:

In that data set we have a new column "In-Memory" - indicating that table is created to be stored In-Memory. Data Size numbers show real memory usage by that table. That option is supposed to work in SQL Server 2014 and up.

There is also new column called "Temporal". It indicates if a table is System Versioned or it is Historical repository. Row_Count number for Historical repository indicates number of changes done to the main table. That option is applicable only for SQL Server 2016.

I've updated only "Ctrl-F1" button, which returns list of tables within a database. Here is the link to the full script archive: https://drive.google.com/open?id=0B5yWoyX1eEWqZ3FJUnNHZm80bzQ