home
training courses
why choose us?
solutions
support
company
LearnItFirst User Forum
Welcome Guest
Search
|
New Posts
|
Members
|
Log In
|
Register
SQL Server Forum - LearnItFirst.com
»
SQL Server Scripts, Code Samples and SSMS Custom Reports
»
All SQL Server Versions
»
Script to Turn on Auto Update Statistics for all Tables and Indexes
Script to Turn on Auto Update Statistics for all Tables and Indexes
Options
Previous Topic
·
Next Topic
Scott Whigham
Posted:
Wednesday, September 20, 2006 4:53:26 PM
Rank: Super Mod
Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX
Code:
/*
Author: Scott Whigham from
http://www.LearnSqlServer.com/
Description: This script turns on auto stats for every table and it's indexes.
Versions: SQL Server 2005, 2000, 7.0 but really for 2000 and 7.0
Creation Date: Sept 20, 2006
For more scripts like this one, visit
http://forums.learnsqlserver.com/codesamples.aspx
*/
SET NOCOUNT ON
DECLARE @Table TABLE (Id INT NOT NULL IDENTITY(1,1), TableName SYSNAME, SchemaName SYSNAME)
DECLARE @CurrentId INT, @Counter INT, @DynamicSql NVARCHAR(1024), @TableName SYSNAME
, @StartTime DATETIME, @EndTime DATETIME
SET @StartTime = GETDATE()
INSERT @Table
SELECT TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
SELECT TOP 1 @CurrentId = Id, @TableName = SchemaName + '.' + TableName, @Counter = Id FROM @Table
WHILE @CurrentId IS NOT NULL
BEGIN
SET @DynamicSql = 'EXEC sp_autostats ''' + @TableName + ''', ''ON'''
EXEC(@DynamicSql)
DELETE @Table WHERE Id = @CurrentId
SET @CurrentId = NULL
SELECT TOP 1 @CurrentId = Id, @TableName = SchemaName + '.' + TableName, @Counter = Id FROM @Table
END
SET @EndTime = GETDATE()
SET @DynamicSql = 'Finished running sp_autostats ''ON'' for ' + CAST(@Counter AS VARCHAR) + ' tables in '
+ CAST(DATEDIFF(second, @StartTime, @EndTime) AS VARCHAR) + ' seconds'
PRINT @DynamicSql
Back to top
Users browsing this topic
Guest
Forum Jump
SQL Server Database Administration
- General SQL Database Question & Answer
- Backup, Recovery and Disaster Recovery
- SQL Server Security
- Integration Services (SSIS) and DTS
Transact-SQL Programming
- DML (SELECT, INSERT, UPDATE, DELETE) Questions
- Stored Procedures, Triggers, & Functions
SQL Server Scripts, Code Samples and SSMS Custom Reports
- All SQL Server Versions
- SQL Server 2005/2008
- SQL Server Management Studio Custom Reports
Customer Service
- Video Requests
You
cannot
post new topics in this forum.
You
cannot
reply to topics in this forum.
You
cannot
delete your posts in this forum.
You
cannot
edit your posts in this forum.
You
cannot
create polls in this forum.
You
cannot
vote in polls in this forum.
SQL Server 2005 DBA Training Videos
SQL Server 2008 DBA Training Videos
Watch this topic
RSS Feed
Email this topic
Print this topic
Threaded
Normal