2

I have a project database that maintain different databases from different environnement (dev, test, prod, predprod...), I need to check if AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICSis set to OFF/ON against each DB and if its OFF- alter it to ON.

ALTER DATABASE  DB
SET AUTO_CREATE_STATISTICS ON

ALTER DATABASE  DB
SET AUTO_UPDATE_STATISTICS ON

How can i achieve this?

4

This uses a Dynamic SQL query. It excludes system databases. Checks for is_auto_create_stats_on or is_auto_update_stats_on 0 OFF and change it to ON

declare @sql    nvarchar(max);

; with 
db as
(
    select  name, is_auto_create_stats_on, is_auto_update_stats_on 
    from    sys.databases
    where   name    not in ('master', 'tempdb', 'model', 'msdb')
    and     (
                is_auto_create_stats_on = 0
            or  is_auto_update_stats_on = 0
            )
)
select  @sql    = isnull(@sql + char(13), '')
                + 'ALTER DATABASE ' + quotename(db.name) + ' '
                + 'SET ' + o.opt + ' ON;'
from    db 
        cross apply
        (
            select  opt = 'AUTO_CREATE_STATISTICS'
            where   is_auto_create_stats_on = 0

            union all

            select  opt = 'AUTO_UPDATE_STATISTICS'
            where   is_auto_update_stats_on = 0
        ) o

-- print out for verification before execute
print   @sql
exec    sp_executesql @sql

You may mask the sp_executesql and verify the result before actual execution

2
DECLARE @SQL    NVARCHAR(MAX) = ''

-- getting the commands list
SET @SQL = (
    SELECT '' + x.cmd
    FROM (
        select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET AUTO_CREATE_STATISTICS ON;' AS cmd
        from sys.databases
        WHERE database_id > 4
            AND is_auto_create_stats_on = 0
        UNION ALL
        select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET AUTO_UPDATE_STATISTICS ON;'
        from sys.databases
        WHERE database_id > 4
            AND is_auto_update_stats_on = 0
    ) x
    FOR XML PATH('')
);

IF @SQL IS NOT NULL BEGIN
    SELECT @SQL;

--uncomment to execute
--  EXEC sp_executesql @SQL;
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.