Apply Row and Page Compression to Tables and Indexes
Jun 30, 2026 / · 10 min read · sql server data compression sys.dm_db_index_physical_stats sys.partitions sys.indexes sys.objects sys.schemas row compression page compression index maintenance maintenance scripts ·Storage budgets shrink faster than databases grow. Uncompressed tables and indexes often carry two to four times the physical page count that row or page compression would require — a gap that widens with every batch load or continuous INSERT. sys.dm_db_index_physical_stats joined to sys.partitions surfaces every …
Read MoreList All Tables Across All Databases in SQL Server
Jun 29, 2026 / · 11 min read · sql server sys.databases sys.tables sys.schemas dynamic sql cross database catalog views table inventory sp_executesql administration metadata scripts ·A cross-database table inventory is one of the most common ad hoc requests in any SQL Server environment — and one of the few queries that cannot be answered by staying inside a single database context. sys.databases exposes every online database on the instance; three-part name references to sys.tables inside dynamic …
Read Moresp_pkeys and Primary Key Metadata in SQL Server
Jun 16, 2026 / · 6 min read · sql server sp_pkeys sys.key_constraints sys.index_columns sys.columns sys.types sys.tables sys.objects sys.schemas information_schema primary keys catalog views metadata database structure scripts ·Which columns make up a table's primary key, and in what order are they declared? For a single table, the system stored procedure sp_pkeys answers in one line; for a database-wide inventory, the catalog views behind it give you full control. This post covers both, from the quick lookup to the complete metadata query. …
Read Moresysindexes vs sys.indexes: Legacy and Modern Catalog Views
Plenty of SQL Server scripts still in circulation read rowcnt and dpages straight out of sysindexes — a habit inherited from SQL Server 2000, where that table was the canonical place to find row counts and page totals. The object survives only as the sys.sysindexes compatibility view, is flagged for removal, and …
Read MoreSQL Server Catalog Views: sys.tables, sys.indexes, sys.objects
Jun 14, 2026 / · 6 min read · sql server catalog views system tables metadata sys.tables sys.indexes sys.objects sys.schemas sys.partitions sys.columns sys.index_columns sys.foreign_keys database structure scripts ·The sys catalog views are the supported, forward-compatible way to read SQL Server metadata, and three of them — sys.objects, sys.tables, and sys.indexes — answer most day-to-day questions about what lives in a database. This script joins them into one inventory of every user table, its schema, its row count, and each …
Read MoreSQL Server Duplicate and Overlapping Index Detection Script
Duplicate and overlapping indexes waste storage, slow down every write, and split optimizer statistics across redundant copies. This T-SQL script compares each non-heap index in the current database on its first sixteen key columns and surfaces any pair of indexes that overlap, giving the DBA a short list of cleanup …
Read MoreSQL Server Find Columns by Data Type Across the Database
"Where is every datetime column?" — "Which tables still use text instead of varchar(max)?" — "Are any columns using a deprecated user-defined type?" These are recurring DBA questions during schema audits, deprecation sweeps, and pre-migration assessments. This T-SQL script joins sys.columns to sys.types, sys.tables, …
Read More