USE [master] | |
GO | |
/****** Object: StoredProcedure [dbo].[sp_dboption] Script Date: 21.03.2012 7:33:37 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE procedure [dbo].[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 | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment