How to check AUTO_CREATE_STATISTICS/AUTO_UPDATE_STATISTICS is enabled for database 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?
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