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 …

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. …

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 …

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 …

"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, …

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 …

A single catalog query can list every grant, deny, and role membership in a database, and the result is the starting point for an access review. The script below returns one flat result set that covers permissions granted directly to a user, permissions a user holds through a database role, and permissions granted to …

Find Unused Nonclustered Indexes in SQL Server This script queries sys.dm_db_index_usage_stats to identify nonclustered indexes that have never been used by any read operation since the last SQL Server restart, yet still carry write overhead with every INSERT, UPDATE, and DELETE on the table. Purpose and Overview Every …

Before sys.dm_db_index_physical_stats existed, checking fragmentation across a database meant looking up every user table's object ID and running DBCC SHOWCONTIG against each one individually. This script closes that gap for the sysobjects-based era of SQL Server administration: it queries sysobjects for every table of …