SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- =====================================================
-- Author:		Axosoft, LLC
-- Create date: 04/24/07
-- Description:	Update stats for an entire database
-- =====================================================
CREATE PROCEDURE spUpdateStatistics 
@DbName sysname, 
@Sample int = 100
AS
BEGIN

SET NOCOUNT ON;

DECLARE @Sql nvarchar(4000)
DECLARE @TableName sysname
DECLARE @RowNum 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 @SQL

SELECT @RowNum = MAX(TableID) FROM #Tables

WHILE @RowNum > 0
BEGIN

	SELECT @TableName = TableName
	FROM #Tables
	WHERE TableID = @RowNum
 
	SET @SQL = 'UPDATE STATISTICS ' + @dbname + '.dbo.[' + @TableName + '] WITH SAMPLE ' + CONVERT(varchar(3), @sample) + ' PERCENT'

	PRINT @SQL

	EXEC sp_executesql @SQL

	SET @RowNum = @RowNum - 1

END

DROP TABLE #Tables

SET NOCOUNT OFF;
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO