top of page
Search

Where did my [sys].[sp_dboption] go?

Well in SQL Server 2012, to be quite blunt, [sys].[sp_dboption] has been deprecated, dumped, canned or if  you prefer, sent to Zim. Now isn’t that just dandy..because you will only realise this only when it is too late. But on the other hand Darren, you can always do a quick fix, all you need is an earlier flavour of SQL Server and some creative thinking. Open SSMS and explore your database in question. 



USE[master]
GO
/****** Object: StoredProcedure [sys].[sp_dboption] Script Date: 2013-01-14 10:49:58 AM ******/
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
/*ALTER procedure [sys].[sp_dboption] -- 1999/08/09 18:25*/
CREATEprocedure[sp_dboption] -- Fixed alter statement
 @dbnamesysname=NULL, -- database name to change
 @optnamevarchar(35)=NULL, -- option name to turn on/off
 @optvaluevarchar(10)=NULL -- true or false
as
 setnocount on

declare@dbidint -- dbid of the database
 declare@catvalueint -- number of category option
 declare@optcountint -- number of options like @optname
 declare@allstatoptsint -- bit map off all options stored in sysdatqabases.status
 -- that can be set by sp_dboption.
 declare@alloptoptsint -- bit map off all options stored in sysdatqabases.status
 -- that can be set by sp_dboption.
 declare@allcatoptsint -- bit map off all options stored in sysdatqabases.category
 -- that can be set by sp_dboption.
 declare@exec_stmtnvarchar(max)
 declare@fulloptnamevarchar(35)
 declare@alt_optnamevarchar(50)
 declare@alt_optvaluevarchar(30)
 declare@optnameInvarchar(35)

 select@optnameIn=@optname
 ,@optname=LOWER(@optnamecollateLatin1_General_CI_AS)

-- If no @dbname given, just list the possible dboptions.
 -- Only certain status bits may be set or cleared by sp_dboption.

-- Get bitmap of all options that can be set by sp_dboption.
 select@allstatopts=numberfrommaster.dbo.spt_valueswheretype='D'
 andname='ALL SETTABLE OPTIONS'

select@allcatopts=numberfrommaster.dbo.spt_valueswheretype='DC'
 andname='ALL SETTABLE OPTIONS'

select@alloptopts=numberfrommaster.dbo.spt_valueswheretype='D2'
 andname='ALL SETTABLE OPTIONS'

if@dbnameisnull
 begin
 select'Settable database options:'=name
 frommaster.dbo.spt_values
 where (type='D'
 andnumber&@allstatopts<> 0
 andnumbernotin(0,@allstatopts)) -- Eliminate non-option entries
 or(type='DC'
 andnumber&@allcatopts<> 0
 andnumbernotin(0,@allcatopts))
 or(type='D2'
 andnumber&@alloptopts<> 0
 andnumbernotin(0,@alloptopts))
 orderbyname
 return (0)
 end

-- Verify the database name and get info
 select@dbid=dbid
 frommaster.dbo.sysdatabases
 wherename=@dbname

-- If @dbname not found, say so and list the databases.
 if@dbidisnull
 begin
 raiserror(15010,-1,-1,@dbname)
 print' '
 select'Available databases:'=name
 frommaster.dbo.sysdatabases
 return (1)
 end

-- If no option was supplied, display current settings.
 if@optnameisnull
 begin
 select'The following options are set:'=v.name
 frommaster.dbo.spt_valuesv,master.dbo.sysdatabasesd
 whered.name=@dbname
 and((number&@allstatopts<> 0
 andnumbernotin(-1,@allstatopts)
 andv.type='D'
 and(v.number&d.status)=v.number)
 or(number&@allcatopts<> 0
 andnumbernotin(-1,@allcatopts)
 andv.type='DC'
 andd.category&v.number<> 0)
 or(number&@alloptopts<> 0
 andnumbernotin(-1,@alloptopts)
 andv.type='D2'
 andd.status2&v.number<> 0))
 return(0)
 end

if@optvalueisnotnullandlower(@optvalue)notin('true','false','on','off')
 begin
 raiserror(15241,-1,-1)
 return (1)
 end

-- Use @optname and try to find the right option.
 -- If there isn't just one, print appropriate diagnostics and return.
 select@optcount=count(*),@fulloptname=min(name)
 frommaster.dbo.spt_values
 wherelower(namecollateLatin1_General_CI_AS)like'%'+@optname+'%'
 and((type='D'
 andnumber&@allstatopts<> 0
 andnumbernotin(-1,@allstatopts))
 or(type='DC'
 andnumber&@allcatopts<> 0
 andnumbernotin(-1,@allcatopts))
 or(type='D2'
 andnumber&@alloptopts<> 0
 andnumbernotin(-1,@alloptopts)))

-- If no option, show the user what the options are.
 if@optcount= 0
 begin
 raiserror(15011,-1,-1,@optnameIn)
 print' '

select'Settable database options:'=name
 frommaster.dbo.spt_values
 where (type='D'
 andnumber&@allstatopts<> 0
 andnumbernotin(-1,@allstatopts))-- Eliminate non-option entries
 or(type='DC'
 andnumber&@allcatopts<> 0
 andnumbernotin(-1,@allcatopts))
 or(type='D2'
 andnumber&@alloptopts<> 0
 andnumbernotin(-1,@alloptopts))
 orderbyname

return (1)
 end

-- If more than one option like @optname, show the duplicates and return.
 if@optcount> 1
 begin
 raiserror(15242,-1,-1,@optnameIn)
 print' '

selectduplicate_options=name
 frommaster.dbo.spt_values
 wherelower(namecollateLatin1_General_CI_AS)like'%'+@optname+'%'
 and((type='D'
 andnumber&@allstatopts<> 0
 andnumbernotin(-1,@allstatopts))
 or(type='DC'
 andnumber&@allcatopts<> 0
 andnumbernotin(-1,@allcatopts))
 or(type='D2'
 andnumber&@alloptopts<> 0
 andnumbernotin(-1,@alloptopts))
 )
 return (1)
 end

-- Just want to see current setting of specified option.
 if@optvalueisnull
 begin
 selectOptionName=v.name,
 CurrentSetting=(case
 when (((v.number&d.status)=v.number
 andv.type='D')
 or(d.category&v.number<> 0
 andv.type='DC')
 or(d.status2&v.number<> 0
 andv.type='D2')
 )
 then'ON'
 whennot
 (((v.number&d.status)=v.number
 andv.type='D')
 or(d.category&v.number<> 0
 andv.type='DC')
 or(d.status2&v.number<> 0
 andv.type='D2')
 )
 then'OFF'
 end)
 frommaster.dbo.spt_valuesv,master.dbo.sysdatabasesd
 whered.name=@dbname
 and((v.number&@allstatopts<> 0
 andv.numbernotin(-1,@allstatopts) -- Eliminate non-option entries
 andv.type='D')
 or(v.number&@allcatopts<> 0
 andv.numbernotin(-1,@allcatopts) -- Eliminate non-option entries
 andv.type='DC')
 or(v.number&@alloptopts<> 0
 andv.numbernotin(-1,@alloptopts) -- Eliminate non-option entries
 andv.type='D2')
 )
 andlower(v.name)=lower(@fulloptname)

return (0)
 end

select@catvalue= 0
 select@catvalue=number
 frommaster.dbo.spt_values
 wherelower(name)=lower(@fulloptname)
 andtype='DC'

-- if setting replication option, call sp_replicationdboption directly
 if (@catvalue<> 0)
 begin
 select@alt_optvalue=(caselower(@optvalue)
 when'true'then'true'
 when'on'then'true'
 else'false'
 end)

select@alt_optname=(case@catvalue
 when 1 then'publish'
 when 2 then'subscribe'
 when 4 then'merge publish'
 elsequotename(@fulloptname,'''')
 end)

select@exec_stmt=quotename(@dbname,'[') +'.dbo.sp_replicationdboption'

EXEC@exec_stmt@dbname,@alt_optname,@alt_optvalue
 return (0)
 end
 -- call Alter Database to set options

-- set option value in alter database
 select@alt_optvalue=(caselower(@optvalue)
 when'true' then'ON'
 when'on' then'ON'
 else'OFF'
 end)

-- set option name in alter database
 select@fulloptname=lower(@fulloptname)
 select@alt_optname=(case@fulloptname
 when'auto create statistics'then'AUTO_CREATE_STATISTICS'
 when'auto update statistics'then'AUTO_UPDATE_STATISTICS'
 when'autoclose'then'AUTO_CLOSE'
 when'autoshrink'then'AUTO_SHRINK'
 when'ansi padding'then'ANSI_PADDING'
 when'arithabort'then'ARITHABORT'
 when'numeric roundabort'then'NUMERIC_ROUNDABORT'
 when'ansi null default'then'ANSI_NULL_DEFAULT'
 when'ansi nulls'then'ANSI_NULLS'
 when'ansi warnings'then'ANSI_WARNINGS'
 when'concat null yields null'then'CONCAT_NULL_YIELDS_NULL'
 when'cursor close on commit'then'CURSOR_CLOSE_ON_COMMIT'
 when'torn page detection'then'TORN_PAGE_DETECTION'
 when'quoted identifier'then'QUOTED_IDENTIFIER'
 when'recursive triggers'then'RECURSIVE_TRIGGERS'
 when'default to local cursor'then'CURSOR_DEFAULT'
 when'offline'then (case@alt_optvaluewhen'ON'then'OFFLINE'else'ONLINE'end)
 when'read only'then (case@alt_optvaluewhen'ON'then'READ_ONLY'else'READ_WRITE'end)
 when'dbo use only'then (case@alt_optvaluewhen'ON'then'RESTRICTED_USER'else'MULTI_USER'end)
 when'single user'then (case@alt_optvaluewhen'ON'then'SINGLE_USER'else'MULTI_USER'end)
 when'select into/bulkcopy'then'RECOVERY'
 when'trunc. log on chkpt.'then'RECOVERY'
 when'db chaining'then'DB_CHAINING'
 else@alt_optname
 end)

if@fulloptname='dbo use only'
 begin
 if@alt_optvalue='ON'
 begin
 ifdatabaseproperty(@dbname,'IsSingleUser')= 1
 begin
 raiserror(5066,-1,-1);
 return (1)
 end
 end
 else
 begin
 ifdatabaseproperty(@dbname,'IsDBOOnly')= 0
 return (0)
 end
 end

if@fulloptname='single user'
 begin
 if@alt_optvalue='ON'
 begin
 ifdatabaseproperty(@dbname,'ISDBOOnly')= 1
 begin
 raiserror(5066,-1,-1);
 return (1)
 end
 end
 else
 begin
 ifdatabaseproperty(@dbname,'IsSingleUser')= 0
 return (0)
 end
 end

select@alt_optvalue=(case@fulloptname
 when'default to local cursor'then (case@alt_optvaluewhen'ON'then'LOCAL'else'GLOBAL'end)
 when'offline'then''
 when'read only'then''
 when'dbo use only'then''
 when'single user'then''
 else @alt_optvalue
 end)

iflower(@fulloptname)='select into/bulkcopy'
 begin
 if@alt_optvalue='ON'
 begin
 ifdatabaseproperty(@dbname,'IsTrunclog')= 1
 select@alt_optvalue='RECMODEL_70BACKCOMP'
 else
 select@alt_optvalue='BULK_LOGGED'
 end
 else
 begin
 ifdatabaseproperty(@dbname,'IsTrunclog')= 1
 select@alt_optvalue='SIMPLE'
 else
 select@alt_optvalue='FULL'
 end
 end

iflower(@fulloptname)='trunc. log on chkpt.'
 begin
 if@alt_optvalue='ON'
 begin
 ifdatabaseproperty(@dbname,'IsBulkCopy')= 1
 select@alt_optvalue='RECMODEL_70BACKCOMP'
 else
 select@alt_optvalue='SIMPLE'
 end
 else
 begin
 ifdatabaseproperty(@dbname,'IsBulkCopy')= 1
 select@alt_optvalue='BULK_LOGGED'
 else
 select@alt_optvalue='FULL'
 end
 end

-- construct the ALTER DATABASE command string
 select@exec_stmt='ALTER DATABASE '+quotename(@dbname)+' SET '+@alt_optname+' '+@alt_optvalue+' WITH NO_WAIT'
 EXEC (@exec_stmt)

if@@error<> 0
 begin
 raiserror(15627,-1,-1)
 return (1)
 end

return (0)-- sp_dboption
<div class="MsoNormal" style="line-height: normal; margin: 0;">
1 view0 comments

Recent Posts

See All

I have 2 identical SQL servers, with identical I refer to schema and not data. What I need to do is get the same data in both. This is simple replication and would imply pulling the data from Table 1

bottom of page