Generate DBCC SHOWCONTIG Commands for All SQL Server Tables

calendar Aug 1, 2025 / · 8 min read · sysobjects sql server database maintenance fragmentation dbcc commands performance administration scripts optimization dbcc sys.tables sys.columns sys.databases sys.dm_db_index_physical_stats sys.objects syscolumns sysdatabases  ·
Share on: linkedin copy

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 type U, concatenates a ready-to-run DBCC SHOWCONTIG command for each one, and returns the full batch sorted alphabetically by table name — turning what would be dozens of hand-typed commands into a single copy-paste result set.

Purpose and Overview

DBCC SHOWCONTIG reports fragmentation for a single table or index at a time, returning metrics like Scan Density, Logical Scan Fragmentation, Extent Scan Fragmentation, and Average Page Density. Run in isolation, it answers "is this one table fragmented?" On a database with dozens or hundreds of user tables, the practical question is broader — which tables need attention this week — and answering that manually means writing one DBCC SHOWCONTIG (table_id) statement per table by hand, looking up each object ID first.

This script removes that manual lookup step entirely. sysobjects is a system table (retained today as a backward-compatibility view over the catalog views) that carries one row per database object — tables, views, stored procedures, triggers, and more — along with its object id and a type code identifying what kind of object it is. Filtering type = 'U' isolates user tables specifically, excluding system tables, views, and every other object type that would otherwise clutter the output.

The result of the query isn't data about fragmentation — it's a batch of executable T-SQL. Each row in the output is a string built by concatenating the literal text DBCC SHOWCONTIG (, the table's numeric ID converted to text, and a closing ) followed by a GO batch separator. Copy the result set into a query window and it becomes a script that runs DBCC SHOWCONTIG against every user table in the database in one pass, ordered alphabetically for easy scanning.

Code Breakdown

The script is a single SELECT statement that never touches fragmentation data directly — it only builds the commands that will.

1select 'DBCC SHOWCONTIG (' + CONVERT(varchar(12), id) + ')' + 'go' 
2from sysobjects 
3where type = 'U' 
4order by name

String concatenation and the CONVERT function

sysobjects.id is an int column, and T-SQL's + concatenation operator requires matching or implicitly convertible types on both sides. CONVERT(varchar(12), id) turns the numeric object ID into text before it's spliced between the literal 'DBCC SHOWCONTIG (' and ')' strings. varchar(12) is generous headroom — SQL Server object IDs are 4-byte integers, so the maximum possible value never exceeds 11 characters including a leading negative sign, which some system and compatibility-view object IDs can carry.

Filtering to user tables with type = 'U'

The type column in sysobjects uses two-character codes to classify every row: 'U' for user tables, 'V' for views, 'P' for stored procedures, 'S' for system tables, and several others for triggers, defaults, and constraints. WHERE type = 'U' is what keeps this script scoped to actual data tables rather than generating nonsensical DBCC SHOWCONTIG calls against views or procedures, which the command doesn't operate on.

The missing line break — a practical gotcha

The script concatenates ')' directly against the literal 'go' with no space and, more importantly, no line break between one row's output and the next row's output. GO is a batch separator recognized by SQL Server Management Studio and sqlcmd, not the database engine itself, and it only works when it appears alone on its own line. Viewed in the SSMS results grid, each row looks like a clean, separate line — but that's a grid-rendering artifact, not an actual newline character in the string. Copy multiple rows out of the grid and paste them into a query window, and depending on how the paste handles row boundaries, the commands can land concatenated on a single line with go sitting mid-string instead of on its own line, which breaks batch parsing. Switching SSMS to Results to Text (Ctrl+T) before running the query avoids this, because text-mode results preserve a genuine newline between rows.

Modernizing the script: sys.tables and WITH TABLERESULTS

sysobjects still works in current SQL Server versions as a compatibility view, but sys.tables and sys.objects are the catalog views Microsoft has recommended since SQL Server 2005, and they don't share sysobjects' mixed-object-type design. The same idea rewritten against sys.tables, using object_id instead of id and adding a real CHAR(13) line break plus the WITH TABLERESULTS option:

1SELECT 'DBCC SHOWCONTIG (' + CONVERT(varchar(12), object_id) + ') WITH TABLERESULTS' + CHAR(13) + 'GO'
2FROM sys.tables
3ORDER BY name

WITH TABLERESULTS changes DBCC SHOWCONTIG's output from a block of narrative text messages into a proper result set with named columns — Pages, Extents, ScanDensity, LogicalFragmentation, and the rest — which is what makes it possible to insert the output into a temp table or a history table for trend tracking instead of reading free text off the screen.

Automating execution with dynamic SQL

Rather than generating commands to copy and run manually, the same sysobjects query can build one large batch string and execute it directly with sp_executesql:

1DECLARE @sql NVARCHAR(MAX) = ''
2SELECT @sql = @sql + 'DBCC SHOWCONTIG (' + CONVERT(varchar(12), id) + ') WITH TABLERESULTS' + CHAR(13) + 'GO' + CHAR(13)
3FROM sysobjects 
4WHERE type = 'U' 
5ORDER BY name
6
7EXEC sp_executesql @sql

This is convenient for a one-off run, but it loses the review step the original two-part workflow — generate, then eyeball, then execute — gives a DBA. On a database with an unusual or unexpected table inventory, seeing the generated command list before running it is often worth the extra click.

Key Benefits and Use Cases

  • Automated command generation — replaces manually writing one DBCC SHOWCONTIG call per table with a single query that scales to any number of tables.
  • Comprehensive coverage — the type = 'U' filter guarantees every user table is included; nothing gets skipped because a DBA forgot to look it up.
  • Ready-to-execute output — the generated text already includes the GO separator, so (line-break caveats aside) it pastes directly into a new query window.
  • Cross-version reach — sysobjects is retained as a compatibility view, so this pattern still runs on instances where team habits or older scripts still reference it.
  • Baseline snapshotting — running the generated batch before and after an index maintenance window produces a before/after fragmentation comparison with no extra setup.
  • A stepping stone to automation — the same sysobjects-to-sys.tables migration path shown here applies to any script still written against the legacy system tables.

Performance Considerations

  • DBCC SHOWCONTIG is a scan-based command: it reads pages to compute its fragmentation metrics, so running it against every table in a large database can generate meaningful I/O — schedule full-database runs outside peak query windows.
  • sysobjects is a compatibility view, not the current catalog: Microsoft has directed script authors toward sys.objects, sys.tables, and related catalog views since SQL Server 2005; new scripts should be written against those rather than extending sysobjects usage.
  • Free-text output without WITH TABLERESULTS is hard to automate: the original script's plain-text DBCC SHOWCONTIG output can't be inserted into a table for trending without parsing; add WITH TABLERESULTS if the goal is anything beyond a one-time read.
  • Object IDs are instance-specific: a table's id/object_id is not guaranteed to be the same across a restore or a copy to a different instance, so generated command lists shouldn't be cached or reused across environments — regenerate them fresh against the target database.
  • Running the full batch blocks nothing by design, but adds load: DBCC SHOWCONTIG without repair options is a read operation and won't lock out writers the way an index rebuild would, but dozens of sequential scans back-to-back still add sustained disk read pressure on a busy instance.

Practical Tips

  • Switch SSMS to Results to Text (Ctrl+T) before copying the generated command list — it preserves real line breaks between rows and avoids the concatenated-GO problem described above.
  • Add WITH TABLERESULTS to the generated commands whenever the output needs to feed a history table or an automated fragmentation report, rather than being read as plain text.
  • Prefer generating against sys.tables for any new script; keep the sysobjects version only for maintaining scripts already written against it.
  • When fragmentation results come back, lighter fragmentation generally warrants a REORGANIZE, while heavier fragmentation warrants a REBUILD — the exact thresholds and trade-offs are covered in Microsoft's index maintenance guidance linked below.
  • On current SQL Server versions, treat this script as a stopgap rather than a destination: sys.dm_db_index_physical_stats returns the same fragmentation picture (and more — per-index and per-partition detail) directly as structured rows, with no command-generation step required at all.

Conclusion

This script's value is in the step it removes — looking up object IDs and hand-writing a DBCC SHOWCONTIG call per table — not in any special fragmentation logic of its own. The sysobjects version keeps working on instances where legacy habits or older tooling still expect it, while the sys.tables-plus-WITH TABLERESULTS variant and the dynamic-SQL execution wrapper both move toward a more current, more automatable setup. For ongoing fragmentation monitoring on a modern instance, sys.dm_db_index_physical_stats remains the direct path; this script earns its place as the quick, dependency-free option when that DMV isn't the workflow already in place.

References

Posts in this series