In Oracle databases there is a table that allows to list all the tables in the database (table 'dba_tables') and we can use this 'dba_tables' to create maintenance scripts dynamically. In SQL Server we can create also scripts of tables maintenace by querying the table [dataBase].dbo.sysobjects.
In the example below we use a T-SQL script to update statistics for all tables in a SQL Server database by querying dynamically the data dictionary (using the table dbo.sysobjects). This T-SQL code can be encapsulated in a stored procedure or in a job to be executed by the SQL Server Agent to automatically keep statistics updated on all tables of the dbo scheme in a SQL Server database.
Update statistics from all tables of the 'dbo' scheme on a SQL Server database
-- Declaration of variables
DECLARE @dbName sysname
DECLARE @sample int
DECLARE @SQL nvarchar(4000)
DECLARE @ID int
DECLARE @Table sysname
DECLARE @RowCnt int
-- Filter by database and percentage for recalculation of statistics
SET @dbName = 'AdventureWorks2008'
SET @sample = 100
--Temporary Table
CREATE TABLE ##Tables
(
TableID INT IDENTITY(1, 1) NOT NULL,
TableName SYSNAME NOT NULL
)
--We feed the table with the list of tables
SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Tables (TableName) '
SET @SQL = @SQL + 'SELECT [name] FROM ' + @dbName + '.dbo.sysobjects WHERE xtype = ''U'' AND [name] <> ''dtproperties'''
EXEC sp_executesql @statement = @SQL
SELECT TOP 1 @ID = TableID, @Table = TableName
FROM ##Tables
ORDER BY TableID
SET @RowCnt = @@ROWCOUNT
-- For each table
WHILE @RowCnt <> 0
BEGIN
SET @SQL = 'UPDATE STATISTICS ' + @dbname + '.dbo.[' + @Table + '] WITH SAMPLE ' + CONVERT(varchar(3), @sample) + ' PERCENT'
EXEC sp_executesql @statement = @SQL
SELECT TOP 1 @ID = TableID, @Table = TableName
FROM ##Tables
WHERE TableID > @ID
ORDER BY TableID
SET @RowCnt = @@ROWCOUNT
END
--Drop the temporal table
DROP TABLE ##Tables
[[ad]]
Update statistics from all tables of all schemes on a SQL Server database
But if we have tables in schemes different than the dbo this script woud fail for this tables contained in other schemas. Another version of the script, that update all the table statistics in a SQLServer Database for all the tables, for the dbo scheme and for all other schemes is: (changes in red)
-- Declaration of variables
DECLARE @dbName sysname
DECLARE @sample int
DECLARE @SQL nvarchar(4000)
DECLARE @ID int
DECLARE @Table sysname
DECLARE @RowCnt int
-- Filter by database and percentage for recalculation of statistics
SET @dbName = 'AdventureWorks2008'
SET @sample = 100
--Temporary Table
CREATE TABLE ##Tables
(
TableID INT IDENTITY(1, 1) NOT NULL,
TableName SYSNAME NOT NULL
)
--We feed the table with the list of tables of all schemas
SET @SQL = ''
SET @SQL = 'INSERT INTO ##Tablas(TableName) '
SET @SQL = @SQL + ' select ''['' + TABLE_CATALOG + ''].['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + '']'' from INFORMATION_SCHEMA.TABLES'
EXEC sp_executesql @statement = @SQL
SELECT TOP 1 @ID = TableID, @Table = TableName
FROM ##Tables
ORDER BY TableID
SET @RowCnt = @@ROWCOUNT
-- For each table
WHILE @RowCnt <> 0
BEGIN
-- Update statistics using only the table name
SET @SQL = 'UPDATE STATISTICS ' + @Tabla + ' WITH SAMPLE ' + CONVERT(varchar(3), @sample) + ' PERCENT'
EXEC sp_executesql @statement = @SQL
SELECT TOP 1 @ID = TableID, @Table = TableName
FROM ##Tables
WHERE TableID > @ID
ORDER BY TableID
SET @RowCnt = @@ROWCOUNT
END
--Drop the temporal table
DROP TABLE ##Tables
