Summary: Discover how to extract comprehensive table properties in SQL Server, including row counts, data compression, and modern features like Memory Optimization, Temporal types, and External table status.
Get Table Property Overview
Modern versions of SQL Server have introduced several new properties for tables that are essential for auditing and performance tuning. Using sys.tables and sys.partitions, we can get a high-level view of how our data is stored and managed.
Query to Retrieve Table Metadata
The following query provides a detailed breakdown of table configurations, including replication status, CDC tracking, and specific engine features like PolyBase (External tables) or In-Memory OLTP.
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);
Key Properties Explained
- is_memory_optimized: Indicates if the table is an In-Memory OLTP table.
- temporal_type_desc: Shows if the table is a System-Versioned (Temporal) table for point-in-time analysis.
- is_external: Identifies if the table is an External Table (used with PolyBase or Azure Storage).
- data_compression_desc: Displays whether the index/table is using ROW or PAGE compression.
Related Resources
You might also find these related resources helpful for broader server and database auditing:
💡 Pro Tip: This query uses
WITH (NOLOCK) to prevent blocking during metadata retrieval on busy production systems. However, keep in mind that row counts from sys.partitions are approximate; for 100% accuracy, a COUNT(*) is required.
