Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_dboption

  No additional text.


Syntax
create procedure sys.sp_dboption	-- 1999/08/09 18:25
	@dbname sysname = NULL,			-- database name to change
	@optname varchar(35) = NULL,	-- option name to turn on/off
	@optvalue varchar(10) = NULL	-- true or false
as
	set nocount    on

	declare @dbid int			-- dbid of the database
	declare @catvalue int		-- number of category option
	declare @optcount int		-- number of options like @optname
	declare @allstatopts int	-- bit map off all options stored in sysdatqabases.status
								-- that can be set by sp_dboption.
	declare @alloptopts int		-- bit map off all options stored in sysdatqabases.status
								-- that can be set by sp_dboption.
	declare @allcatopts int		-- bit map off all options stored in sysdatqabases.category
								-- that can be set by sp_dboption.
	declare @exec_stmt nvarchar(max)
	declare @fulloptname varchar(35)
	declare @alt_optname varchar(50)
	declare @alt_optvalue varchar(30)
	declare @optnameIn varchar(35)
	
	select @optnameIn = @optname
		   ,@optname = LOWER (@optname collate Latin1_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=number from master.dbo.spt_values where type = 'D'
		and name = 'ALL SETTABLE OPTIONS'

	select @allcatopts=number from master.dbo.spt_values where type = 'DC'
		and name = 'ALL SETTABLE OPTIONS'

	select @alloptopts=number from master.dbo.spt_values where type = 'D2'
		and name = 'ALL SETTABLE OPTIONS'

	if @dbname is null
	begin
		select 'Settable database options:' = name
			from master.dbo.spt_values
			where (type = 'D'
				and number & @allstatopts <> 0
				and number not in (0,@allstatopts))	-- Eliminate non-option entries
			 or (type = 'DC'
				and number & @allcatopts <> 0
				and number not in (0,@allcatopts))
			 or (type = 'D2'
				and number & @alloptopts <> 0
				and number not in (0,@alloptopts))
			order by name
		return (0)
	end

	--  Verify the database name and get info
	select @dbid = dbid
		from master.dbo.sysdatabases
		where name = @dbname

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

	-- If no option was supplied, display current settings.
	if @optname is null
	begin
		select 'The following options are set:' = v.name
			from master.dbo.spt_values v, master.dbo.sysdatabases d
			where d.name=@dbname
				   and ((number & @allstatopts <> 0
						 and number not in (-1,@allstatopts)
						 and v.type = 'D'
						 and (v.number & d.status)=v.number)
					 or (number & @allcatopts <> 0
						 and number not in (-1,@allcatopts)
						 and v.type = 'DC'
						 and d.category & v.number <> 0)
					 or (number & @alloptopts <> 0
						 and number not in (-1,@alloptopts)
						 and v.type = 'D2'
						 and d.status2 & v.number <> 0))
		return(0)
	end

	if @optvalue is not null and lower(@optvalue) not in ('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)
		from master.dbo.spt_values
		where lower(name collate Latin1_General_CI_AS) like '%' + @optname + '%'
			and ((type = 'D'
				  and number & @allstatopts <> 0
				  and number not in (-1,@allstatopts))
				or (type = 'DC'
					  and number & @allcatopts <> 0
					  and number not in (-1,@allcatopts))
				or (type = 'D2'
					  and number & @alloptopts <> 0
					  and number not in (-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
			from master.dbo.spt_values
			where (type = 'D'
					and number & @allstatopts <> 0
					and number not in (-1,@allstatopts)) -- Eliminate non-option entries
				or (type = 'DC'
					and number & @allcatopts <> 0
					and number not in (-1,@allcatopts))
				or (type = 'D2'
					and number & @alloptopts <> 0
					and number not in (-1,@alloptopts))
			order by name

		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 ' '

		select duplicate_options = name
		from master.dbo.spt_values
		where lower(name collate Latin1_General_CI_AS) like '%' + @optname + '%'
			and ((type = 'D'
				 and number & @allstatopts <> 0
				 and number not in (-1,@allstatopts))
			  or (type = 'DC'
				 and number & @allcatopts <> 0
				 and number not in (-1,@allcatopts))
			  or (type = 'D2'
				 and number & @alloptopts <> 0
				 and number not in (-1,@alloptopts))
			)
		return (1)
	end

	--  Just want to see current setting of specified option.
	if @optvalue is null
	begin
		select OptionName = v.name,
			CurrentSetting = (case
				  when ( ((v.number & d.status) = v.number
						  and v.type = 'D')
					  or (d.category & v.number <> 0
						   and v.type = 'DC')
					  or (d.status2 & v.number <> 0
						   and v.type = 'D2')
					   )
					 then 'ON'
				  when not
					   ( ((v.number & d.status) = v.number
						  and v.type = 'D')
					  or (d.category & v.number <> 0
						   and v.type = 'DC')
					  or (d.status2 & v.number <> 0
						   and v.type = 'D2')
					   )
					 then 'OFF'
			   end)
			from master.dbo.spt_values v, master.dbo.sysdatabases d
			where d.name=@dbname
			   and ((v.number & @allstatopts <> 0
					 and v.number not in (-1,@allstatopts)	-- Eliminate non-option entries
					 and v.type = 'D')
				 or (v.number & @allcatopts <> 0
					 and v.number not in (-1,@allcatopts)	-- Eliminate non-option entries
					 and v.type = 'DC')
				 or (v.number & @alloptopts <> 0
					 and v.number not in (-1,@alloptopts)	-- Eliminate non-option entries
					 and v.type = 'D2')
				   )
				and lower(v.name) = lower(@fulloptname)

		return (0)
	end

	select @catvalue = 0
	select @catvalue = number
		  from master.dbo.spt_values
		  where lower(name) = lower(@fulloptname)
		  and type = 'DC'

	-- if setting replication option, call sp_replicationdboption directly
	if (@catvalue <> 0)
	begin
		select @alt_optvalue = (case lower(@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'
				else quotename(@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 = (case lower(@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_optvalue when 'ON' then 'OFFLINE' else 'ONLINE' end)
			when 'read only' then (case @alt_optvalue when 'ON' then 'READ_ONLY' else 'READ_WRITE' end)
			when 'dbo use only' then (case @alt_optvalue when 'ON' then 'RESTRICTED_USER' else 'MULTI_USER' end)
			when 'single user' then (case @alt_optvalue when '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
			if databaseproperty(@dbname, 'IsSingleUser') = 1
			begin
				raiserror(5066,-1,-1);
				return (1)
			end
		end
		else
		begin
			if databaseproperty(@dbname, 'IsDBOOnly') = 0
				return (0)
		end
	end

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

	select @alt_optvalue = (case @fulloptname
		when 'default to local cursor' then (case @alt_optvalue when '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)

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

	if lower(@fulloptname) = 'trunc. log on chkpt.'
	begin
		if @alt_optvalue = 'ON'
		begin
			if databaseproperty(@dbname, 'IsBulkCopy') = 1
				select @alt_optvalue = 'RECMODEL_70BACKCOMP'
			else
				select @alt_optvalue = 'SIMPLE'
		end
		else
		begin
			if databaseproperty(@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

 
Last revision 2008RTM
See also

  sp_autostats (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash