Ramblings of a DBA

Tara Kizer
posts - 166, comments - 835, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

UPDATE STATISTICS stored procedure

Here's the stored procedure that I use to update statistics on a database.  I don't use sp_updatestats as it doesn't allow you to change the number of rows to sample.  I typically run it weekly on the databases that I support. 

CREATE PROC isp_UPDATE_STATISTICS
(@dbName sysname, @sample int)
AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)
DECLARE @ID int
DECLARE @TableName sysname
DECLARE @RowCnt int

CREATE TABLE ##Tables
(
 TableID INT IDENTITY(1, 1) NOT NULL,
 TableName SYSNAME NOT NULL
)

SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Tables (TableName) '
SET @SQL = @SQL + 'SELECT [name] '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysobjects '
SET @SQL = @SQL + 'WHERE xtype = ''U'' AND [name] <> ''dtproperties'''

EXEC sp_executesql @statement = @SQL

SELECT TOP 1 @ID = TableID, @TableName = TableName
FROM ##Tables
ORDER BY TableID

SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

 SET @SQL = 'UPDATE STATISTICS ' + @dbname + '.dbo.[' + @TableName + '] WITH SAMPLE ' + CONVERT(varchar(3), @sample) + ' PERCENT'

 EXEC sp_executesql @statement = @SQL

 SELECT TOP 1 @ID = TableID, @TableName = TableName
 FROM ##Tables
 WHERE TableID > @ID
 ORDER BY TableID

 SET @RowCnt = @@ROWCOUNT

END

DROP TABLE ##Tables

GO

 

Print | posted on Monday, August 14, 2006 3:26 PM | Filed Under [ SQL Server - Database Administration ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET