use master
go
create table #dboptupgrd
   (grp    tinyint       not null,
    dbid   int           not null,
    dbopt1 int           not null,
    dbopt2 int           not null,
    seq    tinyint       not null,
    txt    varchar (78)  not null)

--
--  LET'S RUN FROM THE CORRECT DATABASE FOR sp_dboption
--
insert into #dboptupgrd
   values (0, 0, 0, 0, 1, 'use master')

--
--  GENERATE sp_dboption COMMANDS FOR THE "D" OPTIONS.
--
insert into #dboptupgrd
   select 1,
          d.dbid,
          vD.number,
          0,
          1,
          'sp_dboption '
          +  convert (char (26), d.name)
          +  ', "'
          +  convert (char (29), vD.name  +  '"')
          +  ', false'
   from master.dbo.sysdatabases d,
        master.dbo.spt_values vD
   where vD.type                = 'D'
     and (d.status & vD.number) = vD.number
     and vD.name                not in ("failed upgrade",
                                        "trunc. log on chkpt.",
                                        "don't recover",
                                        "not recovered",
                                        "ALL SETTABLE OPTIONS",
                                        "inconsistent security label")

--
--  tempdb REQUIRES THIS OPTION TO BE KEPT.
--
delete from #dboptupgrd
where charindex ('sp_dboption tempdb ', txt)  = 1
  and charindex ('select into/bulkcopy', txt) > 0

--
--  GENERATE sp_dboption COMMANDS FOR THE "D2" OPTIONS.
--
insert into #dboptupgrd
   select 1,
          d.dbid,
          0,
          vD2.number,
          1,
          'sp_dboption '
          +  convert (char (26), d.name)
          +  ', "'
          +  convert (char (29), vD2.name  +  '"')
          +  ', false'
   from master.dbo.sysdatabases d,
        master.dbo.spt_values vD2
   where vD2.type                  = 'vD2'
     and (d.status2 & vD2.number)  = vD2.number
     and vD2.name                 in ('abort tran on log full',
                                      'no free space acctg',
                                      'auto identity',
                                      'identity in nonunique index')


--
--  GENERATE THE "use <db>" AND "checkpoint" COMMANDS.
--
insert into #dboptupgrd
   select distinct 3, dbid, 0, 0, 1, 'use '  +  db_name (dbid)
   from #dboptupgrd

insert into #dboptupgrd
   select 3, dbid, 0, 1, 0, 'checkpoint'
   from #dboptupgrd
   where grp = 3

--
--  VERIFY THAT ALL OPTIONS ARE OFF
--
insert into #dboptupgrd
   values (4, 0, 0, 0, 0, 'sp_db')

--
--  GENERATE THE "go" STATEMENTS FOR ALL OF THE ABOVE.
--
insert into #dboptupgrd
   select grp, dbid, dbopt1, dbopt2, 2, 'go'
   from #dboptupgrd

--
--  CRANK OUT THE SQL IN CORRECT SEQUENCE.
--
select 'sp_dboption TEXT' = txt
from #dboptupgrd
order by grp, dbid, dbopt1, dbopt2, seq
go
