SQL Server Database Schema and Data Dictionary Queries
Jun 18, 2026 / · 6 min read · sql server information_schema data dictionary database schema metadata sys.objects sys.columns sys.sql_modules catalog views documentation database structure ansi standard scripts ·Documenting a database starts with a data dictionary: every table, every column, its data type, length, nullability, and default. SQL Server exposes this through the ANSI-standard INFORMATION_SCHEMA views, which produce a portable schema report that reads almost like English and runs unchanged on other ISO-compliant …
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 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 DBCC CHECKIDENT: Check and Reseed Identity
A logging table grew to forty million rows, the bulk of it was archived out, and now only the last week's data remains — but the next insert still gets identity value 40,000,001, leaving a vast gap below it. Or a botched data load left the seed sitting far above the real maximum, and future inserts skip millions of …
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: every constraint, its parent table and column, and the referenced table and column on the other side. This T-SQL script joins sys.foreign_keys to sys.foreign_key_columns, sys.tables, and sys.columns to produce the canonical foreign-key map of the …
Read MoreGenerate DBCC SHOWCONTIG Commands for All SQL Server Tables
Generate DBCC SHOWCONTIG Commands for All User Tables in SQL Server Database administrators frequently need to monitor table and index fragmentation to maintain optimal database performance. This SQL Server script provides an efficient way to generate DBCC SHOWCONTIG commands for all user tables in your database …
Read More