Get table property
The latest version of SQL Server introduces several new properties for tables.
SELECT OBJECT_NAME(t.[object_id]) AS [ObjectName]
,p.[rows] AS [Table Rows]
,p.index_id
,p.data_compression_desc AS [Index Data Compression]
,t.create_date
,t.lock_on_bulk_load
,t.is_replicated
,t.has_replication_filter
,t.is_tracked_by_cdc
,t.lock_escalation_desc
,t.is_filetable
,t.is_memory_optimized
,t.durability_desc
,t.temporal_type_desc
,t.is_remote_data_archive_enabled
,t.is_external -- new for SQL Server 2016
FROM sys.tables AS t WITH (NOLOCK)
INNER JOIN sys.partitions AS p WITH (NOLOCK) ON t.[object_id] = p.[object_id]
WHERE OBJECT_NAME(t.[object_id]) NOT LIKE N'sys%'
ORDER BY OBJECT_NAME(t.[object_id])
,p.index_id
OPTION (RECOMPILE);
You might also find these related resources helpful:
