Execute UPDATE STATISTICS for all SQL Server Databases

Guys,

Today we will write a simple script which will generate UPDATE STATISTICS for all the database on current SQL Server. Statistics in SQL Server refers specifically to information that the server collects about the distribution of data in columns and indexes. This data is used by the query optimizer to determine the plan of attack for returning results when you run a query. While in the majority of cases SQL Server takes care of this statistical recordkeeping for you automatically, it's useful to have some understanding of what's going on, and to know when you might need to intervene.


















Automatic Statistics
By default, SQL Server databases automatically create and update statistics. Although you can disable this (by setting a property of the database), you should almost certainly leave it on. SQL Server needs these statistics to do a good job with query processing, and it's likely to be smarter than you about what and when to update. The information that gets stored includes:

• The number of rows and pages occupied by a table's data
• The time that statistics were last updated
• The average length of keys in a column
• Histograms showing the distribution of data in a column
• String summaries that are used when performing LIKE queries on character data

SQL Server maintains statistics on index and key columns of all of your tables - in other words, the columns that the query processor is able to use in various ways in queries depending on the data that they contain.

SQL Server's engine will update the statistic when:

• When data is initially added to an empty table
• The table had > 500 records when statistics were last collected and the lead column of the statistics object has now increased by 500 records since that collection date
• The table had < 500 records when statistics were last collected and the lead column of the statistics object has now increased by 500 records + 20% of the row count from the previous statistics collection date

Below is a simple code that should work on all SQL Versions

Set nocount on

DECLARE @minId int, @maxId int
DECLARE @DB Nvarchar(100)

Create table #tmp
(
ID int Identity,
Name varchar(100)
)

INSERT INTO #tmp
SELECT [name] 
FROM master..sysdatabases 
WHERE [name] NOT IN ('model', 'tempdb') 
ORDER BY [name] 

Select @minId = 1, @maxId = count(1) from #tmp

While @minId <= @maxId
Begin
SELECT 'Use ' + name + CHAR(13) + 'Exec sp_UpdateStats ' from #tmp Where Id = @minId
Set @minId+=1
END

drop table #tmp

This code yields following results:

Use AdventureWorks
Exec sp_UpdateStats

Use AdventureWorksDW
Exec sp_UpdateStats

Use LearningCT
Exec sp_UpdateStats

Use master
Exec sp_UpdateStats

Use msdb
Exec sp_UpdateStats

Use Northwind
Exec sp_UpdateStats

Use ReportServer$MSSQL2008
Exec sp_UpdateStats

Use ReportServer$MSSQL2008TempDB
Exec sp_UpdateStats

Use SensitiveDB
Exec sp_UpdateStats

Use testdb
Exec sp_UpdateStats

Now copy this text & paste it into a query window in SQL Server Management Studio, then execute it.

When you execute the code, you will see the output like this:

This shows which all indexes are updated.

Happy SQL Coding