фирма 1С:
USE[master]
GO
/****** Object: StoredProcedure [dbo].[sp_dboption] Script Date: 21.03.2012 7:33:37 ******/
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATEprocedure[dbo].[sp_dboption]-- 1999/08/09 18:25
@dbnamesysname=NULL,-- database name to change
@optnamevarchar(35)=NULL,-- option name to turn on/off
@optvaluevarchar(10)=NULL-- true or false
as
setnocounton
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
z88; -- 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
GO
