SQLServer 2008: Actualizar estadísticas de tabla de forma dinámica en toda una base de datos

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