-- Get database options
-- D Maxwell, June 2007
-- Just a wrapper script for sp_dboption
-- that gets the options for all dbs.

-- First, get a list of possible options:
create table ##all_options (option_name varchar(50) not null)
insert into ##all_options  exec master..sp_dboption

-- Now, get the list of databases.  
create table ##dbnames (dbname varchar(25) not null)
insert into ##dbnames select name from master..sysdatabases

-- Now, get the set options for each database.
-- First you need a table to hold them.
create table ##dboptions_set (
   dbname varchar(25) null, 
   dboption varchar(50) not null
)

-- Now you can insert the db names and options into the table.

while (select count(*) from ##dbnames) > 0
   begin
      declare @db varchar(25)
      set @db = (select top 1 dbname from ##dbnames)

      -- We do this part first since it returns multiple rows.
      insert into ##dboptions_set(dboption)
      exec master..sp_dboption @dbname = @db
      
      -- Now we go back and fill in the db names.
      update ##dboptions_set
      set dbname = @db where dbname is null

      -- We're done with that one.  Loop to the next one.
      delete from ##dbnames where dbname = @db
   end

-- We have the list, now we make it useful.  First, 
-- we need to build a report table that shows all DBs
-- and all options.  An actual good use of a cross join.

-- Got to get the name list back, first.
insert into ##dbnames select name from master..sysdatabases

create table ##option_report ( 
   dbname varchar(25) not null,
   dboption varchar(50) not null,
   setting tinyint null
)

insert into ##option_report
   select d.dbname, o.option_name, 0
      from ##dbnames d cross join ##all_options o
      order by d.dbname

-- Now we set the options as set or not set, 
-- according to the ##dboptions_set table.

while (select count(*) from ##dboptions_set) > 0
   begin
      declare @dbchecked varchar(25)
      declare @optionset varchar(50)

      set @dbchecked = (select top 1 dbname from ##dboptions_set)
      set @optionset = (select top 1 dboption from ##dboptions_set)

      update ##option_report
         set setting = 1 
            where dbname = @dbchecked 
              and dboption = @optionset

      delete from ##dboptions_set 
         where dbname = @dbchecked 
         and dboption = @optionset
   end

update r
set setting = 1
from ##option_report r JOIN ##dboptions_set s 
on r.dbname = s.dbname and r.dboption = s.dboption

quote:

---

Originally posted by harsh_athalye

Why not a simple join?

update r
set setting = 1
from ##option_report r JOIN ##dboptions_set s 
on r.dbname = s.dbname and r.dboption = s.dboption

---

quote:

---

Also, why you need global temp table? why not local temp table?

---