SQL Server Dynamic Update Statistics and Select Scripts for Database Maintenance

calendar Aug 6, 2025 / · 4 min read · sql server database administration t-sql performance tuning dynamic sql database maintenance statistics automation sysobjects update statistics sys.tables  ·
Share on: linkedin copy

SQL Server Dynamic Update Statistics and Select Scripts for Database Maintenance

Purpose

These SQL Server scripts are designed to automatically generate database maintenance commands by dynamically creating UPDATE STATISTICS statements and SELECT queries for all user tables in a database. This approach is particularly useful for database administrators who need to perform bulk operations across multiple tables without manually writing individual commands for each table.

Code Breakdown

Script 1: Dynamic UPDATE STATISTICS Generator

1select 'Update Statistics ' + name + ' go' + ' SELECT getdate()' + ' go' 
2from sysobjects 
3where type = 'u' 
4order by name

Purpose: Generates UPDATE STATISTICS commands for all user tables, including timestamp tracking.

Components:

  • 'Update Statistics ' + name + ' go': Creates the UPDATE STATISTICS command
  • ' SELECT getdate()' + ' go': Adds a timestamp query after each update
  • from sysobjects where type = 'u': Queries system objects table for user tables only
  • order by name: Sorts results alphabetically by table name

Script 2: Dynamic SELECT Statement Generator

1select 'Select * From ' + name + 'go'  
2from sysobjects 
3where type = 'U' 
4order by name

Purpose: Generates SELECT statements to query all records from each user table.

Components:

  • 'Select * From ' + name + 'go': Creates SELECT statements
  • from sysobjects where type = 'U': Queries for user tables (note: 'U' uppercase)
  • order by name: Alphabetical sorting

Key Points and Insights

1. System Table Usage

The scripts utilize the sysobjects system table, which contains metadata about database objects. The type = 'u' (or 'U') filter specifically targets user-created tables, excluding system tables.

2. Dynamic SQL Generation

These scripts don't execute the commands directly; instead, they generate the SQL statements as text output. This approach provides:

  • Safety: Review generated commands before execution
  • Flexibility: Modify output before running
  • Documentation: Save generated scripts for future reference

3. Batch Separators

The inclusion of 'go' statements creates proper batch separators, ensuring each command executes independently when run in SQL Server Management Studio.

4. Performance Considerations

UPDATE STATISTICS operations can be resource-intensive, especially on large databases. The timestamp tracking (SELECT getdate()) helps monitor execution time for each table.

Implementation Best Practices

1. Execution Strategy

1-- Step 1: Generate the commands
2-- Step 2: Copy output to new query window
3-- Step 3: Review generated SQL
4-- Step 4: Execute during maintenance window

2. Enhanced Version with Error Handling

 1select 'BEGIN TRY' + CHAR(13) + CHAR(10) +
 2       'Update Statistics ' + name + CHAR(13) + CHAR(10) +
 3       'PRINT ''Updated statistics for ' + name + ' at '' + CONVERT(varchar, getdate())' + CHAR(13) + CHAR(10) +
 4       'END TRY' + CHAR(13) + CHAR(10) +
 5       'BEGIN CATCH' + CHAR(13) + CHAR(10) +
 6       'PRINT ''Error updating ' + name + ': '' + ERROR_MESSAGE()' + CHAR(13) + CHAR(10) +
 7       'END CATCH' + CHAR(13) + CHAR(10) + 'GO'
 8from sysobjects 
 9where type = 'u' 
10order by name

When to Use These Scripts

UPDATE STATISTICS Script

  • After bulk data loads: When large amounts of data have been inserted
  • Performance issues: When query plans appear outdated
  • Scheduled maintenance: Regular database maintenance routines
  • Index maintenance: Complement to index rebuild/reorganize operations

SELECT Script

  • Data validation: Quick verification of table contents
  • Documentation: Understanding database structure
  • Troubleshooting: Investigating data issues across multiple tables

Limitations and Considerations

1. Legacy System Table

sysobjects is a legacy system table. Modern alternatives include:

1-- Modern equivalent using sys.tables
2select 'Update Statistics ' + name + ' go' + ' SELECT getdate()' + ' go' 
3from sys.tables 
4order by name

2. Permission Requirements

  • Requires appropriate permissions to query system tables
  • UPDATE STATISTICS requires db_owner or appropriate table-level permissions

3. Resource Impact

  • UPDATE STATISTICS can be I/O intensive
  • Consider running during low-activity periods
  • Monitor system resources during execution

Advanced Variations

Selective Statistics Update

1select 'Update Statistics ' + name + ' WITH SAMPLE 50 PERCENT go' + ' SELECT getdate()' + ' go' 
2from sysobjects 
3where type = 'u' 
4and name like 'Customer%'  -- Filter specific tables
5order by name

Row Count Verification

1select 'Select COUNT(*) as RowCount, ''' + name + ''' as TableName From ' + name + ' go'  
2from sysobjects 
3where type = 'U' 
4order by name

Conclusion

These dynamic SQL generation scripts provide a powerful foundation for database maintenance automation. While simple in design, they demonstrate important concepts in database administration including system table querying, dynamic SQL construction, and bulk operation planning. When implemented with proper safety measures and scheduling considerations, these scripts can significantly streamline routine database maintenance tasks.

References

Posts in this series