List All Tables Across All Databases in SQL Server
Jun 29, 2026 / · 9 min read · sql server sys.databases sys.tables sys.schemas sys.partitions dynamic sql cross database catalog views table inventory sp_executesql administration metadata scripts ·A table inventory that spans every database on an instance cannot be answered from one database context, because the table catalog lives inside each database. The pattern below reads the database list from sys.databases, builds one INSERT ... SELECT per online user database against that database's sys.tables and …
Read MoreSystem-Versioned (Temporal) Tables in SQL Server
Jun 19, 2026 / · 8 min read · sql server temporal tables system versioning for system_time history table sys.tables sys.databases point in time query data audit catalog views database structure metadata scripts ·Every UPDATE and DELETE on a system-versioned table leaves a queryable copy of the previous row, with no triggers and no hand-built audit table. SQL Server records the period each row version was valid and answers point-in-time questions through a single FOR SYSTEM_TIME clause. The catalog view sys.tables lists which …
Read Moresp_pkeys and Primary Key Metadata in SQL Server
Jun 16, 2026 / · 7 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 MoreSQL Server Catalog Views: sys.tables, sys.indexes, sys.objects
Jun 14, 2026 / · 9 min read · sql server catalog views system tables metadata sys.tables sys.indexes sys.objects sys.schemas sys.partitions sys.columns sys.index_columns database structure scripts ·Three catalog views, sys.objects, sys.tables, and sys.indexes, answer most day-to-day questions about what a database contains. This script joins them with sys.schemas and sys.partitions into one inventory: every user table, its schema, its dates, its row count, and each index defined on it. The row count comes from a …
Read MoreKnowing how much space each table and each index consumes is the foundation of capacity planning, archive policy, and index cleanup decisions on any SQL Server instance. This T-SQL script reads the partition-level storage statistics out of sys.dm_db_partition_stats and reports the size of every index on a user table — …
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 MoreSQL Server List All Foreign Keys with Referenced Tables
Every database with foreign keys eventually needs a single inventory: each constraint, the table and column that hold it, and the table and column it points at. This T-SQL script joins sys.foreign_keys to sys.foreign_key_columns, sys.tables, and sys.columns to produce that map for the current database, with one row per …
Read MoreSQL Server Identify Heap Tables Without Clustered Indexes
May 15, 2026 / · 9 min read · sql server heap tables sys.tables sys.indexes sys.dm_db_index_physical_stats sys.allocation_units sys.partitions performance database administration sql scripts ·A heap table — a table with no clustered index — stores rows in no particular order, forces full table scans for most non-indexed queries, and accumulates forwarded records every time an UPDATE causes a row to outgrow its page. This script queries sys.tables, sys.indexes, sys.partitions, sys.allocation_units, and …
Read MoreSQL Server Foreign Keys Without Indexes Script
Apr 18, 2026 / · 6 min read · sql server indexes foreign keys performance sql scripts database structure sys.foreign_keys query optimization sys.foreign_key_columns sys.index_columns sys.tables ·Foreign keys enforce referential integrity, but SQL Server does not automatically create indexes on the referencing (child) columns. An unindexed foreign key column forces a full table scan every time a join or a DELETE on the parent table triggers a referential check. This script identifies all foreign key columns in …
Read MoreSQL Server: Find Tables Without Primary Keys
Apr 16, 2026 / · 7 min read · sql server primary keys database structure heap tables sql scripts schema audit performance sys.tables sys.dm_db_index_physical_stats sys.indexes sys.key_constraints sys.partitions ·Tables without primary keys are heap structures with no guaranteed row ordering and no unique row identifier. They cause full table scans in queries that could use seeks, slow down DELETE and UPDATE operations, and are incompatible with SQL Server replication and Change Data Capture. This script finds every user table …
Read More