SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure 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(550)
declare @fulloptname varchar(35)
declare @alt_optname varchar(50)
declare @alt_optvalue varchar(30)
/*
** 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)
