/*
** ProcSyst.SQL
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
*/

go
use master
go
checkpoint
go
set nocount on
set implicit_transactions off
set ansi_nulls off
set quoted_identifier on	-- Force all ye devs to do this correctly!
go

declare @vdt varchar(99)
select  @vdt = convert(varchar,getdate(),113)
raiserror('Starting Install\ProcSyst.SQL at  %s',0,1,@vdt) with nowait
go

print ' '
print 'Making sure that updates to system tables are allowed.'
go

declare  @int1			integer
--dbcc getvalue('current_version')  --1=4.21A ,400-406=6.0 ,407-?=6.5, currently 408=7.0
--select @dbcc_current_version = @@error

if (     object_id('sp_configure','P') IS NOT NULL
    AND  1 <> (select value from syscurconfigs where config = 102)
   )
	begin						--Query tree compatible
	exec @int1 = sp_configure 'allow updates',1
	if @@error <> 0 or @int1 <> 0
		raiserror('Bad sp_configure exec at top of ProcSyst.SQL, killing spid.'
			,22,127) with log
	reconfigure with override
	end
go

---- Make sure server was started in single user mode or that sp_configure was used
----    to enable updates to system tables.

if (select value from syscurconfigs where config = 102) <> 1
	raiserror('Cannot run ProcSyst.SQL unless updates to system tables are enabled.  Shutdown server and restart with the ''-m'' option or use sp_configure to enable updates to system tables.'
			,22,127) with log
go

exec sp_MS_upd_sysobj_category 1  --Capture now_datetime for use below.

go

print ' '
print 'Dropping procedures that will be (re)created.'
go

----- ---

if object_id('sp_fallback_activate_svr','P') IS NOT NULL
	drop procedure sp_fallback_activate_svr
if object_id('sp_fallback_activate_svr_db','P') IS NOT NULL
	drop procedure sp_fallback_activate_svr_db

if object_id('sp_fallback_deactivate_svr','P') IS NOT NULL
	drop procedure sp_fallback_deactivate_svr
if object_id('sp_fallback_deactivate_svr_db','P') IS NOT NULL
	drop procedure sp_fallback_deactivate_svr_db

if object_id('sp_fallback_enroll_svr_db','P') IS NOT NULL
	drop procedure sp_fallback_enroll_svr_db

if object_id('sp_fallback_help','P') IS NOT NULL
	drop procedure sp_fallback_help

if object_id('sp_fallback_help_db_dev','P') IS NOT NULL
	drop procedure sp_fallback_help_db_dev

if object_id('sp_fallback_MS_enroll_db','P') IS NOT NULL
	drop procedure sp_fallback_MS_enroll_db

if object_id('sp_fallback_MS_enroll_dev','P') IS NOT NULL
	drop procedure sp_fallback_MS_enroll_dev

if object_id('sp_fallback_MS_enroll_usg','P') IS NOT NULL
	drop procedure sp_fallback_MS_enroll_usg

if object_id('sp_fallback_MS_sel_fb_svr','P') IS NOT NULL
	drop procedure sp_fallback_MS_sel_fb_svr

if object_id('sp_fallback_MS_verify_ri','P') IS NOT NULL
	drop procedure sp_fallback_MS_verify_ri

if object_id('sp_fallback_permanent_svr','P') IS NOT NULL
	drop procedure sp_fallback_permanent_svr

if object_id('sp_fallback_upd_dev_drive','P') IS NOT NULL
	drop procedure sp_fallback_upd_dev_drive

if object_id('sp_fallback_withdraw_svr_db','P') IS NOT NULL
	drop procedure sp_fallback_withdraw_svr_db


go
------------------------------------------------------------

if object_id('sp_a_count_bits_on','P') IS NOT NULL
		     drop procedure sp_a_count_bits_on

if object_id('sp_addextendedproc','P') IS NOT NULL
	drop procedure sp_addextendedproc

if object_id('sp_addmessage','P') IS NOT NULL
	drop procedure sp_addmessage

if object_id('sp_addremotelogin','P') IS NOT NULL
	drop procedure sp_addremotelogin

if object_id('sp_addsegment','P') IS NOT NULL
	drop procedure sp_addsegment
go

checkpoint
go

if object_id('sp_addtype','P') IS NOT NULL
	drop procedure sp_addtype

if object_id('sp_addumpdevice','P') IS NOT NULL
	drop procedure sp_addumpdevice

if object_id('sp_altermessage','P') IS NOT NULL
        drop procedure sp_altermessage

if object_id('sp_attach_db','P') IS NOT NULL
	drop procedure sp_attach_db

if object_id('sp_attach_single_file_db','P') IS NOT NULL
	drop procedure sp_attach_single_file_db
go

if object_id('sp_bindefault','P') IS NOT NULL
	drop procedure sp_bindefault

if object_id('sp_bindrule','P') IS NOT NULL
	drop procedure sp_bindrule

if object_id('sp_blockcnt','P') IS NOT NULL
	drop procedure sp_blockcnt

if object_id('sp_checknames','P') IS NOT NULL
	drop procedure sp_checknames
go

if object_id('sp_configure','P') IS NOT NULL
	drop procedure sp_configure

if object_id('sp_create_removable','P') IS NOT NULL
	drop procedure sp_create_removable

if object_id('sp_createstats','P') IS NOT NULL
	drop procedure sp_createstats

if object_id('sp_cycle_errorlog','P') IS NOT NULL
	drop procedure sp_cycle_errorlog

if object_id('sp_certify_removable','P') IS NOT NULL
	drop procedure sp_certify_removable

if object_id('sp_check_removable','P') IS NOT NULL
	drop procedure sp_check_removable

if object_id('sp_cnst_csr','P') IS NOT NULL
	drop procedure sp_cnst_csr

if object_id('sp_coalesce_fragments','P') IS NOT NULL
	drop procedure sp_coalesce_fragments

if object_id('sp_dboption','P') IS NOT NULL
	drop procedure sp_dboption

if object_id('sp_dbcmptlevel','P') IS NOT NULL
	drop procedure sp_dbcmptlevel

if object_id('sp_dbremove','P') IS NOT NULL
	drop procedure sp_dbremove

if object_id('sp_depends','P') IS NOT NULL
	drop procedure sp_depends

if object_id('sp_detach_db','P') IS NOT NULL
	drop procedure sp_detach_db

if object_id('sp_diskdefault','P') IS NOT NULL
	drop procedure sp_diskdefault
go

if object_id('sp_dropdevice','P') IS NOT NULL
	drop procedure sp_dropdevice

if object_id('sp_dropdumpdevice','P') IS NOT NULL
	drop procedure sp_dropdumpdevice

if object_id('sp_dropextendedproc','P') IS NOT NULL
	drop procedure sp_dropextendedproc
go

if object_id('sp_dropmessage','P') IS NOT NULL
	drop procedure sp_dropmessage

if object_id('sp_droptype','P') IS NOT NULL
	drop procedure sp_droptype

if object_id('sp_dropremotelogin','P') IS NOT NULL
	drop procedure sp_dropremotelogin

if object_id('sp_dropsegment','P') IS NOT NULL
	drop procedure sp_dropsegment

if object_id('sp_extendsegment','P') IS NOT NULL
	drop procedure sp_extendsegment
go

if object_id('sp_help','P') IS NOT NULL
	drop procedure sp_help

if object_id('sp_help_revdatabase','P') IS NOT NULL
		     drop procedure sp_help_revdatabase

if object_id('sp_helpconstraint','P') IS NOT NULL
	drop procedure sp_helpconstraint

if object_id('sp_helpdb','P') IS NOT NULL
	drop procedure sp_helpdb

if object_id('sp_helpdevice','P') IS NOT NULL
	drop procedure sp_helpdevice

if object_id('sp_helpextendedproc','P') IS NOT NULL
	drop procedure sp_helpextendedproc

if object_id('sp_helpfile','P') IS NOT NULL
	drop procedure sp_helpfile

if object_id('sp_helpfilegroup','P') IS NOT NULL
	drop procedure sp_helpfilegroup

if object_id('sp_helpgroup','P') IS NOT NULL
	drop procedure sp_helpgroup

if object_id('sp_helpindex','P') IS NOT NULL
	drop procedure sp_helpindex

if object_id('sp_helpstats','P') IS NOT NULL
	drop procedure sp_helpstats
go

if exists (select * from sysobjects
	   where xtype = N'IF' and uid = USER_ID('system_function_schema') and
		name = 'fn_dblog')
	drop function system_function_schema.fn_dblog
go

if exists (select * from sysobjects
	   where xtype = N'TF' and uid = USER_ID('system_function_schema') and
		name = 'fn_helpcollations')
	drop function system_function_schema.fn_helpcollations
go

if exists (select * from sysobjects
	   where xtype = N'TF' and uid = USER_ID('system_function_schema') and
		name = 'fn_trace_getinfo')
	drop function system_function_schema.fn_trace_getinfo
go

if exists (select * from sysobjects
	   where xtype = N'IF' and uid = USER_ID('system_function_schema') and
		name = 'fn_trace_gettable')
	drop function system_function_schema.fn_trace_gettable
go

if exists (select * from sysobjects
	   where xtype = N'TF' and uid = USER_ID('system_function_schema') and
		name = 'fn_trace_geteventinfo')
	drop function system_function_schema.fn_trace_geteventinfo
go

if exists (select * from sysobjects
	   where xtype = N'TF' and uid = USER_ID('system_function_schema') and
		name = 'fn_trace_getfilterinfo')
	drop function system_function_schema.fn_trace_getfilterinfo
go

if exists (select * from sysobjects
	   where xtype = N'TF' and uid = USER_ID('system_function_schema') and
		name = 'fn_databaseproperties')
	drop function system_function_schema.fn_databaseproperties
go

if exists (select * from sysobjects
	   where xtype = N'TF' and uid = USER_ID('system_function_schema') and
		name = 'fn_servershareddrives')
	drop function system_function_schema.fn_servershareddrives
go

if exists (select * from sysobjects
	   where xtype = N'TF' and uid = USER_ID('system_function_schema') and
		name = 'fn_virtualfilestats')
	drop function system_function_schema.fn_virtualfilestats
go

if exists (select * from sysobjects
	   where xtype = N'TF' and uid = USER_ID('system_function_schema') and
		name = 'fn_virtualservernodes')
	drop function system_function_schema.fn_virtualservernodes
go

if exists (select * from sysobjects
	   where xtype = N'TF' and uid = USER_ID('system_function_schema') and
		name = 'fn_get_sql')
	drop function system_function_schema.fn_get_sql
go

checkpoint
go

if object_id('sp_helplanguage','P') IS NOT NULL
	drop procedure sp_helplanguage

if object_id('sp_helplog','P') IS NOT NULL
	drop procedure sp_helplog

if object_id('sp_helplogins','P') IS NOT NULL
		     drop procedure sp_helplogins

if object_id('sp_helprotect','P') IS NOT NULL
	drop procedure sp_helprotect

if object_id('sp_helptext','P') IS NOT NULL
	drop procedure sp_helptext

if object_id('sp_helpuser','P') IS NOT NULL
	drop procedure sp_helpuser

if object_id('sp_helpremotelogin','P') IS NOT NULL
	drop procedure sp_helpremotelogin

if object_id('sp_helpsegment','P') IS NOT NULL
	drop procedure sp_helpsegment

if object_id('sp_helpsort','P') IS NOT NULL
	drop procedure sp_helpsort

if object_id('sp_helpsql','P') IS NOT NULL
	drop procedure sp_helpsql

if object_id('sp_indexoption','P') IS NOT NULL
	drop procedure sp_indexoption

if object_id('sp_lock','P') IS NOT NULL
	drop procedure sp_lock

if object_id('sp_getapplock','P') IS NOT NULL
	drop procedure sp_getapplock

if object_id('sp_releaseapplock','P') IS NOT NULL
	drop procedure sp_releaseapplock
go

if object_id('sp_logdevice','P') IS NOT NULL
	drop procedure sp_logdevice

if object_id('sp_monitor','P') IS NOT NULL
	drop procedure sp_monitor

if object_id('sp_namecrack','P') IS NOT NULL
	drop procedure sp_namecrack

if object_id('sp_namecrack_qi','P') IS NOT NULL
	drop procedure sp_namecrack_qi

if object_id('sp_objectfilegroup','P') IS NOT NULL
	drop procedure sp_objectfilegroup

if object_id('sp_placeobject','P') IS NOT NULL
	drop procedure sp_placeobject

if object_id('sp_procoption','P') IS NOT NULL
	drop procedure sp_procoption

if object_id('sp_processmail','P') IS NOT NULL
	drop procedure sp_processmail
go

if object_id('sp_recompile','P') IS NOT NULL
	drop procedure sp_recompile

if object_id('sp_remoteoption','P') IS NOT NULL
	drop procedure sp_remoteoption

if object_id('sp_rename','P') IS NOT NULL
	drop procedure sp_rename

if object_id('sp_renamedb','P') IS NOT NULL
	drop procedure sp_renamedb

if object_id('sp_remove_tempdb_file','P') IS NOT NULL
        drop procedure sp_remove_tempdb_file

if object_id('sp_resetstatus','P') IS NOT NULL
   drop procedure sp_resetstatus
go

if object_id('sp_add_file_recover_suspect_db','P') IS NOT NULL
	drop procedure sp_add_file_recover_suspect_db

if object_id('sp_add_data_file_recover_suspect_db','P') IS NOT NULL
	drop procedure sp_add_data_file_recover_suspect_db

if object_id('sp_add_log_file_recover_suspect_db','P') IS NOT NULL
	drop procedure sp_add_log_file_recover_suspect_db
go

if object_id('sp_spaceused','P') IS NOT NULL
	drop procedure sp_spaceused

if object_id('sp_checktabletempsize', 'P') IS NOT NULL
	drop procedure sp_checktabletempsize

if object_id('sp_checkdbtempsize', 'P') IS NOT NULL
	drop procedure sp_checkdbtempsize

if object_id('sp_sqlexec','P') IS NOT NULL
	drop procedure sp_sqlexec
go

if object_id('sp_tableoption','P') IS NOT NULL
	drop procedure sp_tableoption

if object_id('sp_invalidate_textptr','P') IS NOT NULL
	drop procedure sp_invalidate_textptr

if object_id('sp_tempdbspace','P') IS NOT NULL
	drop procedure sp_tempdbspace

if object_id('sp_unbindefault','P') IS NOT NULL
	drop procedure sp_unbindefault

if object_id('sp_unbindrule','P') IS NOT NULL
	drop procedure sp_unbindrule


if object_id('sp_user_counter1','P') IS NOT NULL
        drop procedure sp_user_counter1
if object_id('sp_user_counter2','P') IS NOT NULL
        drop procedure sp_user_counter2
if object_id('sp_user_counter3','P') IS NOT NULL
        drop procedure sp_user_counter3
if object_id('sp_user_counter4','P') IS NOT NULL
        drop procedure sp_user_counter4
if object_id('sp_user_counter5','P') IS NOT NULL
        drop procedure sp_user_counter5
if object_id('sp_user_counter6','P') IS NOT NULL
        drop procedure sp_user_counter6
if object_id('sp_user_counter7','P') IS NOT NULL
        drop procedure sp_user_counter7
if object_id('sp_user_counter8','P') IS NOT NULL
        drop procedure sp_user_counter8
if object_id('sp_user_counter9','P') IS NOT NULL
        drop procedure sp_user_counter9
if object_id('sp_user_counter10','P') IS NOT NULL
        drop procedure sp_user_counter10
go


if object_id('sp_validaltlang','P') IS NOT NULL
	drop procedure sp_validaltlang

if object_id('sp_validlang','P') IS NOT NULL
	drop procedure sp_validlang

if object_id('sp_validname','P') IS NOT NULL
	drop procedure sp_validname

if object_id('sp_who','P') IS NOT NULL
	drop procedure sp_who

if object_id('sp_who2','P') IS NOT NULL
	drop procedure sp_who2
go

if object_id('sp_updatestats','P') IS NOT NULL
	drop procedure sp_updatestats
go

if object_id('sp_autostats','P') IS NOT NULL
	drop procedure sp_autostats
go

if object_id('sp_helptrigger','P') IS NOT NULL
	drop procedure sp_helptrigger
go

if object_id('sp_dropextendedproperty','P') IS NOT NULL
	drop procedure sp_dropextendedproperty
if object_id('sp_addextendedproperty','P') IS NOT NULL
	drop procedure sp_addextendedproperty
if object_id('sp_updateextendedproperty','P') IS NOT NULL
	drop procedure sp_updateextendedproperty
if object_id('sp_validatepropertyinputs','P') IS NOT NULL
	drop procedure sp_validatepropertyinputs
if exists (select * from sysobjects
	   where xtype = N'TF' and uid = USER_ID('system_function_schema') and
		name = 'fn_listextendedproperty')
	drop function system_function_schema.fn_listextendedproperty
go

if object_id('sp_fixindex','P') IS NOT NULL
	drop procedure sp_fixindex
go

if object_id('MS_sqlctrs_users','P') IS NOT NULL
    drop proc MS_sqlctrs_users
go

checkpoint
go


-------------------------
 -- create XP early for sysdepends
raiserror('Special section to create extended procs early for sysdepends...',0,1)
go


raiserror(15339,-1,-1,'sp_dropextendedproc')
go
create procedure sp_dropextendedproc --- 1996/08/30 20:13
@functname nvarchar(517) -- name of function
as
/*
**  If we're in a transaction, disallow the dropping of the
**  extended stored procedure.
*/
set implicit_transactions off
if @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sp_dropextendedproc')
		return (1)
	end

/*
** Drop the extended procedure mapping.
*/
dbcc dropextendedproc( @functname )
return (0) -- sp_dropextendedproc
go



raiserror(15339,-1,-1,'sp_addextendedproc')
go
create procedure sp_addextendedproc --- 1996/08/30 20:13
@functname nvarchar(517),		/* (owner.)name of function to call */
@dllname varchar(255)		/* name of DLL containing function */
as
/*
**  If we're in a transaction, disallow the addition of the
**  extended stored procedure.
*/
set implicit_transactions off
if @@trancount > 0
begin
	raiserror(15002,-1,-1,'sp_addextendedproc')
	return (1)
end

-- Disallow 0-length string & NULL
if @dllname is null or datalength(@dllname) = 0
begin
	raiserror(15311,-1,-1,@dllname)
	return (1)
end

/*
** Create the extended procedure mapping.
*/
dbcc addextendedproc( @functname, @dllname)
return (0) -- sp_addextendedproc
go


raiserror(15339,-1,-1,'sp_helpextendedproc')
go
create procedure sp_helpextendedproc --- 1996/08/14 15:53
@funcname sysname = NULL
as

set nocount on

if (select count(*) from master.dbo.sysobjects where xtype = N'X ') = 0
	begin
		raiserror(15326,-1,-1)
		return (0)
	end


if @funcname is not null
begin
	/*
	**  Make sure the function name exists
	*/
	if not exists (select * from master.dbo.sysobjects
			where xtype = N'X '
			  and name = @funcname)
	begin
		raiserror(15019,-1,-1,@funcname)
		return (1)
	end
	/*print out select function name info*/
	select distinct name = o.name, dll = substring(c.text,1,255)
	from master.dbo.sysobjects o, master.dbo.syscomments c
	where o.id = c.id
		and o.name = @funcname
		and o.xtype = N'X '
	order by o.name
end
else
/*
**  or print out all function name info
*/
select distinct name = o.name, dll = substring(c.text,1,255)
	from master.dbo.sysobjects o, master.dbo.syscomments c
	where o.id = c.id
		and o.xtype = N'X '
	order by o.name

return (0) -- sp_helpextendedproc
go


---------------------------------------------------------------
raiserror('Now done creating the general system stored procs.  Start with miscellaneous tasks like xp_ and grants.',0,1)
---------------------------------------------------------------
go

---- Drop extended procs if they already exist now that sp_dropextendedproc has been created.

print ' '
print 'Dropping any existing extended stored procedures.'
go

if object_id('xp_addattach','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_addattach'

if object_id('xp_addmsgline','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_addmsgline'

if object_id('xp_cmdshell','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_cmdshell'

if object_id('sp_cursor','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_cursor'

if object_id('xp_userlock','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_userlock'

if object_id('sp_cursorclose','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_cursorclose'

if object_id('sp_cursorfetch','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_cursorfetch'

if object_id('sp_cursoropen','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_cursoropen'

if object_id('sp_cursoroption','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_cursoroption'

if object_id('xp_deletemail','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_deletemail'

if object_id('xp_enumgroups','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_enumgroups'

if object_id('xp_findnextmsg','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_findnextmsg'

if object_id('xp_logevent','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_logevent'

if object_id('xp_loginconfig','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_loginconfig'

if object_id('xp_logininfo','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_logininfo'

if object_id('xp_loginmail','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_loginmail'

if object_id('xp_logoffmail','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_logoffmail'

if object_id('xp_mailproclist','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_mailproclist'

if object_id('xp_msver','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_msver'

if object_id('xp_prepmsg','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_prepmsg'

if object_id('xp_readmail','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_readmail'

if object_id('xp_sendmail','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_sendmail'

if object_id('xp_sendmsg','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_sendmsg'

if object_id('xp_sprintf','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_sprintf'

if object_id('xp_sscanf','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_sscanf'

if object_id('xp_startmail','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_startmail'

if object_id('xp_stopmail','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_stopmail'

if object_id('xp_get_mapi_default_profile','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_get_mapi_default_profile'

if object_id('xp_get_mapi_profiles','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_get_mapi_profiles'

if object_id('xp_test_mapi_profile','X') IS NOT NULL
	exec sp_dropextendedproc 'xp_test_mapi_profile'
go

if object_id('sp_bindsession','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_bindsession'

if object_id('sp_getbindtoken','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_getbindtoken'

if object_id('sp_createorphan','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_createorphan'

if object_id('sp_droporphans','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_droporphans'

if object_id('sp_sdidebug','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_sdidebug'

if object_id('sp_executesql','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_executesql'

if object_id('sp_stmtpermissions','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_stmtpermissions'

if object_id('sp_objpermissions','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_objpermissions'

if object_id('sp_prepare','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_prepare'

if object_id('sp_execute','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_execute'

if object_id('sp_prepexec','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_prepexec'

if object_id('sp_prepexecrpc','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_prepexecrpc'

if object_id('sp_unprepare','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_unprepare'

if object_id('sp_cursorprepare','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_cursorprepare'

if object_id('sp_cursorexecute','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_cursorexecute'

if object_id('sp_cursorprepexec','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_cursorprepexec'

if object_id('sp_cursorunprepare','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_cursorunprepare'

if object_id('sp_reset_connection','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_reset_connection'

if object_id('sp_getschemalock','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_getschemalock'

if object_id('sp_releaseschemalock','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_releaseschemalock'

if object_id('sp_xml_preparedocument','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_xml_preparedocument'

if object_id('sp_xml_removedocument','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_xml_removedocument'

-- The following four extended procs only appear in shiloh beta 1
if object_id('sp_xml_insertfromxml','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_xml_insertfromxml'

if object_id('sp_xml_fetchintoxml','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_xml_fetchintoxml'

if object_id('sp_xml_removexml','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_xml_removexml'

if object_id('sp_xml_fetchdocument','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_xml_fetchdocument'

if object_id('sp_resyncprepare','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_resyncprepare'

if object_id('sp_resyncexecute','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_resyncexecute'

if object_id('sp_resyncexecutesql','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_resyncexecutesql'

if object_id('sp_resyncuniquetable','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_resyncuniquetable'

if object_id('sp_trace_create','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_trace_create'

if object_id('sp_trace_setevent','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_trace_setevent'

if object_id('sp_trace_setfilter','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_trace_setfilter'

if object_id('sp_trace_setstatus','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_trace_setstatus'

if object_id('sp_trace_generateevent','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_trace_generateevent'


go


-- Add extended stored procedures.
print ' '
print 'Adding extended stored procedures.'
go

execute sp_addextendedproc 'xp_cmdshell'	,'xplog70.dll'
execute sp_addextendedproc 'xp_logevent'	,'xplog70.dll'
execute sp_addextendedproc 'xp_sprintf'	,'xplog70.dll'
execute sp_addextendedproc 'xp_sscanf'		,'xplog70.dll'
execute sp_addextendedproc 'xp_msver'		,'xplog70.dll'
execute sp_addextendedproc 'xp_enumgroups'	,'xplog70.dll'
go

-- Add mail enabling extended procedures
--
-- IA64 related change. Due to missing libraries from Exchange SQLMail procs 
-- won't be available for Liberty B-1. Bug #352141
--
-- Stub will be there and will display a descriptive message
-- Bug #360731
--
execute sp_addextendedproc 'xp_startmail'                ,'sqlmap70.dll'
execute sp_addextendedproc 'xp_stopmail'                 ,'sqlmap70.dll'
execute sp_addextendedproc 'xp_sendmail'                 ,'sqlmap70.dll'
execute sp_addextendedproc 'xp_deletemail'               ,'sqlmap70.dll'
execute sp_addextendedproc 'xp_findnextmsg'              ,'sqlmap70.dll'
execute sp_addextendedproc 'xp_readmail'                 ,'sqlmap70.dll'
execute sp_addextendedproc 'xp_get_mapi_default_profile' ,'sqlmap70.dll'
execute sp_addextendedproc 'xp_get_mapi_profiles'        ,'sqlmap70.dll'
execute sp_addextendedproc 'xp_test_mapi_profile'        ,'sqlmap70.dll'

go

--Add extended stored procedures for NT integrated login security.
--
execute sp_addextendedproc 'xp_loginconfig'	,'xplog70.dll'
go

-- Add extended stored procedures for cursor support.
--
execute sp_addextendedproc 'sp_cursor'		,'(server internal)'
execute sp_addextendedproc 'sp_cursorclose'	,'(server internal)'
execute sp_addextendedproc 'sp_cursorfetch'	,'(server internal)'
execute sp_addextendedproc 'sp_cursoropen'	,'(server internal)'
execute sp_addextendedproc 'sp_cursoroption'	,'(server internal)'
go

-- Add extended stored procedures for bound session support.
--
execute sp_addextendedproc 'sp_bindsession'	,'(server internal)'
execute sp_addextendedproc 'sp_getbindtoken'	,'(server internal)'
go

-- Add extended stored procedures for orphaned text support for ODBC
--
execute sp_addextendedproc 'sp_createorphan' ,'(server internal)'
execute sp_addextendedproc 'sp_droporphans' ,'(server internal)'
go

-- Add extended stored procedures for XML text support
--
execute sp_addextendedproc 'sp_xml_preparedocument' ,'(server internal)'
execute sp_addextendedproc 'sp_xml_removedocument' ,'(server internal)'

go

-- Add extended stored procedures for SQLTrace support
--
execute sp_addextendedproc 'sp_trace_create'		,'(server internal)'
execute sp_addextendedproc 'sp_trace_setevent'		,'(server internal)'
execute sp_addextendedproc 'sp_trace_setfilter'		,'(server internal)'
execute sp_addextendedproc 'sp_trace_setstatus'		,'(server internal)'
execute sp_addextendedproc 'sp_trace_generateevent'	,'(server internal)'
go

-- This xproc will be used for SQL debugging and will be invoked either via RPC or SQL language.
--
execute sp_addextendedproc 'sp_sdidebug'	,'(server internal)'
go

-- This xproc will be used for server support for Prepare-Execute
--
execute sp_addextendedproc 'sp_executesql'	,'(server internal)'
go

-- Add extended stored procedures for application locks
--
execute sp_addextendedproc 'xp_userlock'	,'(server internal)'
go

-- Starfter requested security proc
--
execute sp_addextendedproc 'sp_prepare'	,'(server internal)'
go
execute sp_addextendedproc 'sp_execute'	,'(server internal)'
go
execute sp_addextendedproc 'sp_prepexec'	,'(server internal)'
go
execute sp_addextendedproc 'sp_prepexecrpc'	,'(server internal)'
go
execute sp_addextendedproc 'sp_unprepare'	,'(server internal)'
go
execute sp_addextendedproc 'sp_cursorprepare'	,'(server internal)'
go
execute sp_addextendedproc 'sp_cursorexecute'	,'(server internal)'
go
execute sp_addextendedproc 'sp_cursorprepexec'	,'(server internal)'
go
execute sp_addextendedproc 'sp_cursorunprepare'	,'(server internal)'
go

-- This xproc will be used for server support for connection-caching
--
execute sp_addextendedproc 'sp_reset_connection','(server internal)'
go

-- This xproc will be used for server support of schema locks
execute sp_addextendedproc 'sp_getschemalock','(server internal)'
execute sp_addextendedproc 'sp_releaseschemalock','(server internal)'
go

-- Add the access resync query extended proc
execute sp_addextendedproc 'sp_resyncprepare'	,'(server internal)'
go
execute sp_addextendedproc 'sp_resyncexecute'	,'(server internal)'
go
execute sp_addextendedproc 'sp_resyncexecutesql'	,'(server internal)'
go
execute sp_addextendedproc 'sp_resyncuniquetable'	,'(server internal)'
go

-- UPDATE A VIEW'S METADATA TO REFLECT CHANGES IN UNDERLYING TABLES --
if object_id('sp_refreshview','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_refreshview'
execute sp_addextendedproc 'sp_refreshview','(server internal)'
grant execute on sp_refreshview to public
go

-- SP_SETLOGIN USED BY AGENT
if object_id('sp_setuserbylogin','X') IS NOT NULL
	exec sp_dropextendedproc 'sp_setuserbylogin'
execute sp_addextendedproc 'sp_setuserbylogin','(server internal)'
go
grant execute on sp_setuserbylogin to public
go
exec sp_MS_marksystemobject 'sp_setuserbylogin'
go

-------------------------------------
raiserror('Creating the general purpose System Stored Procedures ....',0,1)
-------------------------------------
go


raiserror(15339,-1,-1,'sp_user_counter1 - N')
go

create proc sp_user_counter1 @newvalue int as
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 1', @newvalue)
go
create proc sp_user_counter2 @newvalue int as
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 2', @newvalue)
go
create proc sp_user_counter3 @newvalue int as
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 3', @newvalue)
go
create proc sp_user_counter4 @newvalue int as
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 4', @newvalue)
go
create proc sp_user_counter5 @newvalue int as
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 5', @newvalue)
go
create proc sp_user_counter6 @newvalue int as
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 6', @newvalue)
go
create proc sp_user_counter7 @newvalue int as
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 7', @newvalue)
go
create proc sp_user_counter8 @newvalue int as
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 8', @newvalue)
go
create proc sp_user_counter9 @newvalue int as
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 9', @newvalue)
go
create proc sp_user_counter10 @newvalue int as
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 10', @newvalue)
go


--perfmon
raiserror(15339,-1,-1,'sp_blockcnt')
go
create procedure sp_blockcnt --- 1996/04/08 00:00
as
select blockedusers=count(*) from master.dbo.sysprocesses where blocked <> 0
go



--new query to watch max tempdbspace from perfmon
raiserror(15339,-1,-1,'sp_tempdbspace')
go
create proc sp_tempdbspace --- 1996/04/08 00:00
as

declare @dbsize dec(15,0)
declare @freespace dec(15,0)
declare @spaceused dec(15,0)

select @dbsize = sum(convert(dec(15),size))
               from tempdb.dbo.sysfiles

select  database_name = 'tempdb',
               database_size = (@dbsize / 128),
	       spaceused=(select (sum(convert(dec(15),reserved))/128)
               	from tempdb..sysindexes
		where indid in (0, 1, 255))
go




raiserror(15339,-1,-1,'sp_dboption')
go
/*ANSI_NULLS ON  for creation of sp_dboption*/
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)

declare @status int

/*
**  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 lower(@optvalue) not in ('true', 'false', 'on', 'off') and @optvalue is not null
   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) like '%' + lower(@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,@optname)
      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,@optname)
      print ' '

      select duplicate_options = name
         from master.dbo.spt_values
         where lower(name) like '%' + lower(@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
		if lower(@optvalue) in ('true', 'on')
			begin
				select @alt_optvalue = 'true'
			end
		else
			begin
				select @alt_optvalue = 'false'
			end

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

		if @catvalue = 1
			begin
				select @alt_optname  = 'publish'
			end
		if @catvalue = 2
			begin
				select @alt_optname  = 'subscribe'
			end
		if @catvalue = 4
			begin
				select @alt_optname  = 'merge publish'
			end

		exec @exec_stmt @dbname, @alt_optname, @alt_optvalue
		return (0)
	end


/* call Alter Database to set options */

/* set option value in alter database*/
if lower(@optvalue) in ('true', 'on')
   begin
		select @alt_optvalue = 'ON'
   end

else
	begin
		select @alt_optvalue = 'OFF'
	end

/* if Cross DB Ownership Chaining is the option. Set it and get out */
if lower(@fulloptname) = 'db chaining'
	begin
	
    -- CHECK PERMISSIONS (Note: All sysadmins are dbo) --
    if not (is_srvrolemember('sysadmin') = 1)
    begin
        raiserror(15247,-1,-1)
        return(1)
    end

    -- CANT SET IN ANY OF OF MASTER/MODEL/TEMPDB --
    if lower(@dbname) in ('master', 'model', 'tempdb') or
	   (lower(@dbname) ='msdb' and @alt_optvalue = 'OFF')
    begin
        raiserror(5600,-1,-1)
        return(1)
    end

    -- ERROR IF IN USER TRANSACTION --
    if @@trancount > 0
    begin
        raiserror(15289,-1,-1)
        return (1)
    end

   -- MAKE THE FOLLOWING REMOVE/REMAP/DELETES ATOMIC --
    begin transaction

	select @status = status2 from master.dbo.sysdatabases where name = @dbname
	if @alt_optvalue = 'ON'
		set @status = @status | 1024
	else
		select @status = @status & (~ 1024)
	update master.dbo.sysdatabases set status2 = @status where name = @dbname


    -- REFLECT NEW STATUS IN SYSDATABASES --
    commit transaction

    -- CHECKPOINT DATABASE TO FORCE CHANGES TO IN-MEMORY STRUCTURE --
	select @exec_stmt = 'use ' +  quotename(@dbname, '[')   + ' checkpoint'		 
	exec (@exec_stmt)        

    return (0) -- end of set db chaining/user info in doubt  option
	end


/* set option name in alter database */
if lower(@fulloptname) = 'auto create statistics'
	begin
		select @alt_optname = 'AUTO_CREATE_STATISTICS'
	end

if lower(@fulloptname) = 'auto update statistics'
	begin
		select @alt_optname = 'AUTO_UPDATE_STATISTICS'
	end

if lower(@fulloptname) = 'autoclose'
	begin
		select @alt_optname = 'AUTO_CLOSE'
	end

if lower(@fulloptname) = 'autoshrink'
	begin
		select @alt_optname = 'AUTO_SHRINK'
	end

if lower(@fulloptname) = 'ansi padding'
	begin
		select @alt_optname = 'ANSI_PADDING'
	end

if lower(@fulloptname) = 'arithabort'
	begin
		select @alt_optname = 'ARITHABORT'
	end

if lower(@fulloptname) = 'numeric roundabort'
	begin
		select @alt_optname = 'NUMERIC_ROUNDABORT'
	end

if lower(@fulloptname) = 'ansi null default'
	begin
		select @alt_optname = 'ANSI_NULL_DEFAULT'
	end

if lower(@fulloptname) = 'ansi nulls'
	begin
		select @alt_optname = 'ANSI_NULLS'
	end

if lower(@fulloptname) = 'ansi warnings'
	begin
		select @alt_optname = 'ANSI_WARNINGS'
	end

if lower(@fulloptname) = 'concat null yields null'
	begin
		select @alt_optname = 'CONCAT_NULL_YIELDS_NULL'
	end

if lower(@fulloptname) = 'cursor close on commit'
	begin
		select @alt_optname = 'CURSOR_CLOSE_ON_COMMIT'
	end

if lower(@fulloptname) = 'torn page detection'
	begin
		select @alt_optname = 'TORN_PAGE_DETECTION'
	end

if lower(@fulloptname) = 'quoted identifier'
	begin
		select @alt_optname = 'QUOTED_IDENTIFIER'
	end

if lower(@fulloptname) = 'recursive triggers'
	begin
		select @alt_optname = 'RECURSIVE_TRIGGERS'
	end

if lower(@fulloptname) = 'default to local cursor'
	begin
		select @alt_optname = 'CURSOR_DEFAULT'

		if @alt_optvalue = 'ON'
		   begin
				select @alt_optvalue = 'LOCAL'
		   end

		else
			begin
				select @alt_optvalue = 'GLOBAL'
			end
	end

if lower(@fulloptname) = 'offline'
	begin
		if @alt_optvalue = 'ON'
		   begin
				select @alt_optname = 'OFFLINE'
		   end

		else
			begin
				select @alt_optname = 'ONLINE'
			end
		select @alt_optvalue = ''
	end

if lower(@fulloptname) = 'read only'
	begin
		if @alt_optvalue = 'ON'
		   begin
				select @alt_optname = 'READ_ONLY'
		   end

		else
			begin
				select @alt_optname = 'READ_WRITE'
			end
		select @alt_optvalue = ''
	end

if lower(@fulloptname) = 'dbo use only'
	begin
		if @alt_optvalue = 'ON'
			begin
				if databaseproperty(@dbname, 'IsSingleUser') = 1
					begin
						raiserror(5066,-1,-1);
						return (1)
					end
				select @alt_optname = 'RESTRICTED_USER'
			end

		else
			begin
				if databaseproperty(@dbname, 'IsDBOOnly') = 0
					begin
						return (0)
					end
				select @alt_optname = 'MULTI_USER'
			end

		select @alt_optvalue = ''
	end

if lower(@fulloptname) = 'single user'
	begin
		if @alt_optvalue = 'ON'
		   begin
				if databaseproperty(@dbname, 'ISDBOOnly') = 1
					begin
						raiserror(5066,-1,-1);
						return (1)
					end
				select @alt_optname = 'SINGLE_USER'
		   end

		else
			begin
				if databaseproperty(@dbname, 'IsSingleUser') = 0
					begin
						return (0)
					end
				select @alt_optname = 'MULTI_USER'
			end
		select @alt_optvalue = ''
	end

if lower(@fulloptname) = 'select into/bulkcopy'
	begin
		select @alt_optname = 'RECOVERY'

		if @alt_optvalue = 'ON'
		   begin
				if databaseproperty(@dbname, 'IsTrunclog') = 1
					begin
						select @alt_optvalue = 'RECMODEL_70BACKCOMP'
					end
				else
					begin
						select @alt_optvalue = 'BULK_LOGGED'
					end
		   end

		else
			begin
				if databaseproperty(@dbname, 'IsTrunclog') = 1
					begin
						select @alt_optvalue = 'SIMPLE'
					end
				else
					begin
						select @alt_optvalue = 'FULL'
					end
			end
	end

if lower(@fulloptname) = 'trunc. log on chkpt.'
	begin
		select @alt_optname = 'RECOVERY'

		if @alt_optvalue = 'ON'
			begin
				if databaseproperty(@dbname, 'IsBulkCopy') = 1
					begin
						select @alt_optvalue = 'RECMODEL_70BACKCOMP'
					end
				else
					begin
						select @alt_optvalue = 'SIMPLE'
					end
			end

		else
			begin
				if databaseproperty(@dbname, 'IsBulkCopy') = 1
					begin
						select @alt_optvalue = 'BULK_LOGGED'
					end
				else
					begin
						select @alt_optvalue = 'FULL'
					end
			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
else
	begin
		return (0)
	end

return (0) -- sp_dboption
go
set ansi_nulls off
go
/*ANSI_NULLS OFF  for after creation of sp_dboption*/

checkpoint
go

raiserror(15339,-1,-1,'sp_dbcmptlevel')
go
/*ANSI_NULLS ON  for creation of sp_dbcmptlevel*/
set ansi_nulls on
go
create procedure sp_dbcmptlevel  -- 1997/04/15
@dbname sysname = NULL,		/* database name to change */
@new_cmptlevel tinyint = NULL OUTPUT	/* the new compatibility level to change to */
as

set nocount    on

declare @exec_stmt nvarchar(275)
declare @returncode	int
declare @comptlevel	float(8)
declare @dbid int				/* dbid of the database */
declare @dbsid varbinary(85)    /* id of the owner of the database */
declare @orig_cmptlevel tinyint	/* original compatibility level */
declare @input_cmptlevel tinyint	/* compatibility level passed in by user */
	,@cmptlvl60 tinyint			/* compatibility to SQL Server Version 6.0 */
	,@cmptlvl65 tinyint			/* compatibility to SQL Server Version 6.5 */
	,@cmptlvl70 tinyint			/* compatibility to SQL Server Version 7.0 */
	,@cmptlvl80 tinyint			/* compatibility to SQL Server Version 8.0 */
select  @cmptlvl60 = 60,
		@cmptlvl65 = 65,
		@cmptlvl70 = 70,
		@cmptlvl80 = 80


-- SP MUST BE CALLED AT ADHOC LEVEL --
if (@@nestlevel > 1)
begin
    raiserror(15432,-1,-1,'sp_dbcmptlevel')
    return (1)
end

/*
**  If no @dbname given, just list the valid compatibility level values.
*/

if @dbname is null
begin
   raiserror (15048, -1, -1, @cmptlvl60, @cmptlvl65, @cmptlvl70, @cmptlvl80)
   return (0)
end

/*
**  Verify the database name and get info
*/
select @dbid = dbid, @dbsid = sid ,@orig_cmptlevel = cmptlevel
   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

/*
** Now save the input compatibility level and initialize the return clevel
** to be the current clevel
*/
select @input_cmptlevel = @new_cmptlevel
select @new_cmptlevel = @orig_cmptlevel

/*
** If no clevel was supplied, display and output current level.
*/
if @input_cmptlevel is null
   begin
      raiserror(15054, -1, -1, @orig_cmptlevel)
      return(0)
   end

/*
** We should not allow the user to change the compatibility level of the master database
*/
if @dbid = db_id('master')
	begin
	   raiserror(15417, -1, -1, @dbname)
	   return (1)
	end

/*
** If invalid clevel given, print usage and return error code
** 'usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]'
*/
if @input_cmptlevel not in (@cmptlvl60, @cmptlvl65, @cmptlvl70, @cmptlvl80)
   begin
      raiserror(15416, -1, -1)
      print ' '
      raiserror (15048,
         -1, -1, @cmptlvl60, @cmptlvl65, @cmptlvl70, @cmptlvl80)
      return (1)
   end

/*
** We should not allow the user to change the compatibility level if there exists IV or ICC
*/
if @orig_cmptlevel = @cmptlvl80 and @input_cmptlevel < @cmptlvl80
	begin
		-- CHECK FOR INDEXED VIEWS OR INDEXED COMPUTED-COLUMNS
		if exists (select * from sysobjects where xtype = 'V' and id in (select id from sysindexes)) or
			exists (select * from sysobjects o join sysindexkeys k on o.id=k.id
				where o.xtype = 'U' and ColumnProperty(k.id, col_name(k.id, k.colid), 'IsComputed') = 1)
			begin
				-- Cannot set compat mode because database has a view or computed column that is indexed.
				-- These indexes require an 8.0-compatible database.
				raiserror(15414, -1, -1)
				return (1)
			end
	end

/*
**  Only the SA or the dbo of @dbname can execute the update part
**  of this procedure so check.
*/
if (not (is_srvrolemember('sysadmin') = 1)) and suser_sid() <> @dbsid
	-- ALSO ALLOW db_owner ONLY IF DB REQUESTED IS CURRENT DB
	and (@dbid <> db_id() or is_member('db_owner') <> 1)
   begin
      raiserror(15418,-1,-1)
      return (1)
   end

/*
** We should not allow the user to change the compatibility level for
** replicated or distributed databases
*/
select @comptlevel =	case @input_cmptlevel
							when 60 then 6.0
							when 65 then 6.5
							when 70 then 7.0
							when 80 then 8.0
						end

exec @returncode = sp_MSreplicationcompatlevel @dbname, @comptlevel

if @returncode <> 0
	begin
		raiserror(15306, -1, -1)
		return (1)
	end

/*
**  If we're in a transaction, disallow this since it might make recovery
**  impossible.
*/
set implicit_transactions off
if @@trancount > 0
   begin
      raiserror(15002,-1,-1,'sp_dbcmptlevel')
      return (1)
   end


update master.dbo.sysdatabases set cmptlevel = @input_cmptlevel
      where dbid = @dbid

/*
**  CHECKPOINT the database that was changed.
*/

select @exec_stmt = 'use ' +  quotename(@dbname, '[')   + ' checkpoint'
exec(@exec_stmt )
/*
** If checkpoint unsuccessful, restore the old compatibility level,
** otherwise update output clevel and flush all the SPs of this database
** from the cache
*/
if (@@error <> 0)
	begin
	update		 master.dbo.sysdatabases
		set	 cmptlevel = @orig_cmptlevel
		where	 dbid   = @dbid
	end
else
	begin
	dbcc flushprocindb(@dbid)
	select @new_cmptlevel = @input_cmptlevel
	end

return (0) -- sp_dbcmptlevel
go
set ansi_nulls off
go
/*ANSI_NULLS OFF  for after creation of sp_dbcmptlevel*/

---- Fallback 6.5 sprocs removed for 7.0 until re-coded from scratch to match new tables.
/************************  BEGIN-FALLBACK-STORED-PROCS ***********************/
raiserror(15339,-1,-1,'sp_fallback_MS_sel_fb_svr')
go

create procedure sp_fallback_MS_sel_fb_svr  --- 1997/05/30 02:44
    @pFallbackSvrName    character varying(30)   OUTPUT
as
/********1*********2*********3*********4*********5*********6*********7**

      This sproc is used by ODBC & DBLib when first connecting.
      This sproc will assign a null to the output parm.
      Note: This will need to be changed to return the name
            of the virtual server once WolfPack support is enabled.
*********1*********2*********3*********4*********5*********6*********7*/

Set nocount                   on
Set ansi_nulls                on

SELECT       @pFallbackSvrName   = null

Return 0
go

grant execute on sp_fallback_MS_sel_fb_svr to public
go

checkpoint
go
/*************************  END-FALLBACK-STORED-PROCS ************************/

raiserror(15339,-1,-1,'sp_validname')
go
CREATE PROCEDURE sp_validname
	@name			sysname,
	@raise_error	bit = 1
AS
	-----------------------------------------------------
	--	This SP checks for valid SQL-Server identifiers.
	--	For 7.0+, these are the very-simple checks below.
	--	All non-binary-zero (UNICODE) characters we just
	--	accept as being valid.
	-----------------------------------------------------
	declare @index	int

	Set nocount on

	-- Name cannot be NULL or empty ("")
	-- Blank identifiers (" ") are allowed
	IF (@name is null OR datalength(@name) = 0)
	begin
		if @raise_error = 1
			raiserror (15004,-1,-1)
		return (1)
	end

	-- Name cannot contain a binary-zero character
	select @index = charindex(convert(nchar(1),0x00), @name)
	while (@index <> 0)
	begin
		if unicode(substring(@name, @index, 1)) = 0
		begin
			if @raise_error = 1
				raiserror(15006,-1,-1,@name)
			return (1)
		end
		if @index >= len(@name)
			select @index = 0
		else
		begin
			select @name = substring(@name, @index+1, len(@name)-@index)
			select @index = charindex(convert(nchar(1),0x00), @name)
		end
	end

	-- TH-TH-TH-THAT'S IT!
	return (0) -- sp_validname
go


raiserror(15339,-1,-1,'sp_validlang')
go
create procedure sp_validlang --- 1996/04/08 00:00
@name	sysname
as

/* Check to see if this language is in Syslanguages. */
if exists (select * from master.dbo.syslanguages where name = @name or alias = @name)
	or @name = N'us_english'
	return(0)

raiserror(15033,-1,-1,@name)
return (1) -- sp_validlang
go

checkpoint
go

raiserror(15339,-1,-1,'sp_addmessage')
go
create procedure sp_addmessage --- 1996/04/08 00:00
@msgnum int = null,		-- Number of new message.
@severity smallint = null,	-- Severity of new message.
@msgtext nvarchar(255) = null,	-- Text of new message.
@lang sysname = null,       -- language (name) of new message
@with_log varchar(5) = 'FALSE', -- Whether the message will ALWAYS go to the NT event log
@replace varchar(7) = null	-- Optional parameter to specify that
				-- existing message with same number
				-- should be overwritten.
as
declare @retcode int
declare @langid smallint
declare @msglangid smallint
declare @dlevel smallint

	-- Must be ServerAdmin to manage messages
	if is_srvrolemember('serveradmin') = 0
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

if @msgnum is null or @severity is null or @msgtext is null
	begin
		raiserror(15071,-1,-1)
		return(1)
	end

/*
** User defined messages must be > 50000.
*/
if @msgnum <= 50000
	begin
		raiserror(15040,-1,-1)
		return(1)
	end

/*
** Valid severity range for user defined messges is 1 to 25.
*/
if @severity not between 1 and 25
	begin
		raiserror(15041,-1,-1)
		return(1)
	end

/*
** Verify the language
*/
if @lang is not null
	begin
		exec @retcode = sp_validlang @lang
		if @retcode <>  0
			return(1)
	end
else
	select @lang = @@language

/*
** Get langid from syslanguages; us_english won't exist, so use 0.
*/
select @langid = langid, @msglangid = msglangid
    from master.dbo.syslanguages where name = @lang or alias = @lang

select @langid = isnull(@langid, 0)
select @msglangid = isnull(@msglangid, 1033)

/*
** @with_log must be 'TRUE' or 'FALSE'
*/
if (upper(@with_log) not in ('TRUE', 'FALSE'))
	begin
		raiserror(15271,-1,-1)
		return (1)
	end

/*
** Set the dlevel bit accordingly
*/
if (rtrim(upper(@with_log)) = 'TRUE')
        select @dlevel = 0x80
else
        select @dlevel = 0x0

/*
** If we're adding a non-us_english message, make sure the us_english version already exists.
*/
if (@langid <> 0) and not exists (select * from master.dbo.sysmessages where error=@msgnum and msglangid = 1033)
	begin
		raiserror(15279,-1,-1,@lang)
		return(1)
	end

/*
** If we're adding a non-us_english message, make sure that the severity matches that of the us_english version
*/
if (@langid <> 0 ) and not exists (select * from master.dbo.sysmessages where error=@msgnum and severity=@severity and msglangid = 1033)
	begin
		declare @us_english_severity smallint
		select @us_english_severity = severity from master.dbo.sysmessages where error=@msgnum and msglangid = 1033
		raiserror(15304,-1,-1,@lang,@us_english_severity)
		return(1)
	end

/*
**  Does this message already exist, and if so are we REPLACEing it?
*/
if (select count(*) from master.dbo.sysmessages where error=@msgnum and msglangid=@msglangid) > 0
	if lower(@replace) = 'replace'
		begin
			delete from master.dbo.sysmessages where error = @msgnum and msglangid = @msglangid
			/*
			** If we're REPLACEing a us_english message, make sure any non-us_english messages get updated with the same severity
			*/
			if (@langid = 0)
				begin
					update master.dbo.sysmessages set severity = @severity
					where error = @msgnum and msglangid <> 1033
				end
		end
	else
		begin
			/*
			** The 'replace' option wasn't specified and a
			** msg. with the number already exists.
			*/
			raiserror(15043,-1,-1)
			return(1)
		end

/*
**  Create the message.
*/
insert into master.dbo.sysmessages(error,severity,description,dlevel,msglangid)
	values (@msgnum,@severity,@msgtext,@dlevel,@msglangid)


return (0) -- sp_addmessage
go



raiserror(15339,-1,-1,'sp_addumpdevice')
go
create procedure sp_addumpdevice -- 1995/09/07 12:01
@devtype varchar(20),      /* disk, tape, or diskette */
@logicalname   sysname,      /* logical name of the device */
@physicalname  nvarchar(260),     /* physical name of the device */
@cntrltype  smallint = null,  /* controller type - ignored. */
@devstatus  varchar(40) = 'noskip'  /* device characteristics */
as

declare @status smallint      /* status bits for device */
declare @returncode int

/*
**  An open txn might jeopardize a recovery.
*/
set implicit_transactions off
if @@trancount > 0
   begin
      raiserror(15002,-1,-1,'sp_addumpdevice')
      return (1)
   end

/*
**  You must be SA to execute this sproc.
*/
if (not is_srvrolemember('diskadmin') = 1)
   begin
      raiserror(15247,-1,-1)
      return (1)
   end

select @devtype=lower(@devtype)

/*
**  Check out the @devtype.
*/
if @devtype not in ('disk', 'tape', 'diskette', 'pipe', 'virtual_device')
   begin
      raiserror(15044,-1,-1,@devtype)
      return (1)
   end

/*
**  Check the args are not NULL.
*/
if @logicalname is null
   begin
      raiserror(15045,-1,-1)
      return(1)
   end

/*
**  Check to see that the @logicalname is valid.
*/
exec @returncode = sp_validname @logicalname
if @returncode <> 0
   return(1)

if @physicalname is null
   begin
      raiserror(15046,-1,-1)
      return(1)
   end

/*
**  Make sure physical file name would be unique among devices.
*/
if exists (select * from master.dbo.sysdevices where phyname = @physicalname)
   begin
      raiserror(15061,-1,-1,@physicalname)
      return (1)
   end

/*
**  Prohibit certain special english words from being logical names.
*/
if (@logicalname IN ('disk' ,'diskette' ,'tape' ,'floppy'))
   begin
      raiserror(15285,-1,-1,@logicalname)
      return (1)
   end

/*
**  Make sure that a device with @logicalname doesn't already exist.
*/
if exists (select * from master.dbo.sysdevices where name = @logicalname)
   begin
      raiserror(15026,-1,-1,@logicalname)
      return (1)
   end

/*
**  Always turn on the dump status bit.
*/
select @status = 16

/*
**  If @devtype is a tape then check to see if devstatus is 'skip'.
*/
if @devtype = 'tape'
   begin
      if @devstatus not in ('noskip','skip')
         begin
            raiserror(15047,-1,-1)
            return (1)
         end

      if @devstatus = 'skip' select @status = @status | 8
   end

/*
**  If a disk then the cntrltype = 2
*/
if @devtype = 'disk'
   begin
      insert into master.dbo.sysdevices
         (low, high, size, status, cntrltype, name, phyname)
         values
         (0, 0, 0, @status, 2, @logicalname, @physicalname)
      raiserror(15444,-1,-1)
   end

/*
**  If a diskette then the cntrltype in (3,4)
*/
if @devtype = 'diskette'
   begin
      insert into master.dbo.sysdevices
         (low, high, size, status, cntrltype, name, phyname)
         values
         (0, 0, 0, @status, 3, @logicalname, @physicalname)
      raiserror(15445,-1,-1)
   end

/*
**  Tape device.
*/
if @devtype = 'tape'
   begin
      insert into master.dbo.sysdevices
         (low, high, size, status, cntrltype, name, phyname)
         values
         (0, 0, 0, @status, 5, @logicalname,@physicalname)
      raiserror(15446,-1,-1)
   end

/*
** Pipe.
*/
if @devtype = 'pipe'
   begin
      insert into master.dbo.sysdevices
         (low, high, size, status, cntrltype, name, phyname)
         values
         (0, 0, 0, @status, 6, @logicalname,@physicalname)
      raiserror(15447,-1,-1)
   end

/*
** Virtual device.
*/
if @devtype = 'virtual_device'
   begin
      insert into master.dbo.sysdevices
         (low, high, size, status, cntrltype, name, phyname)
         values
         (0, 0, 0, @status, 7, @logicalname,@physicalname)
      raiserror(15031,-1,-1)
   end


return (0) -- sp_addumpdevice
go


raiserror(15339,-1,-1,'sp_addremotelogin')
go
create procedure sp_addremotelogin --- 1996/04/08 00:00
	@remoteserver	sysname,		/* name of remote server */
	@loginame       sysname = NULL,		/* user's remote name */
	@remotename     sysname = NULL		/* user's local user name */
as
	declare @srvid smallint
	declare @sid varbinary(85)

	-- DISALLOW USER XACT --
	set implicit_transactions off
	if @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sp_addremotelogin')
		return (1)
	end

    -- CHECK PERMISSIONS
    if not (is_srvrolemember('securityadmin') = 1)
    begin
		raiserror(15247,-1,-1)
        return (1)
    end

	-- VALIDATE SERVER NAME --
	select @srvid = srvid from master.dbo.sysservers where srvname = @remoteserver
	if @srvid is null
	begin
		raiserror(15015,-1,-1,@remoteserver)
		return (1)
	end

	-- CHECK FOR INVALID PARAMETER SYNTAX --
	if @loginame is null and @remotename is not null
	begin
		raiserror(15600,-1,-1,'sp_addremotelogin')
		return (1)
	end

	-- VALIDATE @loginame --
	if @loginame is not null
	begin
		select @sid = sid from master.dbo.syslogins where loginname = @loginame
					AND isntname = 0        -- cannot remap to NT login
		if @sid is null
		begin
			raiserror(15067,-1,-1,@loginame)
			return (1)
		end
	end

	-- CHECK FOR DUPLICATE <@remoteserver, @remotename> PAIR --
	--	(Note that this works for @remotename null and not null)
	if exists (select * from master.dbo.sysxlogins where srvid = @srvid
				AND ((@remotename is null AND name is null) OR name = @remotename)
				AND isrpcinmap = 1)
	begin
		if @remotename is null
			raiserror(15066,-1,-1,@remoteserver)
		else
			raiserror(15068,-1,-1,@remotename,@remoteserver)
		return (1)
	end

	-- Check if there is an outgoing mapping to which we can tag on this
	-- incoming mapping
	update master.dbo.sysxlogins set xstatus = xstatus | 32, xdate2 = getdate()
		where srvid = @srvid
		AND ((@remotename is null AND name is null) OR name = @remotename)
		AND ((@sid is null AND sid is null) OR sid = @sid)

	-- If update didnt happen, add an entry. (@srvid, @remotename, @sid)
	if @@rowcount = 0
		insert into master.dbo.sysxlogins
			values(@srvid, @sid, 32, getdate(), getdate(), @remotename, NULL, 0, NULL)

	-- SUCCESS --
	return (0)	-- sp_addremotelogin
go


checkpoint
go


raiserror(15339,-1,-1,'sp_addtype')
go
create procedure sp_addtype --- 1996/04/08 00:00
@typename sysname,		-- name of user-defined type
@phystype sysname,		-- physical system type of user-defined type
@nulltype varchar(8) = null,	-- nullability of new type
@owner sysname = null	-- Owner of type (default is caller)
as

declare @len int		-- length of user type
declare @type tinyint		-- typeid of physical type
declare @tlen smallint		-- length of physical type
declare @typeid smallint	-- user typeid of physical type
declare @nonull bit		-- default is getansinull()
declare @prec int		-- precision of the datatype
declare @scale int		-- scale of the datatype
declare @tprec tinyint		-- precision of the datatype read from systypes
declare @tscale tinyint		-- scale of the datatype read from systypes
declare @tname sysname  -- typename from systypes
declare @tstat tinyint      -- typestat from systypes
declare @orig_phystype	sysname
declare @default_collationid int		----- default collation id
declare @collationid int			---------collation id bo be used
select @orig_phystype = @phystype
select @nulltype = rtrim(lower(@nulltype))
select @typename = rtrim(@typename)
select @phystype = lower(rtrim(@phystype))

-- VALIDATE THE @owner NAME (and verify caller can use this name)
declare @uid smallint
if @owner is null
	select @uid = user_id()
else
	select @uid = uid from sysusers where name = @owner
		and isaliased = 0 AND uid NOT IN (0,3,4) --public/INFO_SCHEMA/etc can't own type
if @uid is null OR
	(is_member('db_owner')=0 AND
	 is_member('db_ddladmin')=0 AND
	 is_member(user_name(@uid))=0)
begin
	raiserror(15600, -1, -1, 'sp_addtype')
	return 1
end

-- TYPES BASED ON BIT CAN BE NULL IN SPHINX,
--	BUT MAKE NOT-NULL THE DFLT FOR BCKWRD-COMPAT
if lower(@phystype) = 'bit' and @nulltype is null
				-- If user didn't specify nullability,
				-- make sure it doesn't get set to nullable
				-- by getansinull()
		select @nulltype = 'not null'

/*
**  Should the user type allow NULLs?
*/
if @nulltype is null
	select @nonull = abs(getansinull()-1)
else if @nulltype = 'null'
	select @nonull = 0
else if @nulltype in ('not null','nonull')
	select @nonull = 1
else
	begin
		raiserror(15085,-1,-1)
		return (1)
	end

/*
**  Check to see that the @typename is valid.
*/
declare @returncode int
execute @returncode = sp_validname @typename
if @returncode <> 0
	return(1)

/*
**  Check to see if the user type already exists or a system type
**  whose name = lower(@typename) (or a synomym) already exists.
*/
if exists (select * from systypes where name = @typename
			or (name = lower(@typename) and xusertype <= 256))
		or lower(@typename)
		in ('character','character varying','char varying',
			'integer','dec','binary varying',
			'national character varying','national character',
			'national char varying','national char',
			'national text',
			'ncharacter varying', 'ncharacter', 'nchar varying',
			'rowversion')
	begin
		raiserror(15029,-1,-1,@typename)
		return (1)
	end

/*
**  Check to see if the user type has been reserved for future use.
*/
if @typename in ('variant')
	begin
		raiserror(15075,-1,-1,@typename)
		return (1)
	end

/*
** Can't supply length with sysname type.
*/
if @phystype like 'sysname%(%'
	begin
		raiserror(15270,-1,-1)
		return(1)
	end

/*
** initialize the length to be NULL first.
*/
select @len = NULL

/*
** If precision and scale were given with the type - extract them
*/
if @phystype like '_%(_%,_%)'
begin
	select @prec = convert(int, substring(@phystype,
		charindex('(',@phystype) + 1,
		charindex(',',@phystype) - 1 - charindex('(',@phystype)))

	select @scale = convert(int, substring(@phystype,
		charindex(',',@phystype) + 1,
		charindex(')',@phystype) - 1 - charindex(',',@phystype)))
	/*
	** Extract the physical type name
	*/
	select @phystype = substring(@phystype, 1,
		   charindex('(', @phystype) - 1)
end
else

/*
**  If a length was given with the user datatype, extract it.
*/
if @phystype like '_%(%)'
begin
	select @len = convert(int, substring(@phystype,
		charindex('(',@phystype) + 1,
		charindex(')',@phystype) - 1 - charindex('(',@phystype)))

	/*
	** Extract the physical type name
	*/
	select @phystype = substring(@phystype, 1,
		   charindex('(', @phystype) - 1)
end

select @phystype = rtrim(@phystype)

select @phystype= (case @phystype
	when 'character' then 'char'
	when 'character varying' then 'varchar'
	when 'char varying' then 'varchar'
	when 'integer' then 'int'
	when 'dec' then 'decimal'
	when 'binary varying' then 'varbinary'
	when 'national character varying' then 'nvarchar'
	when 'national char varying' then 'nvarchar'
	when 'national character' then 'nchar'
	when 'national char' then 'nchar'
	when 'ncharacter varying' then 'nvarchar'
	when 'ncharacter' then 'nchar'
	when 'nchar varying' then 'nvarchar'
	when 'national text' then 'ntext'
	when 'rowversion' then 'timestamp'
	else @phystype
	end)


/*
**  Make sure that the physical type exists and get its characteristics.
**  System physical types have a xusertype < 256 and are owned by the
**  dbo (userid = 1).
*/
select @type = xtype, @tlen = length,
	@tprec = xprec, @tscale = xscale, @tstat = status, @tname = name
from systypes
	where xusertype < 256 and name = @phystype and uid = 1

if @type is null
begin
	raiserror(15036,-1,-1,@orig_phystype)
	return (1)
end

/*
** get the default collation
*/

select @default_collationid  = collationid from systypes where name = @tname

/*
**  Disallow user-defined datatypes on timestamps.  This is done because
**  a timestamp is not a basic type but is really a binary.  There is,
**  therefore, no way to tell if a user-defined datatype is mapped to
**  a binary or a timestamp.  Timestamps can't have rules or defaults.
*/
if @phystype = 'timestamp'
begin
	raiserror(15038,-1,-1)
	return (1)
end

/*
**  Check if the NULL status of the user type is consistent with the NULL status
**  of the physical type.  Here are the possible cases.
**
**		   physical type
**		  NULLs	  NONULLs
**	        -----------------
** user	NULLs	|  ok	|  no
** type NONULLs	|  ok	|  ok
*/
-- NOT NECESSARY: bit and timestamp both already special-cased
/**********
if @nonull = 0 and 1 = 0
	begin
		raiserror(15037,-1,-1,@orig_phystype)
		return (1)
	end
**********/

/* Decide about precision, scale, length
** First check from NUMERIC, DECIMAL
*/
if @tname in ('numeric','decimal')
begin
	/* Type is NUMERIC or DECIMAL */

	if @len > 0
		begin
			/* Length is really the precision
			** Since no scale is specified then scale
			** is minimum(Default, precision). Default = 4
			*/
			select @prec = @len
			select @scale = 0
		end
	else
		if (@prec is NULL)
			begin
				select @prec = 18
				select @scale = 0
			end

	if (@prec > 38) or (@prec < 1)
		begin
			raiserror(15086,-1,-1)
			return (1)
		end

	if (@scale > @prec) or (@scale < 0)
		begin
			/*
			** Illegal scale specified -- must be less than precision
			** and positive.
			*/
			raiserror(15087,-1,-1)
			return (1)
		end

	/* Compute length from precision */
	if (@prec <= 9)
		select @len = 5

	if (@prec > 9) and (@prec <= 19)
		select @len = 9

	if (@prec > 19) and (@prec <= 28)
		select @len = 13

	if (@prec > 28) and (@prec <= 38)
		select @len = 17

end
else

/*
**  Typeids 1 (char), 2 (varchar), 3 (binary) and 4 (varbinary) are the
**  only ones which allow a length to be specified.
*/
if @tname not in ('binary', 'varbinary', 'char', 'varchar', 'nchar', 'nvarchar')
	begin
		/*
		**  We can't use a length and we got one.
		*/
		if @len > 0
			begin
				raiserror(15088,-1,-1)
				return (1)
			end

		/*
		**  Use the fixed length of the physical type.
		*/
		select @len = @tlen
		select @prec = @tprec
		select @scale = @tscale
	end
else
	begin
		/*
		**  We need a length and we didn't get one.
		*/
		if @len is null
			begin
				raiserror(15091,-1,-1)
				return (1)
			end

		-- need to adjust length for unicode (watch out for overflow!)
		if @tname in ('nchar', 'nvarchar') and (@len & 0x80000000) != 0x80000000
			select @len = @len * 2
		if @len <= 0 or @len > 8000
			begin
				raiserror(15092,-1,-1)
				return (1)
			end

		select @prec = @tprec
		select @scale = @tscale
	end

/*
**Get the collation's id
*/

if @tname  in ('char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
	begin
		select @collationid = @default_collationid
	end
else
	begin
		select @collationid=NULL
	end

/*
**  Finally, get the maximum existing user type so we use it + 1 for this
**  new type.
*/
select @typeid = max(xusertype)
	from systypes

/*
**  There are no user defined types yet so use the first number (256).
*/
if @typeid < 256
	select @typeid = 256

-- Set null status bit
if @nonull = 1
    select @tstat = @tstat | 0x01
else
    select @tstat = @tstat & 0xFE

insert systypes (name, xtype, status, xusertype, length, xprec, xscale,
            tdefault, domain, uid, reserved, collationid)
    select @typename, @type, @tstat, @typeid + 1, @len, @prec, @scale,
            0, 0, @uid, 0, @collationid

raiserror(15449,-1,-1)

return (0) -- sp_addtype
go


raiserror(15339,-1,-1,'sp_altermessage')
go
create procedure sp_altermessage --- 1996/04/08 00:00
@message_id       int,
@parameter        sysname,
@parameter_value  varchar(5)
as
begin
  declare @msg            varchar(128)

  select @parameter = upper(@parameter)
  select @parameter_value = upper(@parameter_value)

	-- Must be ServerAdmin to manage messages
	if is_srvrolemember('serveradmin') = 0
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

  /*
  ** Does this message exist?
  */
  if (not exists (select * from master.dbo.sysmessages
                  where error = @message_id))
  begin
          raiserror(15179,-1,-1,@message_id)
          return (1)
  end

  /*
  ** Is Parameter 'WITH_LOG'?
  */
  if (@parameter <> 'WITH_LOG')
  begin
	raiserror(15176,-1,-1)
	return (1)
  end

  /*
  ** Is ParameterValue TRUE or FALSE?
  */
  if (@parameter_value not in ('TRUE', 'FALSE'))
  begin
          raiserror(15277,-1,-1)
          return (1)
  end


  /*
  ** Turn dlevel bit 7 on or off
  */
  if (@parameter_value = 'TRUE')
  begin
          update master.dbo.sysmessages
                  set dlevel = dlevel | 0x80
                          where error = @message_id
  end
  else
  if (@parameter_value = 'FALSE')
  begin
          update master.dbo.sysmessages
                  set dlevel = dlevel & 0x7FFFFF7F
                          where error = @message_id
  end
  return (0)
end
-- sp_altermessage
go

raiserror(15339,-1,-1,'sp_attach_db')
go
create procedure sp_attach_db
@dbname sysname
, @filename1 nvarchar(260)
, @filename2 nvarchar(260) = NULL
, @filename3 nvarchar(260) = NULL
, @filename4 nvarchar(260) = NULL
, @filename5 nvarchar(260) = NULL
, @filename6 nvarchar(260) = NULL
, @filename7 nvarchar(260) = NULL
, @filename8 nvarchar(260) = NULL
, @filename9 nvarchar(260) = NULL
, @filename10 nvarchar(260) = NULL
, @filename11 nvarchar(260) = NULL
, @filename12 nvarchar(260) = NULL
, @filename13 nvarchar(260) = NULL
, @filename14 nvarchar(260) = NULL
, @filename15 nvarchar(260) = NULL
, @filename16 nvarchar(260) = NULL
as
declare @execstring nvarchar (4000)
set nocount on

	IF ((@dbname is null OR datalength(@dbname) = 0) OR
	    (@filename1 is null OR datalength(@filename1) = 0))
	begin
		raiserror (15004,-1,-1)
		return (1)
	end

	-- build initial CREATE DATABASE
	select @execstring = 'CREATE DATABASE '
		+ quotename( @dbname , '[')
		+ ' ON (FILENAME ='
		+ ''''
		+ REPLACE(@filename1,N'''',N'''''')
		+ ''''

	-- add any additional files

	if (@filename2 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename2 ,N'''',N'''''')
			+ ''''
	end

	if (@filename3 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename3 ,N'''',N'''''')
			+ ''''
	end

	if (@filename4 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename4 ,N'''',N'''''')
			+ ''''
	end

	if (@filename5 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename5 ,N'''',N'''''')
			+ ''''
	end

	if (@filename6 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename6 ,N'''',N'''''')
			+ ''''
	end

	if (@filename7 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename7 ,N'''',N'''''')
			+ ''''
	end

	if (@filename8 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename8  ,N'''',N'''''')
			+ ''''
	end

	if (@filename9 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename9 ,N'''',N'''''')
			+ ''''
	end

	if (@filename10 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename10  ,N'''',N'''''')
			+ ''''
	end

	if (@filename11 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename11  ,N'''',N'''''')
			+ ''''
	end

	if (@filename12 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename12  ,N'''',N'''''')
			+ ''''
	end

	if (@filename13 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename13  ,N'''',N'''''')
			+ ''''
	end

	if (@filename14 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename14  ,N'''',N'''''')
			+ ''''
	end

	if (@filename15 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename15 ,N'''',N'''''')
			+ ''''
	end

	if (@filename16 IS NOT NULL)
	begin
		select @execstring = @execstring
			+ ' ), (FILENAME= '''
			+ REPLACE(@filename16  ,N'''',N'''''')
			+ ''''
	end


	-- note it as for attach
	select @execstring = @execstring + ' ) FOR ATTACH'
	exec (@execstring)

if @@error <>  0
	begin
		-- No need to raiserror as the CREATE DATABASE will do so
		return(1)
	end
return (0) -- sp_attach_db
go


raiserror(15339,-1,-1,'sp_attach_single_file_db')
go
create procedure sp_attach_single_file_db
@dbname sysname,
@physname nvarchar(260)
as
declare @execstring nvarchar (400)
set nocount on
	IF ((@dbname is null OR datalength(@dbname) = 0) OR
	   (@physname is null OR datalength(@physname) = 0))
	begin
		raiserror (15004,-1,-1)
		return (1)
	end

	select @execstring = 'CREATE DATABASE '
		+ quotename( @dbname , '[')
		+ ' ON (FILENAME ='
		+ ''''
		+ REPLACE(@physname,N'''',N'''''')
		+ ''''
		+ ' ) FOR ATTACH'
	exec (@execstring)
if @@error <>  0
	begin
		-- No need to raiserror as the CREATE DATABASE will do so
		return(1)
	end
	-- strip out replication from this database
if exists (select * from master.dbo.sysobjects where name=N'sp_removedbreplication')
	begin
		exec sp_removedbreplication @dbname
	end
	return (0) -- sp_attach_single_file_db
go


raiserror(15339,-1,-1,'sp_helplanguage')
go
create procedure sp_helplanguage --- 1996/04/08 00:00
@language sysname = NULL
as

/* Print all languages if the user didn't give the language name. */
if @language is null
begin
	if exists (select * from master.dbo.syslanguages)
		select * from master.dbo.syslanguages
	else
		raiserror(15452,-1,-1)

	/* Find out whether us_english is there or not. */
	if not exists (select * from master.dbo.syslanguages
			where name = 'us_english')
		raiserror(15453,-1,-1)

	return (0)
end

/*  Report information on this language. */
if exists (select * from master.dbo.syslanguages where name = @language)
	begin
		select * from master.dbo.syslanguages where name = @language
		return (0)
	end

if exists (select * from master.dbo.syslanguages where alias = @language)
	begin
		select * from master.dbo.syslanguages where alias = @language
		return (0)
	end

/* Couldn't find this language. */
if @language = 'us_english'
	begin
		raiserror(15453,-1,-1)
		return (0)
	end
else
	begin
		raiserror(15033,-1,-1,@language)
		return (1)
	end
-- sp_helplanguage
go




checkpoint
go

raiserror(15339,-1,-1,'sp_bindefault')
go
create procedure sp_bindefault --- 1996/08/30 20:04
@defname nvarchar(776),			/* name of the default */
@objname nvarchar(517),			/* table or usertype name */
@futureonly varchar(15) = NULL		/* flag to indicate extent of binding */
as

declare @defid int			/* id of the default to bind */
declare @futurevalue varchar(15)	/* the value of @futureonly that causes
					** the binding to be limited */
declare
	@vc1			nvarchar(517)
	,@tab_id		integer
	,@parent_obj	integer
	,@cur_tab_id	integer
	,@colid			smallint
	,@xtype			tinyint
	,@xusertype		smallint
	,@col_status	tinyint
	,@col_default int
	,@identity binary(1)

declare
	@UnqualDef			sysname
	,@QualDef1			sysname
	,@QualDef2			sysname
	,@QualDef3			sysname

	,@UnqualObj			sysname
	,@QualObj1			sysname
	,@QualObj2			sysname
	,@QualObj3			sysname

set cursor_close_on_commit	off
set nocount			on

select @futurevalue = 'futureonly'	/* initialize @futurevalue */
select @identity = 0X80 /* identity columns*/

/*
**  When a default or rule is bound to a user-defined datatype, it is also
**  bound, by default, to any columns of the user datatype that are currently
**  using the existing default or rule as their default or rule.  This default
**  action may be overridden by setting @futureonly = @futurevalue when the
**  procedure is invoked.  In this case existing columns with the user
**  datatype won't have their existing default or rule changed.
*/

-- get name parts --
select @UnqualDef = parsename(@defname, 1),
        @QualDef1 = parsename(@defname, 2),
        @QualDef2 = parsename(@defname, 3),
        @QualDef3 = parsename(@defname, 4)

select @UnqualObj = parsename(@objname, 1),
        @QualObj1 = parsename(@objname, 2),
        @QualObj2 = parsename(@objname, 3),
        @QualObj3 = parsename(@objname, 4)

IF (@UnqualDef is NULL OR @QualDef3 is not null)
   begin
   raiserror(15253,-1,-1,@defname)
   return (1)
   end

IF (@UnqualObj is NULL OR @QualObj3 is not null)
   begin
   raiserror(15253,-1,-1,@objname)
   return (1)
   end


------------------  Verify database.

if ((@QualObj2 is not null and @QualObj1 is null)
	or (@QualDef2 is not null and @QualDef2 <> db_name()))
	begin
		raiserror(15076,-1,-1)
		return (1)
	end

/*
**  Check that the @futureonly argument, if supplied, is correct.
*/
if (@futureonly IS NOT NULL)
begin
	select @futureonly = lower(@futureonly)
	if (@futureonly <> @futurevalue)
		begin
			raiserror(15100,-1,-1)
			return (1)
		end
end

/*
**  Check to see that the default exists and get its id.
*/
select @defid = id, @parent_obj = parent_obj from sysobjects
			where id = object_id(@defname)
				and xtype='D '	-- default object 6

if @defid is NULL
	begin
		raiserror(15016,-1,-1,@UnqualDef)
		return (1)
	end


if @parent_obj > 0
	begin
		raiserror(15050,-1,-1,@defname)
		return(1)
	end

/*
**  If @objname is of the form tab.col then we are binding to a column.
**  Otherwise its a datatype.  In the column case, we need to extract
**  and verify the table and column names and make sure the user owns
**  the table that is getting the default bound. We also need to ensure
**  that we don't overwrite any DRI style defaults.
*/
if @QualObj1 is not null
begin
	if (@QualObj2 is not null)
		select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1)
	else
		select @vc1 = QuoteName(@QualObj1)

	select	@tab_id = o.id,		@colid = c.colid,
			@xtype = c.xtype,	@col_status = c.status,
			@col_default = c.cdefault
	from sysobjects o, syscolumns c
	where c.id = object_id(@vc1,'local')
			and c.name = @UnqualObj
			and o.id = c.id
			and o.xtype='U '

	/*Check that table and column exist*/
 	if @tab_id is null
	begin
		raiserror(15104,-1,-1,@QualObj1,@UnqualObj)
		return (1)
	end

	/*
	**  If the column type is timestamp, disallow the bind.
	**  Defaults can't be bound to timestamp columns.
	*/
	if type_name(@xtype) = 'timestamp'
	begin
		raiserror(15101,-1,-1)
		return (1)
	end

	/*
	**  If the column category is identity, disallow the bind.
	**  Defaults can't be bound to identity columns.
	*/
	if @col_status & @identity = @identity
	begin
		raiserror(15102,-1,-1)
		return (1)
	end

   /*
   **  Check to see if the column was created with or altered
   **  to have a DRI style default value.
   */
	if @col_default > 0
		if exists
         (select	*
            from	sysobjects o
            where	@col_default       = o.id
            and		@tab_id             = o.parent_obj)
		begin
			raiserror(15103,-1,-1)
			return (1)
		end

	BEGIN TRANSACTION txn_bindefault_1

		/*
		**  Since binding a default is a schema change, update schema count
		**  for the object in the sysobjects table.
		*/

		dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

		update syscolumns set cdefault = @defid where id = @tab_id and colid = @colid

	COMMIT TRANSACTION txn_bindefault_1

	raiserror(15511,-1,-1)

end
else
begin
	/*
	**  We're binding to a user type.  In this case, the @objname
	**  is really the name of the user datatype.
	**  When we bind to a user type, any existing columns get changed
	**  to the new binding unless their current binding is not equal
	**  to the current binding for the usertype or if they set the
	**  @futureonly parameter to @futurevalue.
	*/
	declare @olddefault int	/* current default for type */

	/*
	**  Get the current default for the datatype.
	*/

	select @xusertype = xusertype, @olddefault = tdefault
		from systypes where name = @UnqualObj and xusertype > 256
		AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(uid))=1)

	if @xusertype is null
		begin
			raiserror(15105,-1,-1)
			return (1)
		end

	update systypes
		set tdefault = @defid
			from systypes
		where xusertype = @xusertype


	raiserror(15512,-1,-1)

	/*
	**  need the new binding.
	*/
	if isnull(@futureonly, ' ') <> @futurevalue
	begin

		declare ms_crs_t1 cursor local static for
		  select
			distinct
				 c.id
				 ,c.colid
			from	 syscolumns	c JOIN sysobjects o ON c.id = o.id AND o.xtype = N'U '
			where	 c.xusertype	= @xusertype
			and	(c.cdefault	= @olddefault	OR
				 c.cdefault	= 0
				)
			order by c.id
                  for read only

		open ms_crs_t1

		BEGIN TRANSACTION txn_bindefault_3

		fetch next from ms_crs_t1 into
			 @tab_id,
			 @colid

		WHILE @@fetch_status = 0
		begin

			select @vc1 = quotename(user_name(OBJECTPROPERTY(@tab_id,'OwnerId'))) + '.'
						+ quotename(object_name(@tab_id))

			dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

			select @cur_tab_id = @tab_id

			while @cur_tab_id = @tab_id and @@fetch_status = 0
			begin

				update syscolumns
				set cdefault = @defid
				from syscolumns c
				where c.id = @tab_id
				and c.colid = @colid

				fetch next from ms_crs_t1 into
					 @tab_id,
					 @colid
			end

		end --loop 3

		COMMIT TRANSACTION txn_bindefault_3

		deallocate ms_crs_t1

		raiserror(15513,-1,-1)
	end
end

return (0) -- sp_bindefault
go

raiserror(15339,-1,-1,'sp_bindrule')
go
create procedure sp_bindrule --- 1996/08/14 15:02
@rulename nvarchar(776),			/* name of the rule */
@objname nvarchar(517),			/* table or usertype name */
@futureonly varchar(15) = NULL		/* column name */
as

declare @ruleid int			/* id of the rule to bind */
declare @futurevalue varchar(15)	/* the value of @futureonly that causes
					** the binding to be limited */

declare
	@vc1			nvarchar(517)
	,@tab_id		integer
	,@cur_tab_id	integer
	,@colid			smallint

	,@xtype			smallint
	,@xusertype		smallint

declare
	@UnqualRule		sysname
	,@QualRule1		sysname
	,@QualRule2		sysname
	,@QualRule3		sysname

	,@UnqualObj		sysname
	,@QualObj1		sysname
	,@QualObj2		sysname
	,@QualObj3		sysname

set cursor_close_on_commit	off
set nocount on

select @futurevalue = 'futureonly'	/* initialize @futurevalue */

/*
**  When a default or rule is bound to a user-defined datatype, it is also
**  bound, by default, to any columns of the user datatype that are currently
**  using the existing default or rule as their default or rule.  This default
**  action may be overridden by setting @futureonly = @futurevalue when the
**  procedure is invoked.  In this case existing columns with the user
**  datatype won't have their existing default or rule changed.
*/

-- get name parts --
select @UnqualRule = parsename(@rulename, 1),
        @QualRule1 = parsename(@rulename, 2),
        @QualRule2 = parsename(@rulename, 3),
        @QualRule3 = parsename(@rulename, 4)

select @UnqualObj = parsename(@objname, 1),
        @QualObj1 = parsename(@objname, 2),
        @QualObj2 = parsename(@objname, 3),
        @QualObj3 = parsename(@objname, 4)

IF (@UnqualRule is NULL OR @QualRule3 is not null)
   begin
   raiserror(15253,-1,-1,@rulename)
   return (1)
   end

IF (@UnqualObj is NULL OR @QualObj3 is not null)
   begin
   raiserror(15253,-1,-1,@objname)
   return (1)
   end


------------------  Verify database.

if ((@QualObj2 is not null and @QualObj1 is null)
	or (@QualRule2 is not null and @QualRule2 <> db_name()))
	begin
		raiserror(15077,-1,-1)
		return (1)
	end

/*
**  Check that the @futureonly argument, if supplied, is correct.
*/
if (@futureonly IS NOT NULL)
begin
	select @futureonly = lower(@futureonly)
	begin
		if (@futureonly <> @futurevalue)
			begin
				raiserror(15106,-1,-1)
				return (1)
			end
	end
end

/*
**  Check to see that the rule exists and get its id.
*/
select @ruleid = id from sysobjects
			where id = object_id(@rulename)
				and xtype='R ' --rule object 7

if @ruleid is NULL
	begin
		raiserror(15017,-1,-1,@rulename)
		return (1)
	end

/*
**  If @objname is of the form tab.col then we are binding to a column.
**  Otherwise its a datatype.  In the column case, we need to extract
**  and verify the table and column names and make sure the user owns
**  the table that is getting the rule bound.
*/
if @QualObj1 is not null
begin
	if (@QualObj2 is not null)
		select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1)
	else
		select @vc1 = QuoteName(@QualObj1)

	select @tab_id = o.id, @colid = c.colid, @xtype = c.xtype
	from sysobjects o, syscolumns c
	where c.id = object_id(@vc1,'local')
			and c.name = @UnqualObj
			and o.id = c.id
			and o.xtype='U '

	/*Check that table and column exist*/
 	if @tab_id is null
	begin
		raiserror(15104,-1,-1,@QualObj1,@UnqualObj)
		return (1)
	end

	/*
	**  If the column type is image, text, or timestamp, disallow the bind.
	**  Rules can't be bound to image, text, or timestamp columns.
	**  The types are checked in case
	**  there is a user-defined datatype that is an image or text.
	**  User-defined datatypes mapping to timestamp are not allowed
	**  by sp_addtype.
	*/
	if type_name(@xtype) in ('text', 'ntext', 'image', 'timestamp')
		begin
			raiserror(15107,-1,-1)
			return (1)
		end

	BEGIN TRANSACTION txn_bindrule_1

		dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

		update syscolumns set domain = @ruleid
			where id = @tab_id and colid = @colid

	COMMIT TRANSACTION txn_bindrule_1

	raiserror(15514,-1,-1)

end
else
begin
	/*
	**  We're binding to a user type.  In this case, the @objname
	**  is really the name of the user datatype.
	**  When we bind to a user type, any existing columns get changed
	**  to the new binding unless their current binding is not equal
	**  to the current binding for the usertype or if they set the
	**  @futureonly parameter to @futurevalue.
	*/
	declare @oldrule int			/* current rule for type */

	/*
	**  Get the current rule for the datatype.
	*/
	select @oldrule = domain, @xtype = xtype, @xusertype = xusertype
		from systypes where name = @UnqualObj and xusertype > 256
		AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(uid))=1)

	if @oldrule is null
		begin
			raiserror(15105,-1,-1)
			return (1)
		end

	/*
	**  If the column type is image, text, or timestamp, disallow the bind.
	**  Rules can't be bound to image or text columns.
	*/
	if type_name(@xtype) in ('text', 'ntext', 'image', 'timestamp')
		begin
			raiserror(15107,-1,-1)
			return (1)
		end

	update systypes set domain = @ruleid
			from systypes
		where xusertype = @xusertype


	raiserror(15515,-1,-1)

	/*
	**  Now see if there are any columns with the usertype that
	**  need the new binding.
	*/
	if isnull(@futureonly, ' ') <> @futurevalue
	begin
		declare ms_crs_bindrule_1 cursor local static for
		  select
			distinct
				 c.id
				,c.colid
			from	 syscolumns c JOIN sysobjects o ON c.id = o.id AND o.xtype = N'U '
			where	 c.xusertype	= @xusertype
			and	(c.domain	= @oldrule	OR
				 c.domain	= 0
				)
			order by c.id
                  for read only

		open ms_crs_bindrule_1

		BEGIN TRANSACTION txn_bindrule_2

		fetch next from ms_crs_bindrule_1 into
			@tab_id
			,@colid

		WHILE @@fetch_status = 0
		begin

			select @vc1 = quotename(user_name(OBJECTPROPERTY(@tab_id,'OwnerId'))) + '.'
						+ quotename(object_name(@tab_id))

			dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

			select @cur_tab_id = @tab_id

			while @cur_tab_id = @tab_id and @@fetch_status = 0
			begin
				/*
				**  Update syscolumns with new binding.
				*/
				update syscolumns
					set domain = @ruleid
					where id = @tab_id and colid = @colid

				fetch next from ms_crs_bindrule_1 into
					 @tab_id
					,@colid
			end

		end --loop 3

		deallocate ms_crs_bindrule_1

		COMMIT TRANSACTION txn_bindrule_2

		raiserror(15516,-1,-1)
	end
end
return (0) -- sp_bindrule
go

raiserror(15339,-1,-1,'sp_checknames')
go
create procedure sp_checknames --- 1996/04/08 00:00
@mode varchar(20) = NULL		/* mode of operation; e.g. 'silent' */
as

declare @msilent	int		/* set to 1 if 'silent' mode is on */
declare @ret_val	int		/* set to 1 if we find funny char */
declare @codepoint	tinyint		/* set to 1 if we find funny char */
declare @dbname		sysname	/* holds database name */
declare @msg		varchar(90)	/* used for messages to
 */
declare @pat		varchar(132)	/* holds the pattern to search for */

set nocount on

if (@mode like '%help%')
begin
	raiserror(15525,-1,-1)
	raiserror(15526,-1,-1)
	raiserror(15527,-1,-1)
	print ' '
	raiserror(15528,-1,-1)
	raiserror('        sysdatabases.name',0,1)
	raiserror('        sysdevices.name' ,0,1)
	raiserror('        syslogins.name' ,0,1)
	raiserror('        syslogins.dbname',0,1)
	raiserror('        sysremotelogins.remoteusername',0,1)
	raiserror('        sysservers.srvname',0,1)
	raiserror('        sysservers.srvnetname',0,1)
	print ' '
	raiserror(15536,-1,-1)
	raiserror('        syscolumns.name',0,1)
	raiserror('        sysindexes.name',0,1)
	raiserror('        sysobjects.name',0,1)
	raiserror('        syssegments.name',0,1)
	raiserror('        systypes.name',0,1)
	raiserror('        sysusers.name',0,1)
	print ' '
	return (0)
end

/*
**  First, initialize return value, and set up mode variables:
*/
select @ret_val = 0

if (@mode like '%silent%')
	select @msilent = 1
else
	select @msilent = 0


/*
**  Now, initialize the pattern string we will search for:
*/
select @pat = '%[', @codepoint = 127
while (@codepoint < 255)
begin
	select @codepoint = @codepoint + 1
	select @pat = @pat + char(@codepoint)
end
select @pat = @pat + ']%'


/*
**  Get the database name we are in:
*/
select @dbname = db_name()

if (@msilent = 0)
begin
	print ' '
	raiserror(15543,-1,-1,@dbname)
	print ' '
end


/*
**  Look through these only if in the master database:
*/
if (@dbname = 'master')
begin
    if exists (select name from master.dbo.sysdatabases
		    where convert(varchar(132), name) like @pat)
    begin
	if (@msilent = 1)
	    return (1)

	select @ret_val = 1
	print ' '
	print '==============================================================='
	raiserror(15544,-1,-1,'sysdatabases.name')
	print ' '
	raiserror(15545,-1,-1)
	raiserror(15546,-1,-1,'sp_renamedb')
	print ' '
	select dbid,name from master.dbo.sysdatabases
			where convert(varchar(132), name) like @pat
    end

    if exists (select name from master.dbo.sysdevices where convert(varchar(132), name) like @pat)
    begin
	if (@msilent = 1)
	    return (1)

	select @ret_val = 1
	print ' '
	print '==============================================================='
	raiserror(15544,-1,-1,'sysdevices.name')
	print ' '
	raiserror(15564,-1,-1)
	raiserror(15546,-1,-1,'UPDATE')
        print ' '
	select name from master.dbo.sysdevices where convert(varchar(132), name) like @pat
    end

    if exists (select loginname from master.dbo.syslogins where convert(varchar(132), loginname) like @pat)
    begin
	if (@msilent = 1)
	    return (1)

	select @ret_val = 1
	print ' '
	print '==============================================================='
	raiserror(15544,-1,-1,'syslogins.name')
        print ' '
	raiserror(15565,-1,-1)
	raiserror(15546,-1,-1, 'sp_droplogin'' and ''sp_addlogin')
        print ' '
	select sid, loginname from master.dbo.syslogins
			where convert(varchar(132), loginname) like @pat
    end

    if exists (select dbname from master.dbo.syslogins
		    where convert(varchar(132), dbname) like @pat)
    begin
	if (@msilent = 1)
	    return (1)

	select @ret_val = 1
	print ' '
	print '==============================================================='
	raiserror(15544,-1,-1,'syslogins.dbname')
	print ' '
	raiserror(15547,-1,-1)
	raiserror(15548,-1,-1)
	raiserror(15549,-1,-1)
        print ' '
	select sid,loginname,dbname from master.dbo.syslogins
			where convert(varchar(132), dbname) like @pat
    end

    if exists (select remoteusername from master.dbo.sysremotelogins
		    where convert(varchar(132), remoteusername) like @pat)
    begin
	if (@msilent = 1)
	    return (1)

	select @ret_val = 1
	print ' '
	print '==============================================================='
	raiserror(15544,-1,-1,'sysremotelogins.remoteusername')
        print ' '
	raiserror(15566,-1,-1)
	raiserror(15546,-1,-1,'sp_dropremotelogin'' and ''sp_addremotelogin')
        print ' '
	select remoteserverid,remoteusername from master.dbo.sysremotelogins
			where convert(varchar(132), remoteusername) like @pat
    end

    if exists (select srvname from master.dbo.sysservers
		    where convert(varchar(132), srvname) like @pat)
    begin
	if (@msilent = 1)
	    return (1)

	select @ret_val = 1
	print ' '
	print '==============================================================='
	raiserror(15544,-1,-1,'sysservers.srvname')
        print ' '
	raiserror(15567,-1,-1)
	raiserror(15546,-1,-1,'sp_dropserver'' and ''sp_addserver')
        print ' '
	select srvid,srvname from master.dbo.sysservers
			where convert(varchar(132), srvname) like @pat
    end

    if exists (select srvnetname from master.dbo.sysservers
		    where convert(varchar(132), srvnetname) like @pat)
    begin
	if (@msilent = 1)
	    return (1)

	select @ret_val = 1
	print ' '
	print '==============================================================='
	raiserror(15544,-1,-1,'sysservers.srvnetname')
        print ' '
	raiserror(15550,-1,-1)
	raiserror(15551,-1,-1)
	raiserror(15552,-1,-1)
        print ' '
	select srvid,srvname,srvnetname from master.dbo.sysservers
			where convert(varchar(132), srvnetname) like @pat
    end

end


/*
**  For *ALL* databases, we want to look through these:
*/
if exists (select name from dbo.syscolumns
	    where convert(varchar(132), name) like @pat)
begin
    if (@msilent = 1)
	return (1)

    select @ret_val = 1
    print ' '
    print '==============================================================='
    raiserror(15544,-1,-1,'syscolumns.name')
    print ' '
    raiserror(15568,-1,-1)
    raiserror(15546,-1,-1,'sp_rename')
    print ' '
    select objname=o.name,colname=c.name from dbo.syscolumns c, dbo.sysobjects o
		where convert(varchar(132), c.name) like @pat and o.id = c.id
end

if exists (select name from dbo.sysindexes
	    where convert(varchar(132), name) like @pat
	    	  and indid > 0)
begin
    if (@msilent = 1)
	return (1)

    select @ret_val = 1
    print ' '
    print '==============================================================='
    raiserror(15544,-1,-1,'sysindexes.name')
    print ' '
    raiserror(15569,-1,-1)
    raiserror(15546,-1,-1,'UPDATE')
    print ' '
    select id,indid,name from dbo.sysindexes
		where convert(varchar(132), name) like @pat
		and indid > 0
end

if exists (select name from dbo.sysobjects
	    where convert(varchar(132), name) like @pat)
begin
    if (@msilent = 1)
	return (1)

    select @ret_val = 1
    print ' '
    print '==============================================================='
    raiserror(15544,-1,-1,'sysobjects.name')
    print ' '
    raiserror(15570,-1,-1)
    raiserror(15546,-1,-1,'sp_rename')
    print ' '
    select owner = u.name,o.name from dbo.sysobjects o,dbo.sysusers u
		where convert(varchar(132), o.name) like @pat and o.uid=u.uid
end

if exists (select name from dbo.syssegments
	    where convert(varchar(132), name) like @pat)
begin
    if (@msilent = 1)
	return (1)

    select @ret_val = 1
    print ' '
    print '==============================================================='
    raiserror(15544,-1,-1,'syssegments.name')
    print ' '
    raiserror(15571,-1,-1)
    raiserror(15546,-1,-1,'UPDATE')
    print ' '
    select segment,name from dbo.syssegments
		where convert(varchar(132), name) like @pat
end

if exists (select name from dbo.systypes
	    where convert(varchar(132), name) like @pat)
begin
    if (@msilent = 1)
	return (1)

    select @ret_val = 1
    print ' '
    print '==============================================================='
    raiserror(15544,-1,-1,'systypes.name')
    print ' '
    raiserror(15572,-1,-1)
    raiserror(15546,-1,-1,'sp_rename')
    print ' '
    select name from dbo.systypes
		where convert(varchar(132), name) like @pat
end

if exists (select name from dbo.sysusers where convert(varchar(132), name) like @pat)
begin
    if (@msilent = 1)
	return (1)

    select @ret_val = 1
    print ' '
    print '==============================================================='
    raiserror(15544,-1,-1,'sysusers.name')
    print ' '
    raiserror(15573,-1,-1)
    raiserror(15546,-1,-1,'UPDATE')
    print ' '
    select sid,uid,name from dbo.sysusers
		where convert(varchar(132), name) like @pat
end

if (@ret_val = 0  and  @msilent = 0)
begin

    raiserror(15553,-1,-1, @dbname)
    raiserror(15554,-1,-1)
end

return (@ret_val) -- sp_checknames
go

checkpoint
go

raiserror(15339,-1,-1,'sp_configure')
go
CREATE PROCEDURE sp_configure  --- 1996/08/14 09:43

    @configname   varchar(35) = NULL   -- option name to configure
   ,@configvalue  int         = NULL   -- new configuration value
as

set nocount on

declare
    @confignum                int   --Num of the opt to be configured
   ,@configcount              int   --Num of options like @configname
   ,@show_advance             int   --Y/N Read&Write actions on "advanced" opts

declare @fullconfigname		varchar (35)
declare @prevvalue			int
/*
**  Determine @maxnumber based on advance option in syscurconfigs.
*/
if (select value from master.dbo.syscurconfigs where config = 518) = 1
   select @show_advance = 1   -- Display advanced options
else
   select @show_advance = 0   -- Don't display advanced options

/*
**  Make certain that max user info. reflects any addpak upgrades.
*/
if (select high from master.dbo.spt_values where number=103 and type='C')
   <> @@max_connections

   update master.dbo.spt_values
      set high = @@max_connections
      where number = 103
         and type='C'

/*
**  If no option name is given, the procedure will just print out all the
**  options and their values.
*/
if @configname is NULL
   begin
      select name, minimum = low, maximum = high,
         config_value = c.value,
         run_value = master.dbo.syscurconfigs.value
      from master.dbo.spt_values, master.dbo.sysconfigures c, master.dbo.syscurconfigs
      where type = 'C'
         and number = c.config
         and number = master.dbo.syscurconfigs.config

         and
             ((c.status & 2 <> 0 and @show_advance = 1)
                  OR
              (c.status & 2  = 0)
             )
      order by lower(name)

      return (0)
   end

/*
**  Use @configname and try to find the right option.
**  If there isn't just one, print appropriate diagnostics and return.
*/
select @configcount = count(*), @fullconfigname = min (v.name), @prevvalue = min (c.value)
   from master.dbo.spt_values v ,master.dbo.sysconfigures c
   where v.name like '%' + @configname + '%' and v.type = 'C'
      and v.number = c.config
      and
            ((c.status & 2 <> 0 and @show_advance = 1)
                  OR
             (c.status & 2  = 0)
            )

/*
**  If no option, show the user what the options are.
*/
if @configcount = 0
   begin
      raiserror (15123,-1,-1,@configname)

      print ' '
      raiserror (15456,-1,-1)

      /*
      ** Show the user what the options are.
      */
      select name, minimum = low, maximum = high,
         config_value = c.value,
         run_value = master.dbo.syscurconfigs.value
      from master.dbo.spt_values, master.dbo.sysconfigures c, master.dbo.syscurconfigs
      where type = 'C'
         and number = c.config
         and number = master.dbo.syscurconfigs.config

         and
             ((c.status & 2 <> 0 and @show_advance = 1)
                   OR
              (c.status & 2  = 0)
             )

      return (1)
   end

/*
**  If more than one option like @configname, show the duplicates and return.
*/
if @configcount > 1
   begin
      raiserror (15124,-1,-1,@configname)
      print ' '

      select duplicate_options = name
      from master.dbo.spt_values,master.dbo.sysconfigures c
      where name like '%' + @configname + '%'
         and type = 'C'
         and number = c.config
         and
             ((c.status & 2 <> 0 and @show_advance = 1)
                   OR
              (c.status & 2  = 0)
             )

      return (1)
   end
else
   /* There must be exactly one, so get the full name. */
   select @configname = name --,@value_in_sysconfigures = c.value
      from master.dbo.spt_values,master.dbo.sysconfigures c
      where name like '%' + @configname + '%' and type = 'C'
         and number = c.config
         and
             ((c.status & 2 <> 0 and @show_advance = 1)
                   OR
              (c.status & 2  = 0)
             )

/*
** If @configvalue is NULL, just show the current state of the option.
*/
if @configvalue is null
begin

   select       v.name
               ,v.low   as 'minimum'
               ,v.high  as 'maximum'
               ,c.value as 'config_value'
               ,u.value as 'run_value'
         from
                master.dbo.spt_values     v  left outer join
                master.dbo.sysconfigures  c  on v.number = c.config
                                             left outer join
                master.dbo.syscurconfigs  u  on v.number = u.config
         where
                v.type = 'C  '
         and    v.name like '%' + @configname + '%'
         and
               ((c.status & 2 <> 0 and @show_advance = 1)
                     OR
                (c.status & 2  = 0)
               )

   return (0)
end

/*
**  Check.Permissions
*/
if (not is_srvrolemember('serveradmin') = 1)
   begin
      raiserror(15247,-1,-1)
      return (1)
   end

/*
**  Now get the configuration number.
*/
select @confignum = number
   from master.dbo.spt_values,master.dbo.sysconfigures c
   where type = 'C'
      and (@configvalue between low and high or @configvalue = 0)
      and name like '%' + @configname + '%'
      and number = c.config
      and
            ((c.status & 2 <> 0 and @show_advance = 1)
                  OR
             (c.status & 2  = 0)
            )

/*
**  If this is the number of default language, we want to make sure
**  that the new value is a valid language id in Syslanguages.
*/
if @confignum = 124
   begin
   if not exists (select * from master.dbo.syslanguages
         where langid = @configvalue)
      begin
         /* 0 is default language, us_english */
         if @configvalue <> 0
            begin
               raiserror(15127,-1,-1)
               return (1)
            end
      end
   end

/*
**  If this is the number of kernel language, we want to make sure
**  that the new value is a valid language id in Syslanguages.
*/
if @confignum = 132
   begin
   if not exists (select * from master.dbo.syslanguages
         where langid = @configvalue)
      begin
         /* 0 is default language, us_english */
         if @configvalue <> 0
            begin
               raiserror(15028,-1,-1)
               return (1)
            end
      end
   end

/*
**  "user options" should not try to set incompatible options/values.
*/
if @confignum = 1534  --"user options"
   begin

   if (@configvalue & (1024+2048) = (1024+2048)) --ansi_null_default_on/off
      begin
      raiserror(15303,-1,-1,@configvalue)
      return (1)
      end
   end

/*
**  Although the @configname is good, @configvalue wasn't in range.
*/
if @confignum is NULL
   begin
   raiserror(15129,-1,-1,@configvalue,@configname)
   return (1)
   end

--Msg 15002, but in 6.5 allow this inside a txn (not check @@trancount) #12828.

/*
**  Now update sysconfigures.
*/
update master.dbo.sysconfigures set value = @configvalue
   where config = @confignum

/*
** Flush the procedure cache - this is to account for options which become
** effective immediately (ie. dont need a server restart).
*/
dbcc freeproccache

raiserror(15457,-1,-1, @fullconfigname, @prevvalue, @configvalue) with log

return (0) -- sp_configure
go


checkpoint
go



raiserror(15339,-1,-1,'sp_dbremove')
go
create procedure sp_dbremove --- 1996/04/08 00:00
@dbname sysname = null,
@dropdev varchar(10) = null
as
	declare @dbid int
	declare @devname sysname
	declare @physname varchar(255)

	if @dbname is null
		begin
			raiserror(15131,-1,-1)
			return(1)
		end

	if lower(@dropdev) <> 'dropdev' and @dropdev is not null
		begin
			raiserror(15131,-1,-1)
			return(1)
		end

	/* Check to see if database exists. */
	select @dbid = null
	select @dbid = dbid from master.dbo.sysdatabases where name=@dbname
	if @dbid is null
		begin
			raiserror(15010,-1,-1,@dbname)
			return(1)
		end

	/* Make sure no one is in the db. */
	if (select count(*) from master.dbo.sysprocesses where dbid = @dbid) > 0
		begin
			raiserror(15069,-1,-1)
			return (1)
		end

	update master.dbo.sysdatabases set status = 256 where dbid=@dbid
	dbcc dbrepair(@dbname,dropdb,noinit)
	raiserror(15458,-1,-1)

	return(0)
-- sp_dbremove
go




raiserror(15339,-1,-1,'sp_create_removable')
go
create procedure sp_create_removable

@dbname		sysname = null,	/* name of db */
@syslogical	sysname = null,	/* logical name of system device */
@sysphysical	nvarchar (260) = null,	/* physical name of system device */
@syssize	int = null,		/* size of sys device in Meg. */
@loglogical	sysname = null,	/* logical name of log device */
@logphysical	nvarchar (260) = null,	/* physical name of log device */
@logsize	int = null,		/* size of log device in Meg. */
@datalogical1	sysname = null,	/* logical name of data device */
@dataphysical1	nvarchar (260) = null,	/* physical name of data device */
@datasize1	int = null,		/* size of data device in Meg. */
@datalogical2	sysname = null,	/* logical name of data device */
@dataphysical2	nvarchar (260) = null,	/* physical name of data device */
@datasize2	int = null,		/* size of data device in Meg. */
@datalogical3	sysname = null,	/* logical name of data device */
@dataphysical3	nvarchar (260) = null,	/* physical name of data device */
@datasize3	int = null,		/* size of data device in Meg. */
@datalogical4	sysname = null,	/* logical name of data device */
@dataphysical4	nvarchar (260) = null,	/* physical name of data device */
@datasize4	int = null,		/* size of data device in Meg. */
@datalogical5	sysname = null,	/* logical name of data device */
@dataphysical5	nvarchar (260) = null,	/* physical name of data device */
@datasize5	int = null,		/* size of data device in Meg. */
@datalogical6	sysname = null,	/* logical name of data device */
@dataphysical6	nvarchar (260) = null,	/* physical name of data device */
@datasize6	int = null,		/* size of data device in Meg. */
@datalogical7	sysname = null,	/* logical name of data device */
@dataphysical7	nvarchar (260) = null,	/* physical name of data device */
@datasize7	int = null,		/* size of data device in Meg. */
@datalogical8	sysname = null,	/* logical name of data device */
@dataphysical8	nvarchar (260) = null,	/* physical name of data device */
@datasize8	int = null,		/* size of data device in Meg. */
@datalogical9	sysname = null,	/* logical name of data device */
@dataphysical9	nvarchar (260) = null,	/* physical name of data device */
@datasize9	int = null,		/* size of data device in Meg. */
@datalogical10	sysname = null,	/* logical name of data device */
@dataphysical10	nvarchar (260) = null,	/* physical name of data device */
@datasize10	int = null,		/* size of data device in Meg. */
@datalogical11	sysname = null,	/* logical name of data device */
@dataphysical11	nvarchar (260) = null,	/* physical name of data device */
@datasize11	int = null,		/* size of data device in Meg. */
@datalogical12	sysname = null,	/* logical name of data device */
@dataphysical12	nvarchar (260) = null,	/* physical name of data device */
@datasize12	int = null,		/* size of data device in Meg. */
@datalogical13	sysname = null,	/* logical name of data device */
@dataphysical13	nvarchar (260) = null,	/* physical name of data device */
@datasize13	int = null,		/* size of data device in Meg. */
@datalogical14	sysname = null,	/* logical name of data device */
@dataphysical14	nvarchar (260) = null,	/* physical name of data device */
@datasize14	int = null,		/* size of data device in Meg. */
@datalogical15	sysname = null,	/* logical name of data device */
@dataphysical15	nvarchar (260) = null,	/* physical name of data device */
@datasize15	int = null,		/* size of data device in Meg. */
@datalogical16	sysname = null,	/* logical name of data device */
@dataphysical16 nvarchar (260) = null,	/* physical name of data device */
@datasize16	int = null		/* size of data device in Meg. */

as

declare @retcode int,
	@exec_str nvarchar (460),
	@numdevs int

if (not (is_srvrolemember('sysadmin') = 1)) -- Make sure that it's the SA executing this.
	begin
		raiserror(15247,-1,-1)
		return(1)
	end

if @dbname is null
	or @syslogical is null
	or @sysphysical is null
	or @syssize is null
	or @loglogical is null
	or @logphysical is null
	or @logsize is null
	or @datalogical1 is null
	or @dataphysical1 is null
	or @datasize1 is null
		begin
			raiserror (15261,-1,-1)
			return (1)
		end

if exists (select * from master.dbo.sysdatabases where name = @dbname)
	begin
		raiserror(15032,-1,-1,@dbname)
		return(1)
	end

/* Check to verify that valid sizes were supplied for required devices. */
if @syssize < 1 or @logsize < 1 or @datasize1 < 1
	begin
		raiserror (15262,-1,-1)
		return(1)
	end

/* Check to see if a valid database name was supplied. */
exec @retcode = sp_validname @dbname
if @retcode <> 0
	return(1)

/* valid syslogical? */
exec @retcode = sp_validname @syslogical
if @retcode <> 0
	return(1)

/* valid loglogical? */
exec @retcode = sp_validname @loglogical
if @retcode <> 0
	return(1)

/* valid datalogical1? */
exec @retcode = sp_validname @datalogical1
if @retcode <> 0
	return(1)


/* Create the database's system device segment. */
select @exec_str = 'CREATE DATABASE '
		+ quotename( @dbname , '[')
		+ ' ON (NAME ='
		+ quotename( @syslogical , '[')
		+ ',FILENAME ='
		+ ''''
		+ @sysphysical
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@syssize)
		+ ') LOG ON (NAME='
		+ quotename( @loglogical , '[')
		+ ',FILENAME ='
		+ ''''
		+ @logphysical
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@logsize)
		+ ')'
exec(@exec_str)

if @@error <> 0
	begin
		raiserror(15264,-1,-1,'system or log')
		return(1)
	end

-- Add a filegroup for data
select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+ ' ADD FILEGROUP readonlyfilegroup'

exec(@exec_str)

if @@error <> 0
	begin
		raiserror(15264,-1,-1,'user filegroup')
		return(1)
	end

select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical1 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical1
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize1)
		+ ') TO FILEGROUP readonlyfilegroup'
		exec(@exec_str)

if @@error <> 0
	begin
		raiserror(15264,-1,-1,'user data')
		exec ('drop database '+ @dbname)
		return(1)
	end

-- Make this the default filegroup
select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+ ' MODIFY FILEGROUP readonlyfilegroup DEFAULT'

exec(@exec_str)

if @@error <> 0
	begin
		raiserror(15264,-1,-1,'default filegroup')
		return(1)
	end


/* Check out optional data devices. */

if @datalogical2 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical2 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical2
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize2)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical2)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 2
end
else goto no_more_devs

if @datalogical3 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical3 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical3
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize3)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical3)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 3
end
else goto no_more_devs

if @datalogical4 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical4 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical4
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize4)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical4)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 4
end
else goto no_more_devs

if @datalogical5 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical5 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical5
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize5)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical5)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 5
end
else goto no_more_devs

if @datalogical6 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical6 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical6
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize6)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical6)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 6
end
else goto no_more_devs

if @datalogical7 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical7 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical7
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize7)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical7)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 7
end
else goto no_more_devs

if @datalogical8 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical8 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical8
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize8)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical8)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 8
end
else goto no_more_devs
if @datalogical9 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical9 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical9
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize9)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical9)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 9
end
else goto no_more_devs

if @datalogical10 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical10 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical10
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize10)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical10)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 10
end
else goto no_more_devs

if @datalogical11 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical11 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical11
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize11)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical11)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 11
end
else goto no_more_devs

if @datalogical12 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical12 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical12
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize12)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical12)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 12
end
else goto no_more_devs

if @datalogical13 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical13 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical13
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize13)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical13)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 13
end
else goto no_more_devs

if @datalogical14 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical14 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical14
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize14)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical14)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 14
end
else goto no_more_devs

if @datalogical15 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical15 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical15
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize15)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical15)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 15
end
else goto no_more_devs

if @datalogical16 is not null
begin
	select @exec_str = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+' ADD FILE (NAME ='
		+ quotename( @datalogical16 , '[')
		+ ',FILENAME ='
		+ ''''
		+ @dataphysical16
		+ ''''
		+ ',SIZE ='
		+ convert(varchar(28),@datasize16)
		+ ') TO FILEGROUP readonlyfilegroup'
	exec(@exec_str)
	if @retcode <> 0
	begin
		raiserror(15269,-1,-1,@datalogical16)
		exec ('drop database '+ @dbname)
		return(1)
	end
	select @numdevs = 16
end

no_more_devs:


return(0) -- sp_create_removable
go



raiserror(15339,-1,-1,'sp_depends')
go
create procedure sp_depends  --- 1996/08/09 16:51
@objname nvarchar(776)		/* the object we want to check */
as

declare @objid int			/* the id of the object we want */
declare @found_some bit			/* flag for dependencies found */
declare @dbname sysname

/*
**  Make sure the @objname is local to the current database.
*/

select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
	begin
		raiserror(15250,-1,-1)
		return (1)
	end

/*
**  See if @objname exists.
*/
select @objid = object_id(@objname)
if @objid is null
	begin
		select @dbname = db_name()
		raiserror(15009,-1,-1,@objname,@dbname)
		return (1)
	end

/*
**  Initialize @found_some to indicate that we haven't seen any dependencies.
*/
select @found_some = 0

set nocount on

/*
**  Print out the particulars about the local dependencies.
*/
if exists (select *
		from sysdepends
			where id = @objid)
begin
	raiserror(15459,-1,-1)
	select		 'name' = (s6.name+ '.' + o1.name),
			 type = substring(v2.name, 5, 16),
			 updated = substring(u4.name, 1, 7),
			 selected = substring(w5.name, 1, 8),
             'column' = col_name(d3.depid, d3.depnumber)
		from	 sysobjects		o1
			,master.dbo.spt_values	v2
			,sysdepends		d3
			,master.dbo.spt_values	u4
			,master.dbo.spt_values	w5 --11667
			,sysusers		s6
		where	 o1.id = d3.depid
		and	 o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = 'O9T'
		and	 u4.type = 'B' and u4.number = d3.resultobj
		and	 w5.type = 'B' and w5.number = d3.readobj|d3.selall
		and	 d3.id = @objid
		and	 o1.uid = s6.uid
		and deptype < 2

	select @found_some = 1
end

/*
**  Now check for things that depend on the object.
*/
if exists (select *
		from sysdepends
			where depid = @objid)
begin
		raiserror(15460,-1,-1)
	select distinct 'name' = (s.name + '.' + o.name),
		type = substring(v.name, 5, 16)
			from sysobjects o, master.dbo.spt_values v, sysdepends d,
				sysusers s
			where o.id = d.id
				and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
				and d.depid = @objid
				and o.uid = s.uid
				and deptype < 2

	select @found_some = 1
end

/*
**  Did we find anything in sysdepends?
*/
if @found_some = 0
	raiserror(15461,-1,-1)

set nocount off

return (0) -- sp_depends
go

raiserror(15339,-1,-1,'sp_detach_db')
go
create procedure sp_detach_db
@dbname sysname = null,
@skipchecks nvarchar(10) = null
as
declare @dbid int
declare @exec_stmt nvarchar(540)
	if @dbname is null
		begin
			raiserror(15354,-1,-1)
			return(1)
		end

	if lower(@skipchecks) <> N'true'
		and lower(@skipchecks) <> N'false'
		and @skipchecks is not null
		begin
			raiserror(15354,-1,-1)
			return(1)
		end

	select @dbid = null
	select @dbid = dbid from master.dbo.sysdatabases where name=@dbname
	if @dbid is null
		begin
			raiserror(15010,-1,-1,@dbname)
			return(1)
		end

	-- make sure not trying to detach within a transaction
	if @@trancount > 0
		begin
			raiserror(226,-1,-1,'SP_DETACH_DB')
			return(1)
		end

	-- run UPDATE STATISTICS on all tables in the database so they are current
	-- when transferred to READONLY media
	if lower(@skipchecks) <> N'true'
		begin
			print 'Running UPDATE STATISTICS on all tables'
			select @exec_stmt = 'USE ' + quotename( @dbname , '[')
			+ ' exec sp_updatestats ''RESAMPLE'' '
			exec (@exec_stmt)
		end

	select @exec_stmt = 'DBCC DETACHDB ('
			+ quotename( @dbname , '[')
			+ ')'
	exec (@exec_stmt)
	return (0) -- sp_detach_db
go


raiserror(15339,-1,-1,'sp_diskdefault')
go
create procedure sp_diskdefault --- 1996/04/08 00:00
@logicalname	sysname,		/* logical name of the device */
@defstatus	varchar(15)		/* turn on or off */
as

/*
**  If we're in a transaction, disallow this since it might make recovery
**  impossible.
*/
set implicit_transactions off
if @@trancount > 0
	begin
           raiserror(15002,-1,-1,'sp_diskdefault')
	   return (1)
	end

/*
**  Only the SA can run this sproc.
*/
if not is_srvrolemember('diskadmin') = 1
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

/*
**  Make sure that a device with @logicalname exists.
*/
if not exists (select * from master.dbo.sysdevices where name = @logicalname)
	begin
		raiserror(15012,-1,-1,@logicalname)
		return (1)
	end

/*
**  Make sure that it is a database disk and not a dump device.
*/
if exists (select * from master.dbo.sysdevices
		where name = @logicalname
			and status & 16 = 16)
	begin
		raiserror(15035,-1,-1,@logicalname)
		return (1)
	end

/*
**  Make sure that the database disk is NOT a RAM device.
*/
if exists (select *
		from master.dbo.sysdevices
		where name = @logicalname
			and status & 2048 = 2048 )
	begin
		raiserror(15139,-1,-1)
		return (1)
	end

if @defstatus = 'defaulton'
	begin
		update master.dbo.sysdevices set status = status | 1
			where name = @logicalname
		return (0)
	end

if @defstatus = 'defaultoff'
	begin
		update master.dbo.sysdevices set status = status & ~1
			where name = @logicalname
		return (0)
	end

/*
**  @defstatus must be 'defaulton' or 'defaultoff'
*/
raiserror(15140,-1,-1)

return (1) -- sp_diskdefault
go

checkpoint
go


raiserror(15339,-1,-1,'sp_dropdevice')
go
create procedure sp_dropdevice --- 1996/04/08 00:00
@logicalname	sysname,		-- logical name of the device
@delfile	varchar(7) = null	-- optional param. to delete disk file
as


/*
** See if user specified something for @delfile and, if so, validate it.
*/
if @delfile is not null
	begin
		select @delfile = lower(@delfile)

		if @delfile <> 'delfile'
			begin
				raiserror(15216,-1,-1,@delfile)
				return(1)
			end
	end

/*
**  If we're in a transaction, disallow this since it might make recovery
**  impossible.
*/
set implicit_transactions off
if @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sp_dropdevice')
		return (1)
	end

/*
**  Only the system administrator (SA) can run this command.
**  Check to make sure the executor is the sa.
*/
if not is_srvrolemember('diskadmin') = 1
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

/*
**  Check and make sure that the device actually exists.
*/
if not exists (select * from master.dbo.sysdevices where name = @logicalname)
	begin
		raiserror(15012,-1,-1,@logicalname)
		return (1)
	end


/*
** Drop the device.
*/

if @delfile = 'delfile'
	dbcc dbrepair
	('', 'dropdevice',@logicalname, 1)  WITH NO_INFOMSGS
else
	dbcc dbrepair
	('', 'dropdevice',@logicalname, 0)  WITH NO_INFOMSGS


if @@error <> 0
	return (1)

raiserror(15463,-1,-1)

return (0) -- sp_dropdevice
go



raiserror(15339,-1,-1,'sp_dropmessage')
go
create procedure sp_dropmessage --- 1996/04/08 00:00
@msgnum int = null,		-- Number of message to drop.
@lang sysname = null	-- Language of message to drop (or 'ALL')
as
declare @retcode int
declare @msglangid smallint

	-- Must be ServerAdmin to manage messages
	if is_srvrolemember('serveradmin') = 0
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

/*
** If no message id, show usage
*/
if @msgnum is null
	begin
		raiserror(15177,-1,-1)
		return (1)
	end

/*
** Message id must be > 50000
*/
if @msgnum < 50001
	begin
		raiserror(15178,-1,-1)
		return(1)
	end

if (select count(*) from master.dbo.sysmessages where error=@msgnum) = 0
	begin
		raiserror(15179,-1,-1,@msgnum)
		return(1)
	end

/*
** Verify the language
*/
if @lang is null
	select @lang = @@language
if upper(@lang) <> 'ALL'
begin
	begin
		exec @retcode = sp_validlang @lang
		if @retcode <>  0
			return(1)
	end
	/*
	** Get langid from syslanguages; us_english won't exist, so use 0.
	*/
	select @msglangid = isnull((select msglangid from master.dbo.syslanguages where name = @lang or alias = @lang),1033)
end

/*
** The us_english version must be the last one to be dropped
*/
if (@msglangid = 1033) and (select count(*) from master.dbo.sysmessages where error = @msgnum) > 1
begin
	raiserror(15280,-1,-1)
	return(1)
end

/*
**  Drop the message.
*/
if upper(@lang) = 'ALL'
	delete from master.dbo.sysmessages where error = @msgnum
else
	delete from master.dbo.sysmessages where error = @msgnum and msglangid = @msglangid

return (0) -- sp_dropmessage
go


raiserror(15339,-1,-1,'sp_droptype')
go
create procedure sp_droptype --- 1996/04/08 00:00
@typename sysname			/* the user type to drop */
as

declare @typeid smallint		/* the typeid of the usertype to drop */

/*
**  Initialize @typeid so we can tell if we can't find it.
*/
select @typeid = 0

/*
**  Find the user type with @typename.  It must be a user type (xusertype > 256)
**  and it must be owned by the person (or special role) running the procedure.
*/
select @typeid = xusertype
	from systypes
		where name = @typename and xusertype > 256
		AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(uid))=1)

if @typeid = 0
	begin
		raiserror(15105,-1,-1)
		return (1)
	end

/*
**  Check to see if the type is being used.  If it is, it can't be dropped.
*/
if exists (select * from syscolumns where xusertype = @typeid)
	begin
		raiserror(15180,-1,-1)

		/*
		**  Show where it's being used.
		*/
		select object = o.name, type = o.xtype, owner = u.name,
			[column] = c.name, datatype = t.name
		from syscolumns c, systypes t, sysusers u, sysobjects o
		where c.xusertype = @typeid
			and t.xusertype = @typeid
			and o.uid = u.uid
			and c.id = o.id
		order by object, [column]

		return (1)
	end

/*
**  Everything is consistent so drop the type.
*/
delete from systypes where xusertype = @typeid

delete from sysproperties
where type =  1 and id = 0 and
	smallid = @typeid

raiserror(15467,-1,-1)

return (0) -- sp_droptype
go


checkpoint
go

raiserror(15339,-1,-1,'sp_dropremotelogin')
go
create procedure sp_dropremotelogin --- 1996/04/08 00:00
	@remoteserver	sysname,		/* name of remote server */
	@loginame sysname = NULL,		/* user's local user name */
	@remotename sysname = NULL		/* user's remote name */
as
	declare @srvid smallint
	declare @sid varbinary(85)
	declare @count int

	-- DISALLOW USER XACT --
	set implicit_transactions off
	if @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sp_dropremotelogin')
		return (1)
	end

    -- CHECK PERMISSIONS
    if not (is_srvrolemember('securityadmin') = 1)
    begin
		raiserror(15247,-1,-1)
        return (1)
    end

	-- VALIDATE SERVER NAME --
	select @srvid = srvid from master.dbo.sysservers where srvname = @remoteserver
	if @srvid is null
	begin
		raiserror(15015,-1,-1,@remoteserver)
		return (1)
	end

	-- CHECK FOR INVALID PARAMETER SYNTAX --
	if @loginame is null and @remotename is not null
	begin
		raiserror(15600,-1,-1,'sp_dropremotelogin')
		return (1)
	end

	-- VALIDATE @loginame --
	if @loginame is not null
	begin
		select @sid = sid from master.dbo.syslogins where loginname = @loginame
					AND isntname = 0        -- cannot remap to NT login
		if @sid is null
		begin
			raiserror(15067,-1,-1,@loginame)
			return (1)
		end
	end

	-- First remove the isrpcinmap bit from all rows which are also outmap
	update master.dbo.sysxlogins set xstatus = xstatus & ~32	-- isrpcinmap bit
		where srvid = @srvid AND isrpcinmap = 1 AND ishqoutmap = 1
			AND ((@sid IS NULL and sid IS NULL) or sid = @sid)
			AND ((@remotename IS NULL and name IS NULL) or name = @remotename)

	select @count = @@rowcount

	-- Delete the remote login(s) - the remaining rows with isrpcinmap set.
	delete master.dbo.sysxlogins where srvid = @srvid AND isrpcinmap = 1
			AND ((sid IS NULL and @sid IS NULL) or sid = @sid)
			AND ((@remotename IS NULL and name IS NULL) or name = @remotename)

	select @count = @count + @@rowcount

	-- IF NO ROWS UPDATED OR DELETED, ERROR --
	if @count = 0
	begin
		if (@loginame IS NULL)
			raiserror(15021,-1,-1,@remoteserver)
		else if (@remotename IS NULL)
			raiserror(15027,-1,-1,@loginame,@remoteserver)
		else
			raiserror(15185,-1,-1,@remotename,@loginame,@remoteserver)
		return (1)
	end

	-- SUCCESS --
	return (0)	-- sp_dropremotelogin
go


raiserror(15339,-1,-1,'sp_helpconstraint')
go
create proc sp_helpconstraint
    @objname nvarchar(776)			-- the table to check for constraints
   ,@nomsg   varchar(5) = 'msg'		-- 'nomsg' supresses printing of TBName (sp_help)
as
	-- PRELIM
	set nocount on

	declare	@objid			int           -- the object id of the table
			,@cnstdes		nvarchar(4000)-- string to build up index desc
			,@cnstname		sysname       -- name of const. currently under consideration
			,@i				int
			,@cnstid		int
			,@cnsttype		character(2)
			,@keys			nvarchar(2126)	--Length (16*max_identifierLength)+(15*2)+(16*3)
			,@dbname		sysname

	-- Create temp table
	create table #spcnsttab
	(
		cnst_id			int			NOT NULL
		,cnst_type			nvarchar(146) collate database_default NOT NULL   -- 128 for name + text for DEFAULT
		,cnst_name			sysname		collate database_default NOT NULL
		,cnst_nonblank_name	sysname		collate database_default NOT NULL
		,cnst_2type			character(2)	collate database_default NULL
		,cnst_disabled		bit				NULL
		,cnst_notrepl		bit				NULL
		,cnst_delcasc		bit				NULL
		,cnst_updcasc		bit				NULL
		,cnst_keys			nvarchar(2126)	collate database_default NULL	-- see @keys above for length descr
	)

	-- Check to see that the object names are local to the current database.
	select @dbname = parsename(@objname,3)

	if @dbname is not null and @dbname <> db_name()
	begin
		raiserror(15250,-1,-1)
		return (1)
	end

	-- Check to see if the table exists and initialize @objid.
	select @objid = object_id(@objname)
	if @objid is NULL
	begin
		select @dbname=db_name()
		raiserror(15009,-1,-1,@objname,@dbname)
		return (1)
	end

	-- STATIC CURSOR OVER THE TABLE'S CONSTRAINTS
	declare ms_crs_cnst cursor local static for
		select id, xtype, name from sysobjects where parent_obj = @objid
			and xtype in ('C ','PK','UQ','F ', 'D ')	-- ONLY 6.5 sysconstraints objects
		for read only

	-- Now check out each constraint, figure out its type and keys and
	-- save the info in a temporary table that we'll print out at the end.
	open ms_crs_cnst
	fetch ms_crs_cnst into @cnstid ,@cnsttype ,@cnstname
	while @@fetch_status >= 0
	begin

		if @cnsttype in ('PK','UQ')
		begin
			-- get indid and index description
			declare @indid smallint
			select	@indid = indid,
					@cnstdes = case when @cnsttype = 'PK'
								then 'PRIMARY KEY' else 'UNIQUE' end
							 + case when (status & 16)=16
								then ' (clustered)' else ' (non-clustered)' end
			from	sysindexes
			where	name = object_name(@cnstid)
					and id = @objid

			-- Format keys string
			declare @thiskey nvarchar(131) -- 128+3

			select @keys = index_col(@objname, @indid, 1), @i = 2
			if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
				select @keys = @keys  + '(-)'

			select @thiskey = index_col(@objname, @indid, @i)
			if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
				select @thiskey = @thiskey + '(-)'

			while (@thiskey is not null)
			begin
				select @keys = @keys + ', ' + @thiskey, @i = @i + 1
				select @thiskey = index_col(@objname, @indid, @i)
				if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
					select @thiskey = @thiskey + '(-)'
			end

			-- ADD TO TABLE
			insert into #spcnsttab
				(cnst_id,cnst_type,cnst_name, cnst_nonblank_name,cnst_keys, cnst_2type)
			values (@cnstid, @cnstdes, @cnstname, @cnstname, @keys, @cnsttype)
		end

		else
		if @cnsttype = 'F '
		begin
			-- OBTAIN TWO TABLE IDs
			declare @fkeyid int, @rkeyid int
			select @fkeyid = fkeyid, @rkeyid = rkeyid from sysreferences where constid = @cnstid

			-- USE CURSOR OVER FOREIGN KEY COLUMNS TO BUILD COLUMN LISTS
			--	(NOTE: @keys HAS THE FKEY AND @cnstdes HAS THE RKEY COLUMN LIST)
			declare ms_crs_fkey cursor local for select fkey, rkey from sysforeignkeys where constid = @cnstid
			open ms_crs_fkey
			declare @fkeycol smallint, @rkeycol smallint
			fetch ms_crs_fkey into @fkeycol, @rkeycol
			select @keys = col_name(@fkeyid, @fkeycol), @cnstdes = col_name(@rkeyid, @rkeycol)
			fetch ms_crs_fkey into @fkeycol, @rkeycol
			while @@fetch_status >= 0
			begin
				select	@keys = @keys + ', ' + col_name(@fkeyid, @fkeycol),
						@cnstdes = @cnstdes + ', ' + col_name(@rkeyid, @rkeycol)
				fetch ms_crs_fkey into @fkeycol, @rkeycol
			end
			deallocate ms_crs_fkey

			-- ADD ROWS FOR BOTH SIDES OF FOREIGN KEY
			insert into #spcnsttab
				(cnst_id, cnst_type,cnst_name,cnst_nonblank_name,
					cnst_keys, cnst_disabled,
					cnst_notrepl, cnst_delcasc, cnst_updcasc, cnst_2type)
			values
				(@cnstid, 'FOREIGN KEY', @cnstname, @cnstname,
					@keys, ObjectProperty(@cnstid, 'CnstIsDisabled'),
					ObjectProperty(@cnstid, 'CnstIsNotRepl'),
					ObjectProperty(@cnstid, 'CnstIsDeleteCascade'),
					ObjectProperty(@cnstid, 'CnstIsUpdateCascade'),
					@cnsttype)
			insert into #spcnsttab
				(cnst_id,cnst_type,cnst_name,cnst_nonblank_name,
					cnst_keys,
					cnst_2type)
			select
				@cnstid,' ', ' ', @cnstname,
					'REFERENCES ' + db_name()
						+ '.' + rtrim(user_name(ObjectProperty(@rkeyid,'ownerid')))
						+ '.' + object_name(@rkeyid) + ' ('+@cnstdes + ')',
					@cnsttype
		end

		else
		if @cnsttype = 'C '
		begin
			select @i = 1
			select @cnstdes = text from syscomments where id = @cnstid and colid = @i
			while @cnstdes is not null
			begin
				if @i=1
					-- Check constraint
					insert into	#spcnsttab
						(cnst_id, cnst_type ,cnst_name ,cnst_nonblank_name,
							cnst_keys, cnst_disabled, cnst_notrepl, cnst_2type)
					select	@cnstid,
						case when info = 0 then 'CHECK Table Level '
							else 'CHECK on column ' + col_name(@objid ,info) end,
						@cnstname ,@cnstname ,substring(@cnstdes,1,2000),
						ObjectProperty(@cnstid, 'CnstIsDisabled'),
						ObjectProperty(@cnstid, 'CnstIsNotRepl'),
						@cnsttype
					from sysobjects	where id = @cnstid
				else
					insert into #spcnsttab (cnst_id,cnst_type,cnst_name,cnst_nonblank_name,cnst_keys, cnst_2type)
					select	@cnstid,' ' ,' ' ,@cnstname ,substring(@cnstdes,1,2000), @cnsttype

				if len(@cnstdes) > 2000
					insert into #spcnsttab (cnst_id,cnst_type,cnst_name,cnst_nonblank_name,cnst_keys, cnst_2type)
					select	@cnstid,' ' ,' ' ,@cnstname ,substring(@cnstdes,2001,2000), @cnsttype

				select @cnstdes = null
				select @i = @i + 1
				select @cnstdes = text from syscomments where id = @cnstid and colid = @i
			end
		end

		else
		if (@cnsttype = 'D ')
		begin
			select @i = 1
			select @cnstdes = text from syscomments where id = @cnstid and colid = @i
			while @cnstdes is not null
			begin
				if @i=1
					insert into	#spcnsttab
						(cnst_id,cnst_type ,cnst_name ,cnst_nonblank_name ,cnst_keys, cnst_2type)
					select @cnstid, 'DEFAULT on column ' + col_name(@objid ,info),
						@cnstname ,@cnstname ,substring(@cnstdes,1,2000), @cnsttype
					from sysobjects where id = @cnstid
				else
					insert into #spcnsttab (cnst_id,cnst_type,cnst_name,cnst_nonblank_name,cnst_keys, cnst_2type)
					select	@cnstid,' ' ,' ' ,@cnstname ,substring(@cnstdes,1,2000), @cnsttype

				if len(@cnstdes) > 2000
					insert into #spcnsttab (cnst_id,cnst_type,cnst_name,cnst_nonblank_name,cnst_keys, cnst_2type)
					select	@cnstid,' ' ,' ' ,@cnstname ,substring(@cnstdes,2001,2000), @cnsttype

				select @i = @i + 1
				select @cnstdes = null
				select @cnstdes = text from syscomments where id = @cnstid and colid = @i
			end
		end

		fetch ms_crs_cnst into @cnstid ,@cnsttype ,@cnstname
	end		--of major loop
	deallocate ms_crs_cnst

	-- Find any rules or defaults bound by the sp_bind... method.
	insert into #spcnsttab (cnst_id,cnst_type,cnst_name,cnst_nonblank_name,cnst_keys, cnst_2type)
	select c.domain,'RULE on column ' + c.name + ' (bound with sp_bindrule)',
		object_name(c.domain), object_name(c.domain), text, 'R '
	from	syscolumns c, syscomments m
	where	c.id = @objid and m.id = c.domain and ObjectProperty(c.domain, 'IsRule') = 1

	insert into #spcnsttab (cnst_id,cnst_type,cnst_name,cnst_nonblank_name,cnst_keys, cnst_2type)
	select c.cdefault, 'DEFAULT on column ' + c.name + ' (bound with sp_bindefault)',
		object_name(c.cdefault),object_name(c.cdefault), text, 'D '
	from	syscolumns c,syscomments m
	where	c.id = @objid and m.id = c.cdefault and ObjectProperty(c.cdefault, 'IsConstraint') = 0


	-- OUTPUT RESULTS: FIRST THE OBJECT NAME (if not suppressed)
	if @nomsg <> 'nomsg'
	begin
		select 'Object Name' = @objname
		print ' '
	end

	-- Now print out the contents of the temporary index table.
	if exists (select * from #spcnsttab)
		select
			'constraint_type' = cnst_type,
			'constraint_name' = cnst_name,
			'delete_action'=
					CASE
						When cnst_name = ' ' Then ' '
						When cnst_2type in ('F ') Then
							CASE When cnst_delcasc = 1
								Then 'Cascade' else 'No Action' end
						Else '(n/a)'
					END,
			'update_action'=
					CASE
						When cnst_name = ' ' Then ' '
						When cnst_2type in ('F ') Then
							CASE When cnst_updcasc = 1
								Then 'Cascade' else 'No Action' end
						Else '(n/a)'
					END,
			'status_enabled' =
					CASE
						When cnst_name = ' ' Then ' '
						When cnst_2type in ('F ','C ') Then
							CASE When cnst_disabled = 1
								then 'Disabled' else 'Enabled' end
						Else '(n/a)'
					END,
			'status_for_replication' =
					CASE
						When cnst_name = ' ' Then ' '
						When cnst_2type in ('F ','C ') Then
							CASE When cnst_notrepl = 1
								Then 'Not_For_Replication' else 'Is_For_Replication' end
						Else '(n/a)'
					END,
			'constraint_keys' = cnst_keys
		from #spcnsttab order by cnst_nonblank_name ,cnst_name desc
	else
		raiserror(15469,-1,-1) --'No constraints have been defined for this object.'

	print ' '

	if exists (select * from sysreferences where rkeyid = @objid)
		select
			'Table is referenced by foreign key' =
				db_name() + '.'
					+ rtrim(user_name(ObjectProperty(fkeyid,'ownerid')))
					+ '.' + object_name(fkeyid)
					+ ': ' + object_name(constid)
			from sysreferences where rkeyid = @objid order by 1
	else
		raiserror(15470,-1,-1) --'No foreign keys reference this table.'

	return (0) -- sp_helpconstraint
go

checkpoint
go

raiserror(15339,-1,-1,'system_function_schema.fn_dblog')
go
create function system_function_schema.fn_dblog
	(
		@start nvarchar (22) = NULL,
		@end   nvarchar (22) = NULL
	)

returns table
as
	return select * from OpenRowset (DBLog, @start, @end)
go

raiserror(15339,-1,-1,'system_function_schema.fn_helpcollations')
go
create function system_function_schema.fn_helpcollations
	(
	)

returns @tab table(name sysname NOT NULL,
	description	nvarchar(1000)	NOT NULL)
as
begin
	insert @tab
	select * from OpenRowset(collations)

	return
end -- fn_helpcollations
go

raiserror(15339,-1,-1,'system_function_schema.fn_trace_getinfo')
go
create function system_function_schema.fn_trace_getinfo
	(@handle int = 0
	)

returns @tab table(traceid int NOT NULL,
	property int NOT NULL,
	value sql_variant)
as
begin
	insert @tab
	select * from OpenRowset(TraceInfo, @handle)

	return
end -- fn_trace_getinfo
go

raiserror(15339,-1,-1,'system_function_schema.fn_trace_geteventinfo')
go
create function system_function_schema.fn_trace_geteventinfo
	(@handle int
	)

returns @tab table(eventid int NOT NULL,
	columnid int NOT NULL)
as
begin
	insert @tab
	select * from OpenRowset(TraceEventInfo, @handle)

	return
end -- fn_trace_geteventinfo
go

raiserror(15339,-1,-1,'system_function_schema.fn_trace_getfilterinfo')
go
create function system_function_schema.fn_trace_getfilterinfo
	(@handle int = 0
	)

returns @tab table(columnid int NOT NULL,
	logical_operator int NOT NULL,
	comparison_operator int NOT NULL,
	value sql_variant)
as
begin
	insert @tab
	select * from OpenRowset(TraceFilterInfo, @handle)

	return
end -- fn_trace_getfilterinfo
go


raiserror(15339,-1,-1,'system_function_schema.fn_trace_gettable')
go
create function system_function_schema.fn_trace_gettable
	(@filename nvarchar(256), 
	 @numfiles int = -1)
	 
returns table as
return select * from OpenRowset(TrcTable, @filename, @numfiles)
go


raiserror(15339,-1,-1,'system_function_schema.fn_servershareddrives')
go
create function system_function_schema.fn_servershareddrives
	(
	)

returns @tab table(DriveName nchar(1) NOT NULL)
as
begin
	insert @tab
	select * from OpenRowset(servershareddrives)

	return
end -- fn_servershareddrives
go

raiserror(15339,-1,-1,'system_function_schema.fn_virtualfilestats')
go
create function system_function_schema.fn_virtualfilestats
	(
	@DatabaseId Int = -1,
	@FileId Int = -1
	)

returns @tab table(DbId SmallInt NOT NULL, FileId SmallInt NOT NULL, TimeStamp Int NOT NULL,
		NumberReads BigInt NOT NULL, NumberWrites BigInt NOT NULL, BytesRead BigInt NOT NULL,
		BytesWritten BigInt NOT NULL, IoStallMS BigInt NOT NULL )
as
begin
	insert @tab
	select * from OpenRowset(VirtualFileStats, @DatabaseId, @FileId)

	return
end -- fn_virtualfilestats
go

raiserror(15339,-1,-1,'system_function_schema.fn_virtualservernodes')
go
create function system_function_schema.fn_virtualservernodes
	(
	)

returns @tab table(NodeName sysname NOT NULL)
as
begin
	insert @tab
	select * from OpenRowset(virtualservernodes)

	return
end -- fn_virtualservernodes
go

raiserror(15339,-1,-1,'system_function_schema.fn_get_sql')
go
create function system_function_schema.fn_get_sql
	(
	@handle binary(20)
	)
returns @tab table(dbid SmallInt,
	objectid Int,
	number SmallInt,
	encrypted Bit NOT NULL,
	text Text)
as
begin
	insert @tab
	select * from OpenRowset(FnGetSql, @handle)

	return
end -- fn_get_sql
go

raiserror(15339,-1,-1,'sp_helpdb')
go
create procedure sp_helpdb  --- 1995/12/20 15:34 #12755
@dbname sysname = NULL			/* database name to change */
as

declare @exec_stmt nvarchar(625)
declare @showdev	bit
declare @name           sysname
declare @cmd	nvarchar(279)
declare @low nvarchar(11)
declare @dbdesc varchar(600)	/* the total description for the db */
declare @propdesc varchar(40)

set nocount on

/*	Create temp table before any DMP to enure dynamic
**  Since we examine the status bits in sysdatabase and turn them
**  into english, we need a temporary table to build the descriptions.
*/
create table #spdbdesc
(
	dbname sysname,
	owner sysname,
	created nvarchar(11),
	dbid	smallint,
	dbdesc	nvarchar(600)	null,
	dbsize		nvarchar(13) null,
	cmptlevel	tinyint
)


/*
**  If no database name given, get 'em all.
*/
if @dbname is null
	select @showdev = 0
else select @showdev = 1

/*
**  See if the database exists
*/
if not exists (select * from master.dbo.sysdatabases
	where (@dbname is null or name = @dbname))
	begin
		raiserror(15010,-1,-1,@dbname)
	  return (1)
	end

select @low = convert(varchar(11),low) from master.dbo.spt_values
			where type = N'E' and number = 1
/*
**  Initialize #spdbdesc from sysdatabases
*/
insert into #spdbdesc (dbname, owner, created, dbid, cmptlevel)
		select name, suser_sname(sid), convert(nvarchar(11), crdate),
			dbid, cmptlevel from master.dbo.sysdatabases
			where (@dbname is null or name = @dbname)

/*
** Check if you have access to database
** if have access set size and collation
*/
select @low = convert(varchar(11),low) from master.dbo.spt_values
			where type = N'E' and number = 1

declare ms_crs_c1 cursor for
	select db_name (dbid) from #spdbdesc
open ms_crs_c1
fetch ms_crs_c1 into @name
while @@fetch_status >= 0
begin
	if (has_dbaccess(@name) <> 1)
	begin
	  delete #spdbdesc where current of ms_crs_c1
	  raiserror(15622,-1,-1, @name)
	end
	else
		begin
			/* Insert row for each database */
			select @exec_stmt = 'update #spdbdesc
								set dbsize = (select str(convert(dec(15),sum(size))* ' + @low + '/ 1048576,10,2)+ N'' MB'' from '
 								+ quotename(@name, N'[') + N'.dbo.sysfiles) WHERE current of ms_crs_c1'

			execute (@exec_stmt)
		end
	fetch ms_crs_c1 into @name
end
deallocate ms_crs_c1

/*
**  Now for each dbid in #spdbdesc, build the database status
**  description.
*/
declare @curdbid smallint	/* the one we're currently working on */
/*
**  Set @curdbid to the first dbid.
*/
select @curdbid = min(dbid) from #spdbdesc


while @curdbid IS NOT NULL
begin
	set @name = db_name(@curdbid)

	-- These properties always available
	SELECT @dbdesc = 'Status=' + convert(sysname,DatabasePropertyEx(@name,'Status'))
	SELECT @dbdesc = @dbdesc + ', Updateability=' + convert(sysname,DatabasePropertyEx(@name,'Updateability'))
	SELECT @dbdesc = @dbdesc + ', UserAccess=' + convert(sysname,DatabasePropertyEx(@name,'UserAccess'))
	SELECT @dbdesc = @dbdesc + ', Recovery=' + convert(sysname,DatabasePropertyEx(@name,'Recovery'))
	SELECT @dbdesc = @dbdesc + ', Version=' + convert(sysname,DatabasePropertyEx(@name,'Version'))

	-- These props only available if db not shutdown
	IF DatabaseProperty(@name, 'IsShutdown') = 0
	BEGIN
		SELECT @dbdesc = @dbdesc + ', Collation=' + convert(sysname,DatabasePropertyEx(@name,'Collation'))
		SELECT @dbdesc = @dbdesc + ', SQLSortOrder=' + convert(sysname,DatabasePropertyEx(@name,'SQLSortOrder'))
	END

	-- These are the boolean properties
	IF DatabasePropertyEx(@name,'IsAutoClose') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoClose'
	IF DatabasePropertyEx(@name,'IsAutoShrink') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoShrink'
	IF DatabasePropertyEx(@name,'IsInStandby') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsInStandby'
	IF DatabasePropertyEx(@name,'IsTornPageDetectionEnabled') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsTornPageDetectionEnabled'
	IF DatabasePropertyEx(@name,'IsAnsiNullDefault') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullDefault'
	IF DatabasePropertyEx(@name,'IsAnsiNullsEnabled') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullsEnabled'
	IF DatabasePropertyEx(@name,'IsAnsiPaddingEnabled') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiPaddingEnabled'
	IF DatabasePropertyEx(@name,'IsAnsiWarningsEnabled') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiWarningsEnabled'
	IF DatabasePropertyEx(@name,'IsArithmeticAbortEnabled') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsArithmeticAbortEnabled'
	IF DatabasePropertyEx(@name,'IsAutoCreateStatistics') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoCreateStatistics'
	IF DatabasePropertyEx(@name,'IsAutoUpdateStatistics') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoUpdateStatistics'
	IF DatabasePropertyEx(@name,'IsCloseCursorsOnCommitEnabled') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsCloseCursorsOnCommitEnabled'
	IF DatabasePropertyEx(@name,'IsFullTextEnabled') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsFullTextEnabled'
	IF DatabasePropertyEx(@name,'IsLocalCursorsDefault') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsLocalCursorsDefault'
	IF DatabasePropertyEx(@name,'IsNullConcat') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsNullConcat'
	IF DatabasePropertyEx(@name,'IsNumericRoundAbortEnabled') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsNumericRoundAbortEnabled'
	IF DatabasePropertyEx(@name,'IsQuotedIdentifiersEnabled') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsQuotedIdentifiersEnabled'
	IF DatabasePropertyEx(@name,'IsRecursiveTriggersEnabled') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsRecursiveTriggersEnabled'
	IF DatabasePropertyEx(@name,'IsMergePublished') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsMergePublished'
	IF DatabasePropertyEx(@name,'IsPublished') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsPublished'
	IF DatabasePropertyEx(@name,'IsSubscribed') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsSubscribed'
	IF DatabasePropertyEx(@name,'IsSyncWithBackup') = 1
		SELECT @dbdesc = @dbdesc + ', ' + 'IsSyncWithBackup'

	update #spdbdesc set dbdesc = @dbdesc where dbid = @curdbid

	/*
	**  Now get the next, if any dbid.
	*/
	select @curdbid = min(dbid) from #spdbdesc where dbid > @curdbid
end

/*
**  Now #spdbdesc is complete so we can print out the db info
*/
select name = dbname,
	db_size = dbsize,
	owner = owner,
	dbid = dbid,
	created = created,
	status = dbdesc,
    compatibility_level = cmptlevel
from  #spdbdesc
order by dbname

/*
**  If we are looking at one database, show its file allocation.
*/
if @showdev = 1 and has_dbaccess(@dbname) = 1
begin
	print N' '
	select @cmd = N'use ' +  quotename(@dbname) + N' exec sp_helpfile'
	exec (@cmd)

end
return (0) -- sp_helpdb
go

raiserror(15339,-1,-1,'sp_helpdevice')
go
create procedure sp_helpdevice --- 1996/04/08 00:00
@devname sysname = NULL		/* device to check out */
as

/*	Create temp tables before any DML to ensure dynamic
**  Create a temporary table where we can build up a translation of
**  the device status bits.
*/
create table #spdevtab
(
	name sysname		NOT NULL,
	statusdesc nvarchar(255)	null
)
/*
**  See if the device exists.
*/

if not exists (select * from master.dbo.sysdevices where
	(@devname is null or name = @devname))
	begin
		raiserror(15012,-1,-1,@devname)
		return (1)
	end

set nocount on

/*
**  Initialize the temporary table with the names of the devices.
*/
insert into #spdevtab (name)
	select name
		from master.dbo.sysdevices
		where (@devname is null or name = @devname)


/*
**  Now figure out what kind of controller type it is.
**
**  cntrltype =			0	special (data disk)
**				2	disk (dump)
**				3-4	floppy (dump)	Not supported in SQL 7.0
**				5	tape			No size information in SQL 7.0
**				6	pipe
**				7	virtual_device
*/
update #spdevtab
	set statusdesc = N'special'
		from master.dbo.sysdevices d, #spdevtab
			where d.cntrltype = 0
				and #spdevtab.name = d.name
update #spdevtab
	set statusdesc = N'disk'
		from master.dbo.sysdevices d, #spdevtab
			where d.cntrltype = 2
				and #spdevtab.name = d.name

update #spdevtab
	set statusdesc = N'tape'
		from master.dbo.sysdevices d, #spdevtab
			where d.cntrltype = 5
				and #spdevtab.name = d.name

update #spdevtab
	set statusdesc = N'pipe'
		from master.dbo.sysdevices d, #spdevtab
			where d.cntrltype = 6
				and #spdevtab.name = d.name
update #spdevtab
	set statusdesc = N'virtual_device'
		from master.dbo.sysdevices d, #spdevtab
			where d.cntrltype = 7
				and #spdevtab.name = d.name

update #spdevtab
	set statusdesc = N'UNKNOWN DEVICE'
		from master.dbo.sysdevices d, #spdevtab
			where d.cntrltype >= 8
				and #spdevtab.name = d.name


/*
**  Now check out the status bits and turn them into english.
**  Status of 16 is a dump device.
*/
update #spdevtab set statusdesc = statusdesc + N', ' + rtrim(v.name)
	from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	where v.type = 'V' and v.number > -1
		and d.status & v.number = 16
		and #spdevtab.name = d.name

/*
**  Status of 1 is a default disk.
*/
update #spdevtab set statusdesc = statusdesc + N', ' + rtrim(v.name)
	from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	where v.type = 'V' and v.number > -1
		and d.status & v.number = 1
		and #spdevtab.name = d.name

/*
**  Status of 2 is a physical disk.
*/
update #spdevtab
	set statusdesc = substring(statusdesc, 1, 225) + N', ' + rtrim(v.name)
	from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	where v.type = 'V' and v.number > -1
		and d.status & v.number = 2
		and #spdevtab.name = d.name

/*
**  Add in its size in MB.
*/
update #spdevtab
	set statusdesc = statusdesc + N', ' + convert(varchar(10),
		round((convert(float, d.size) * (select low from master.dbo.spt_values
			where type = 'E' and number = 1)
			 / 1048576), 1)) + ' MB'
	from master.dbo.sysdevices d, #spdevtab, master.dbo.spt_values v
	where d.status & 2 = 2
		and #spdevtab.name = d.name
		and v.number = 1
		and v.type = 'E'

/*
**  Status of 4 is a logical disk.
*/
update #spdevtab
	set statusdesc = substring(statusdesc, 1, 225) + N', ' + rtrim(v.name)
	from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	where v.type = 'V' and v.number > -1
		and d.status & v.number = 4
		and #spdevtab.name = d.name

/*
**  Status of 8 is a skip tape header.
*/
update #spdevtab
	set statusdesc = substring(statusdesc, 1, 225) + N', ' + rtrim(v.name)
	from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	where v.type = 'V' and v.number > -1
		and d.status & v.number = 8
		and #spdevtab.name = d.name
/*
**  Status of 4096 is read only.
*/
update #spdevtab
	set statusdesc = substring(statusdesc, 1, 225) + N', ' + rtrim(v.name)
	from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	where v.type = 'V' and v.number > -1
		and d.status & v.number = 4096
		and #spdevtab.name = d.name
/*
**  Status of 8192 is deferred.
*/
update #spdevtab
	set statusdesc = substring(statusdesc, 1, 225) + N', ' + (v.name)
	from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
	where v.type = 'V' and v.number > -1
		and d.status & v.number = 8192
		and #spdevtab.name = d.name

set nocount off

/*
**  The device number is in the high byte of sysdevices.low so
**  spt_values tells us which byte to pick out.
*/
select device_name = d.name, physical_name = d.phyname,
	description = #spdevtab.statusdesc,
	status = d.status&12319, d.cntrltype,
	size
	from master.dbo.sysdevices d, #spdevtab, master.dbo.spt_values v
	where d.name = #spdevtab.name
		and v.type = 'E'
		and v.number = 3

return(0) -- sp_helpdevice
go

raiserror(15339,-1,-1,'sp_helpfile')
go
create procedure sp_helpfile
@filename sysname = NULL			/* file name or all files */
as

set nocount on

if @filename IS NULL
begin
select 	name,  fileid, filename,
	filegroup = filegroup_name(groupid),
	'size' = convert(nvarchar(15), size * 8) + N' KB',
	'maxsize' = (case maxsize when -1 then N'Unlimited'
			else
			convert(nvarchar(15), maxsize * 8) + N' KB' end),
	'growth' = (case status & 0x100000 when 0x100000 then
		convert(nvarchar(3), growth) + N'%'
		else
		convert(nvarchar(15), growth * 8) + N' KB' end),
	'usage' = (case status & 0x40 when 0x40 then 'log only' else 'data only' end)
	from sysfiles
	order by fileid

end
else
begin
	if file_id(@filename) IS NULL
	begin -- no such file
		raiserror (15325, -1, -1, 'file', @filename)
		return (1)
	end
	select 	name,  filename,
	filegroup = filegroup_name(groupid),
	'size' = convert(nvarchar(15), size * 8) + N' KB',
	'maxsize' = (case maxsize when -1 then N'Unlimited'
			else
			convert(nvarchar(15), maxsize * 8) + N' KB' end),
	'growth' = (case status & 0x100000 when 0x100000 then
		convert(nvarchar(3), growth) + N'%'
		else
		convert(nvarchar(15), growth * 8) + N' KB' end),
	'usage' = (case status & 0x40 when 0x40 then 'log only' else 'data only' end)
	from sysfiles
	where fileid = file_id(@filename)
	order by fileid
end

return (0) -- sp_helpfile
go

raiserror(15339,-1,-1,'sp_helpfilegroup')
go
create procedure sp_helpfilegroup
@filegroupname sysname = NULL		/* filegroup name or all filegroups */
as

set nocount on
-- status & 0x40 is a log file and thus not in any filegroup
if @filegroupname IS NULL
begin
	select 	g.groupname,  g.groupid, 'filecount' =
		(select count(*) from sysfiles f
			where f.groupid = g.groupid
				and (f.status & 0x40 <> 0x40))
	from sysfilegroups g
end
else
begin
	if (filegroup_id(@filegroupname) IS NULL)
	begin
		raiserror (15325, -1, -1, 'filegroup', @filegroupname)
		return (1)
	end
	select 	g.groupname,  g.groupid, 'filecount' =
		(select count(*) from sysfiles f
			where f.groupid = g.groupid
				and (f.status & 0x40 <> 0x40))
	from sysfilegroups g
	where g.groupid = filegroup_id(@filegroupname)

	select 	'file_in_group' = name,  fileid, filename,
	'size' = convert(nvarchar(15), size * 8) + N' KB',
	'maxsize' = (case maxsize when -1 then N'Unlimited'
			else
			convert(nvarchar(15), maxsize * 8) + N' KB' end),
	'growth' = (case status & 0x100000 when 0x100000 then
		convert(nvarchar(3), growth) + N'%'
		else
		convert(nvarchar(15), growth * 8) + N' KB' end)
	from sysfiles
	where groupid = filegroup_id(@filegroupname)
	and (status & 0x40 <> 0x40)
	order by fileid
end

return (0) -- sp_helpfilegroup
go


raiserror(15339,-1,-1,'sp_helpgroup')
go
create procedure sp_helpgroup --- 1996/04/08 00:00
@grpname sysname = NULL		/* group name of interest */
as

/*
**  If no group name given, list all the groups.
*/
if @grpname is null
begin
	select Group_name = name, Group_id = uid
		from sysusers
			where (issqlrole = 1)
		order by name

	return (0)
end

/*
**  Check to see if group exists.
*/
if not exists (select * from sysusers where name = @grpname
		and (issqlrole = 1))
	begin
		raiserror(15014,-1,-1,@grpname)
		return (1)
	end

/*
**  List the particulars for the group.
*/
select Group_name = substring(g.name, 1, 25), Group_id = g.uid,
	   Users_in_group = substring(u.name, 1, 25),
	   Userid = u.uid
	from sysusers u, sysusers g, sysmembers m
	where g.name = @grpname
		and g.uid = m.groupuid
		and (g.issqlrole = 1)
		and u.uid = m.memberuid
	order by 1, 2

return (0) -- sp_helpgroup
go





raiserror(15339,-1,-1,'sp_helplog')
go
create procedure sp_helplog --- 1996/04/08 00:00
as
declare @firstpage int,
	@devname nvarchar(257),
	@msg nvarchar(255)

raiserror('sp_helplog is no longer supported.',1,1)

return (0) -- sp_helplog
go



raiserror(15339,-1,-1,'sp_helplogins')
go
CREATE PROCEDURE sp_helplogins  --- 1996/08/12 14:34

    @LoginNamePattern     sysname    = NULL
AS

Set nocount on

Declare
		@exec_stmt nvarchar(3550)

Declare
       @RetCode                        int
      ,@CountSkipPossUsers             int
      ,@Int1                           int

Declare
       @c10DBName                      sysname
      ,@c10DBStatus                    int
      ,@c10DBSID                       varbinary(85)

Declare
       @charMaxLenLoginName            varchar(11)
      ,@charMaxLenDBName               varchar(11)
      ,@charMaxLenUserName             varchar(11)
      ,@charMaxLenLangName             varchar(11)

Declare
       @DBOptLoading                   int   --0x0020      32  "DoNotRecover"
      ,@DBOptPreRecovery               int   --0x0040      64
      ,@DBOptRecovering                int   --0x0080     128

      ,@DBOptSuspect                   int   --0x0100     256  ("not recovered")
      ,@DBOptOffline                   int   --0x0200     512
      ,@DBOptDBOUseOnly                int   --0x0800    2048

      ,@DBOptSingleUser                int   --0x1000    4096


-------------  create work holding tables  ----------------
/*Create temp tables before any DML to ensure dynamic*/

CREATE Table #tb2_PlainLogins
   (
    LoginName                       sysname        collate database_default NOT Null
   ,SID                             varchar(85)    collate database_default NOT Null
   ,DefDBName                       sysname	       collate database_default Null
   ,DefLangName                     sysname        collate database_default Null
   ,AUser                           char(5)        collate database_default Null
   ,ARemote                         char(7)        collate database_default Null
   )

CREATE Table #tb1_UA
   (
    LoginName                       sysname		collate database_default NOT Null
   ,DBName                          sysname		collate database_default NOT Null
   ,UserName                        sysname		collate database_default NOT Null
   ,UserOrAlias                     char(8)		collate database_default NOT Null
   )

----------------  Initial data values  -------------------

Select
       @RetCode                        = 0  -- 0=good ,1=bad
      ,@CountSkipPossUsers             = 0


----------------  Only SA can run this  -------------------


IF (not (is_srvrolemember('securityadmin') = 1))
   begin
   raiserror(15247,-1,-1)
   Select @RetCode = 1
   goto label_86return
   end

----------------------  spt_values  ----------------
-------- 'D'

SELECT       @DBOptLoading       = number
      from   master.dbo.spt_values
      where  type                = 'D'
      and    name                = 'loading'

SELECT       @DBOptPreRecovery   = number
      from   master.dbo.spt_values
      where  type                = 'D'
      and    name                = 'pre recovery'

SELECT       @DBOptRecovering    = number
      from   master.dbo.spt_values
      where  type                = 'D'
      and    name                = 'recovering'

SELECT       @DBOptSuspect       = number
      from   master.dbo.spt_values
      where  type                = 'D'
      and    name                = 'not recovered'

SELECT       @DBOptOffline       = number
      from   master.dbo.spt_values
      where  type                = 'D'
      and    name                = 'offline'

SELECT       @DBOptDBOUseOnly    = number
      from   master.dbo.spt_values
      where  type                = 'D'
      and    name                = 'dbo use only'

SELECT       @DBOptSingleUser    = number
      from   master.dbo.spt_values
      where  type                = 'D'
      and    name                = 'single user'



---------------  Cursor, for DBNames  -------------------


DECLARE ms_crs_10_DB
   Cursor local static For
SELECT
             name ,status ,sid
      from
             master.dbo.sysdatabases



OPEN ms_crs_10_DB


-----------------  LOOP 10:  thru Databases  ------------------


--------------
WHILE (10 = 10)
   begin    --LOOP 10: thru Databases


   FETCH
             Next
      from
             ms_crs_10_DB
      into
             @c10DBName
            ,@c10DBStatus
            ,@c10DBSID


   IF (@@fetch_status <> 0)
      begin
      Deallocate ms_crs_10_DB
      BREAK
      end


--------------------  Okay if we peek inside this DB now?


   IF (     @c10DBStatus & @DBOptDBOUseOnly  > 0
       AND  @c10DBSID                       <> suser_sid()
      )
      begin
      Select @CountSkipPossUsers = @CountSkipPossUsers + 1
      CONTINUE
      end


   IF (@c10DBStatus & @DBOptSingleUser  > 0)
      begin

      SELECT    @Int1 = count(*)
         from   master.dbo.sysprocesses
         where  spid <> @@spid
         and    dbid  = db_id(@c10DBName)

      IF (@Int1 > 0)
         begin
         Select @CountSkipPossUsers = @CountSkipPossUsers + 1
         CONTINUE
         end
      end


   IF (@c10DBStatus &
         (
           @DBOptLoading
         | @DBOptRecovering
         | @DBOptSuspect
         | @DBOptPreRecovery
         )
               > 0
      )
      begin
      Select @CountSkipPossUsers = @CountSkipPossUsers + 1
      CONTINUE
      end


   IF (@c10DBStatus &
         (
           @DBOptOffline
         )
               > 0
      )
      begin
      --Select @CountSkipPossUsers = @CountSkipPossUsers + 1
      CONTINUE
      end

	IF (has_dbaccess(@c10DBName) <> 1)
      begin
	  raiserror(15622,-1,-1, @c10DBName)
      CONTINUE
      end



---------------------  Add the User info to holding table.
	select @exec_stmt = '
   INSERT    #tb1_UA
            (
             DBName
            ,LoginName
            ,UserName
            ,UserOrAlias
            )
      Select
             N' + quotename(@c10DBName, '''') + '
            ,l.loginname
            ,u.name
            ,''User''
         from
             ' + quotename(@c10DBName, '[') + '.dbo.sysusers       u
            ,master.dbo.syslogins                  l
         where
             u.sid  = l.sid AND isaliased=0' +
			case @LoginNamePattern
			when null then ''
			else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
				or l.loginname = N' + quotename(@LoginNamePattern , '''') + ')'
			end
			+
'     UNION
      Select

             N' + quotename(@c10DBName, '''') + '
            ,l.loginname
            ,u2.name
            ,''MemberOf''
         from
             ' + quotename(@c10DBName, '[')+ '.dbo.sysmembers	   m
            ,' + quotename(@c10DBName, '[')+ '.dbo.sysusers       u1
            ,' + quotename(@c10DBName, '[')+ '.dbo.sysusers       u2
            ,master.dbo.syslogins                  l
         where
             u1.sid     = l.sid
         and m.memberuid = u1.uid
		 and m.groupuid  = u2.uid' +
			case @LoginNamePattern
			when null then ''
			else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
				or l.loginname = N' + quotename(@LoginNamePattern , '''') + ')'
			end

   EXECUTE(@exec_stmt)

   end --loop 10



---------------  Populate plain logins work table  ---------------


INSERT       #tb2_PlainLogins
            (
             LoginName
            ,SID
            ,DefDBName
            ,DefLangName
            ,AUser
            ,ARemote
            )
   SELECT
             loginname
            ,convert(varchar(85), sid)
            ,dbname
            ,language
            ,Null
            ,Null
      from
             master.dbo.syslogins
      where
             @LoginNamePattern is null
			 or name = @LoginNamePattern
             or loginname = @LoginNamePattern


-- AUser

UPDATE       #tb2_PlainLogins --(1996/08/12)
      set
             AUser  = 'yes'
      from
             #tb2_PlainLogins
            ,#tb1_UA             tb1
      where
             #tb2_PlainLogins.LoginName     = tb1.LoginName
      and    #tb2_PlainLogins.AUser        IS Null



UPDATE       #tb2_PlainLogins
      set
             AUser    =
                  CASE @CountSkipPossUsers
                     When  0  Then  'NO'
                     Else           '?'
                  END
      where
             AUser   IS Null


-- ARemote

UPDATE       #tb2_PlainLogins
      set
             ARemote   = 'YES'
      from
             #tb2_PlainLogins
            ,master.dbo.sysremotelogins   rl
      where
             #tb2_PlainLogins.SID = rl.sid
      and    #tb2_PlainLogins.ARemote                 IS Null



UPDATE       #tb2_PlainLogins
      set
             ARemote  = 'no'
      where
             ARemote IS Null



------------  Optimize widths for plain Logins report  ----------


SELECT
             @charMaxLenLoginName      =
                  convert ( varchar
                           ,isnull ( max(datalength(LoginName)) ,9)
                          )
            ,@charMaxLenDBName         =
                  convert ( varchar
                           , isnull (max(isnull (datalength(DefDBName) ,9)) ,9)
                          )
            ,@charMaxLenLangName   =
                  convert ( varchar
                           , isnull (max(isnull (datalength(DefLangName) ,11)) ,11)
                          )
      from
             #tb2_PlainLogins



----------------  Print out plain Logins report  -------------

/*** Message Handlers get confused.
Raiserror('...Logins...' ,0,1)
***/

EXECUTE(
'
Set nocount off


SELECT
          ''LoginName''       = substring (LoginName     ,1 ,'
                                       + @charMaxLenLoginName   + ')

         ,''SID''             = convert(varbinary(85), SID)

         ,''DefDBName''       = substring (DefDBName     ,1 ,'
                                       + @charMaxLenDBName      + ')

         ,''DefLangName''     = substring (DefLangName   ,1 ,'
                                       + @charMaxLenLangName    + ')

         ,AUser
         ,ARemote
   from
          #tb2_PlainLogins
   order by
          LoginName


Set nocount on
'
)



------------  Optimize UA report column display widths  -----------


SELECT
             @charMaxLenLoginName   =
                  convert ( varchar
                           ,isnull ( max(datalength(LoginName)) ,9)
                          )
            ,@charMaxLenDBName      =
                  convert ( varchar
                           ,isnull ( max(datalength(DBName)) ,6)
                          )
            ,@charMaxLenUserName    =
                  convert ( varchar
                           ,isnull ( max(datalength(UserName)) ,8)
                          )
      from
             #tb1_UA



------------  Print out the UserOrAlias report  ------------

/***
Raiserror('...Logins-to-Users...' ,0,1)
***/

EXECUTE(
'
Set nocount off


SELECT
          ''LoginName''    = substring (LoginName  ,1 ,'
                                       + @charMaxLenLoginName  + ')

         ,''DBName''       = substring (DBName     ,1 ,'
                                       + @charMaxLenDBName     + ')

         ,''UserName''     = substring (UserName   ,1 ,'
                                       + @charMaxLenUserName   + ')

         ,UserOrAlias
   from
          #tb1_UA
   order by
          1 ,2 ,3


Set nocount on
'
)


-----------------------  Finalization  --------------------
label_86return:

IF (object_id('#tb2_PlainLogins') IS NOT Null)
            DROP Table #tb2_PlainLogins

IF (object_id('#tb1_UA') IS NOT Null)
            DROP Table #tb1_UA

Return @RetCode -- sp_helplogins
go


raiserror(15339,-1,-1,'sp_helpindex')
go
create proc sp_helpindex
	@objname nvarchar(776)		-- the table to check for indexes
as
	-- PRELIM
	set nocount on

	declare @objid int,			-- the object id of the table
			@indid smallint,	-- the index id of an index
			@groupid smallint,  -- the filegroup id of an index
			@indname sysname,
			@groupname sysname,
			@status int,
			@keys nvarchar(2126),	--Length (16*max_identifierLength)+(15*2)+(16*3)
			@dbname	sysname

	-- Check to see that the object names are local to the current database.
	select @dbname = parsename(@objname,3)

	if @dbname is not null and @dbname <> db_name()
	begin
			raiserror(15250,-1,-1)
			return (1)
	end

	-- Check to see the the table exists and initialize @objid.
	select @objid = object_id(@objname)
	if @objid is NULL
	begin
		select @dbname=db_name()
		raiserror(15009,-1,-1,@objname,@dbname)
		return (1)
	end

	-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
	declare ms_crs_ind cursor local static for
		select indid, groupid, name, status from sysindexes
			where id = @objid and indid > 0 and indid < 255 and (status & 64)=0 order by indid
	open ms_crs_ind
	fetch ms_crs_ind into @indid, @groupid, @indname, @status

	-- IF NO INDEX, QUIT
	if @@fetch_status < 0
	begin
		deallocate ms_crs_ind
		raiserror(15472,-1,-1) --'Object does not have any indexes.'
		return (0)
	end

	-- create temp table
	create table #spindtab
	(
		index_name			sysname	collate database_default NOT NULL,
		stats				int,
		groupname			sysname collate database_default NULL,
		index_keys			nvarchar(2126)	collate database_default NOT NULL -- see @keys above for length descr
	)

	-- Now check out each index, figure out its type and keys and
	--	save the info in a temporary table that we'll print out at the end.
	while @@fetch_status >= 0
	begin
		-- First we'll figure out what the keys are.
		declare @i int, @thiskey nvarchar(131) -- 128+3

		select @keys = index_col(@objname, @indid, 1), @i = 2
		if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
			select @keys = @keys  + '(-)'

		select @thiskey = index_col(@objname, @indid, @i)
		if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
			select @thiskey = @thiskey + '(-)'

		while (@thiskey is not null )
		begin
			select @keys = @keys + ', ' + @thiskey, @i = @i + 1
			select @thiskey = index_col(@objname, @indid, @i)
			if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
				select @thiskey = @thiskey + '(-)'
		end

		select @groupname = null
		select @groupname = groupname from sysfilegroups where groupid = @groupid

		-- INSERT ROW FOR INDEX
		insert into #spindtab values (@indname, @status, @groupname, @keys)

		-- Next index
		fetch ms_crs_ind into @indid, @groupid, @indname, @status
	end
	deallocate ms_crs_ind

	-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
	declare @empty varchar(1) select @empty = ''
	declare @des1			varchar(35),	-- 35 matches spt_values
			@des2			varchar(35),
			@des4			varchar(35),
			@des32			varchar(35),
			@des64			varchar(35),
			@des2048		varchar(35),
			@des4096		varchar(35),
			@des8388608		varchar(35),
			@des16777216	varchar(35)
	select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
	select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
	select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
	select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
	select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
	select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
	select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
	select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
	select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216

	-- DISPLAY THE RESULTS
	select
		'index_name' = index_name,
		'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
				case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
				+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
				+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
				+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
				+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
				+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
				+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
				+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
				+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
				+ ' located on ' + groupname),
		'index_keys' = index_keys
	from #spindtab
	order by index_name


	return (0) -- sp_helpindex
go

raiserror(15339,-1,-1,'sp_helpstats')
go

create proc sp_helpstats
	@objname nvarchar(520),		-- the table to check for statistics (to accomodate for 2 part names)
	@results nvarchar(5) = 'STATS'	-- 'ALL' returns indexes & stats, 'STATS' returns just stats
as
	-- PRELIM
	set nocount on
	declare 	@objid int,			-- the object id of the table
			@indid smallint,	-- the index id of an index
			@indname sysname,
			@keys nvarchar(2078),-- string build index key list, length = (16*max_id_length)+(15*2)
			@dbname	sysname,
			@i int,
			@thiskey sysname,
			@curs	cursor
	-- Check to see the the table exists and initialize @objid.
	select @objid = object_id(@objname, 'local')
	if @objid is NULL
		begin
		select @dbname=db_name()
		raiserror(15009,-1,-1,@objname,@dbname)
		return (1)
		end
	If UPPER(@results) <> 'STATS' and UPPER(@results)<> 'ALL'
	BEGIN
    		raiserror(N'Invalid option: %s', 1, 1, @results)
		return (1)
	END

	If UPPER(@results) = 'STATS'
	BEGIN
		set @curs = cursor local fast_forward READ_ONLY for
			select indid, name from sysindexes
			where id = @objid and indid > 0 and indid < 255
			  and (status & (64 | 8388608)) > 0 order by indid -- User created & auto-created stats
	END
	ELSE
	BEGIN
		set @curs = cursor local fast_forward READ_ONLY for
			select indid, name from sysindexes
			where id = @objid and indid > 0 and indid < 255
			  order by indid -- Indexes, User created & auto-created stats
	END

	open @curs
	fetch @curs into @indid, @indname

	-- IF NO STATISTICS, QUIT
	if @@fetch_status < 0
	begin
		deallocate @curs
		If UPPER(@results) = 'STATS'
		BEGIN
			raiserror(15574,-1,-1) --'Object does not have any statistics.'
		END
		ELSE
		BEGIN
			raiserror(15575,-1,-1) --'Object does not have any indexes or statistics.'
		END
	return (0)
	end
	-- create temp table
	create table #spstattab
	(
		stats_name			sysname	collate database_default NOT NULL,
		stats_keys			nvarchar(2078)	collate database_default NOT NULL
	)

	-- Now check out each statistics set, figure out its keys and
	--	save the info in a temporary table that we'll print out at the end.
	while @@fetch_status >= 0
	begin
		-- First we'll figure out what the keys are.

		select @keys = index_col(@objname, @indid, 1),
				@i = 2, @thiskey = index_col(@objname, @indid, 2)
		while (@thiskey is not null )
		begin
			select @keys = @keys + ', ' + @thiskey, @i = @i + 1
			select @thiskey = index_col(@objname, @indid, @i)
		end

		-- INSERT ROW FOR INDEX
		insert into #spstattab values (@indname, @keys)

		-- Next index
		fetch @curs into @indid, @indname
	end
	deallocate @curs

	-- DISPLAY THE RESULTS
	select
		'statistics_name' = stats_name,
		'statistics_keys' = stats_keys
	from #spstattab
	order by stats_name

return (0) -- sp_helpstats

raiserror(15339,-1,-1,'sp_objectfilegroup')
go
create procedure sp_objectfilegroup --- 1996/08/30 17:44
@objid	int
as
	/*
	** Print out the object's data filegroup if applicable.
	*/
	if exists (select * from sysobjects
			where id = @objid
			and type in ('S ','U '))
		begin
			select Data_located_on_filegroup = s.groupname
			from sysfilegroups s, sysindexes i
			where i.id = @objid
				and i.indid < 2
				and i.groupid = s.groupid
		end

	/*
	**  It's not a table so segment is not applicable.
	*/
	else
		select Data_located_on_filegroup = 'not applicable'

return (0) -- sp_objectfilegroup
go

raiserror(15339,-1,-1,'sp_help')
go
create proc sp_help
	@objname nvarchar(776) = NULL		-- object name we're after
as
	-- PRELIMINARY
	set nocount on
	declare	@dbname	sysname

	-- OBTAIN DISPLAY STRINGS FROM spt_values UP FRONT --
	declare @no varchar(35), @yes varchar(35), @none varchar(35)
	select @no = name from master.dbo.spt_values where type = 'B' and number = 0
	select @yes = name from master.dbo.spt_values where type = 'B' and number = 1
	select @none = name from master.dbo.spt_values where type = 'B' and number = 2

	-- If no @objname given, give a little info about all objects.
	if @objname is null
	begin
		-- DISPLAY ALL SYSOBJECTS --
        select
            'Name'          = o.name,
            'Owner'         = user_name(uid),
            'Object_type'   = substring(v.name,5,31)
        from sysobjects o, master.dbo.spt_values v
        where o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
        order by Object_type desc, Name asc

		print ' '

		-- DISPLAY ALL USER TYPES
		select
			'User_type'		= name,
			'Storage_type'	= type_name(xtype),
			'Length'		= length,
			'Prec'			= TypeProperty(name, 'precision'),
			'Scale'			= TypeProperty(name, 'scale'),
			'Nullable'		= case when TypeProperty(name, 'AllowsNull') = 1
											then @yes else @no end,
			'Default_name'	= isnull(object_name(tdefault), @none),
			'Rule_name'		= isnull(object_name(domain), @none),
			'Collation'		= collation
		from systypes
		where xusertype > 256
		order by name

		return(0)
	end

	-- Make sure the @objname is local to the current database.
	select @dbname = parsename(@objname,3)

	if @dbname is not null and @dbname <> db_name()
		begin
			raiserror(15250,-1,-1)
			return(1)
		end

	-- @objname must be either sysobjects or systypes: first look in sysobjects
	declare @objid int
	declare @sysobj_type char(2)
	select @objid = id, @sysobj_type = xtype from sysobjects where id = object_id(@objname)

	-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
	if @objid is null
	begin
		-- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
		select @objid = xusertype from systypes where name = @objname

		-- IF NOT IN SYSTYPES, GIVE UP
		if @objid is null
		begin
			select @dbname=db_name()
			raiserror(15009,-1,-1,@objname,@dbname)
			return(1)
		end

		-- DATA TYPE HELP (prec/scale only valid for numerics)
		select
			'Type_name'		= name,
			'Storage_type'	= type_name(xtype),
			'Length'		= length,
			'Prec'			= TypeProperty(name, 'precision'),
			'Scale'			= TypeProperty(name, 'scale'),
			'Nullable'		= case when allownulls=1 then @yes else @no end,
			'Default_name'	= isnull(object_name(tdefault), @none),
			'Rule_name'		= isnull(object_name(domain), @none),
			'Collation'		= collation
		from systypes
		where xusertype = @objid

		return(0)
	end

	-- FOUND IT IN SYSOBJECT, SO GIVE OBJECT INFO
	select
		'Name'				= o.name,
		'Owner'				= user_name(uid),
        'Type'              = substring(v.name,5,31),
		'Created_datetime'	= o.crdate
	from sysobjects o, master.dbo.spt_values v
	where o.id = @objid and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'

	print ' '

	-- DISPLAY COLUMN IF TABLE / VIEW
	if @sysobj_type in ('S ','U ','V ','TF','IF')
	begin

		-- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE
		declare @numtypes nvarchar(80)
		select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'

		-- INFO FOR EACH COLUMN
		print ' '
		select
			'Column_name'			= name,
			'Type'					= type_name(xusertype),
			'Computed'				= case when iscomputed = 0 then @no else @yes end,
			'Length'				= convert(int, length),
			'Prec'					= case when charindex(type_name(xtype), @numtypes) > 0
										then convert(char(5),ColumnProperty(id, name, 'precision'))
										else '     ' end,
			'Scale'					= case when charindex(type_name(xtype), @numtypes) > 0
										then convert(char(5),OdbcScale(xtype,xscale))
										else '     ' end,
			'Nullable'				= case when isnullable = 0 then @no else @yes end,
			'TrimTrailingBlanks'	= case ColumnProperty(@objid, name, 'UsesAnsiTrim')
										when 1 then @no
										when 0 then @yes
										else '(n/a)' end,
			'FixedLenNullInSource'	= case
						when type_name(xtype) not in ('varbinary','varchar','binary','char')
							Then '(n/a)'
						When status & 0x20 = 0 Then @no
						Else @yes END,
			'Collation'		= collation
		from syscolumns where id = @objid and number = 0 order by colid

		-- IDENTITY COLUMN?
		if @sysobj_type in ('S ','U ','V ','TF')
		begin
			print ' '
			declare @colname sysname
			select @colname = name from syscolumns where id = @objid
						and colstat & 1 = 1
			select
				'Identity'				= isnull(@colname,'No identity column defined.'),
				'Seed'					= ident_seed(@objname),
				'Increment'				= ident_incr(@objname),
				'Not For Replication'	= ColumnProperty(@objid, @colname, 'IsIDNotForRepl')
			-- ROWGUIDCOL?
			print ' '
			select @colname = null
			select @colname = name from syscolumns where id = @objid and number = 0
						and ColumnProperty(@objid, name, 'IsRowGuidCol') = 1
			select 'RowGuidCol' = isnull(@colname,'No rowguidcol column defined.')
		end
	end

	-- DISPLAY PROC PARAMS
	if @sysobj_type in ('P ') --RF too?
	begin
		-- ANY PARAMS FOR THIS PROC?
		if exists (select id from syscolumns where id = @objid)
		begin
			-- INFO ON PROC PARAMS
			print ' '
			select
				'Parameter_name'	= name,
				'Type'				= type_name(xusertype),
                'Length'			= length,
                'Prec'				= case when type_name(xtype) = 'uniqueidentifier' then xprec
										else OdbcPrec(xtype, length, xprec) end,
                'Scale'				= OdbcScale(xtype,xscale),
                'Param_order'		= colid,
				'Collation'		= collation

			from syscolumns where id = @objid
		end
	end

	-- DISPLAY TABLE INDEXES & CONSTRAINTS
	if @sysobj_type in ('S ','U ')
	begin
		print ' '
		execute sp_objectfilegroup @objid
		print ' '
		execute sp_helpindex @objname
		print ' '
		execute sp_helpconstraint @objname,'nomsg'
		if (select count(*) from sysdepends where depid = @objid and deptype = 1) = 0
		begin
			raiserror(15647,-1,-1) -- 'No views with schemabinding reference this table.'
		end
		else
		begin
            select distinct 'Table is referenced by views' = obj.name from sysobjects obj, sysdepends deps
				where obj.xtype ='V' and obj.id = deps.id and deps.depid = @objid
					and deps.deptype = 1 group by obj.name

		end
	end
	else if @sysobj_type in ('V ')
	begin
		-- VIEWS DONT HAVE CONSTRAINTS, BUT PRINT THESE MESSAGES BECAUSE 6.5 DID
		print ' '
		raiserror(15469,-1,-1) -- No constraints defined
		print ' '
		raiserror(15470,-1,-1) --'No foreign keys reference this table.'
		execute sp_helpindex @objname
	end

	return (0) -- sp_help
go

checkpoint
go


raiserror(15339,-1,-1,'sp_helprotect')
go
CREATE PROCEDURE sp_helprotect
	@name				ncharacter varying(776)  = NULL
	,@username			sysname  = NULL
	,@grantorname		sysname  = NULL
	,@permissionarea	character varying(10)  = 'o s'
as

/********
Explanation of the parms...
---------------------------
@name:  Name of [Owner.]Object and Statement; meaning
for sysprotects.id and sysprotects.action at the
same time; thus see parm @permissionarea.
   Examples-   'user2.tb'  , 'CREATE TABLE', null

@username:  Name of the grantee (for sysprotects.uid).
   Examples-   'user2', null

@grantorname:  Name of the grantor (for sysprotects.grantor).
   Examples-   'user2' --Would prevent report rows which would
                       --  have 'dbo' as grantor.

@permissionarea:  O=Object, S=Statement; include all which apply.
   Examples-   'o'  , ',s'  , 'os'  , 'so'  , 's o'  , 's,o'
GeneMi
********/

	Set nocount on

	Declare
	@vc1                   sysname
	,@Int1                  integer

	Declare
	@charMaxLenOwner		character varying(11)
	,@charMaxLenObject		character varying(11)
	,@charMaxLenGrantee		character varying(11)
	,@charMaxLenGrantor		character varying(11)
	,@charMaxLenAction		character varying(11)
	,@charMaxLenColumnName	character varying(11)

	Declare
	@OwnerName				sysname
	,@ObjectStatementName	sysname


	/* Perform temp table DDL here to minimize compilation costs*/
CREATE Table #t1_Prots
	(	Id					int				Null
		,Type1Code			char(6)			collate database_default NOT Null
		,ObjType			char(2)			collate database_default Null

		,ActionName		varchar(20)			collate database_default Null
		,ActionCategory	char(2)				collate database_default Null
		,ProtectTypeName	char(10)		collate database_default Null

		,Columns_Orig		varbinary(32)	Null

		,OwnerName			sysname			collate database_default NOT Null
		,ObjectName			sysname			collate database_default NOT Null
		,GranteeName		sysname			collate database_default NOT Null
		,GrantorName		sysname			collate database_default NOT Null

		,ColumnName			sysname			collate database_default Null
		,ColId				smallint		Null

		,Max_ColId			smallint		Null
		,All_Col_Bits_On	tinyint			Null
		,new_Bit_On			tinyint			Null )  -- 1=yes on


	/*	Check for valid @permissionarea */
	Select @permissionarea = upper( isnull(@permissionarea,'?') )

	IF (	charindex('O',@permissionarea) <= 0
		AND  charindex('S',@permissionarea) <= 0)
	begin
		raiserror(15300,-1,-1 ,@permissionarea,'o,s')
		return (1)
	end

	select @vc1 = parsename(@name,3)

	/* Verified db qualifier is current db*/
	IF (@vc1 is not null and @vc1 <> db_name())
	begin
		raiserror(15302,-1,-1)  --Do not qualify with DB name.
		return (1)
	end

	/*  Derive OwnerName and @ObjectStatementName*/
	select	@OwnerName				=	parsename(@name, 2)
			,@ObjectStatementName	=	parsename(@name, 1)

	IF (@ObjectStatementName is NULL and @name is not null)
	begin
		raiserror(15253,-1,-1,@name)
		return (1)
	end

	/*	Copy info from sysprotects for processing	*/
	IF charindex('O',@permissionarea) > 0
	begin
		/*	Copy info for objects	*/
		INSERT	#t1_Prots
        (	Id
			,Type1Code

			,ObjType
			,ActionName
			,ActionCategory
			,ProtectTypeName

			,Columns_Orig
			,OwnerName
			,ObjectName
			,GranteeName

			,GrantorName
			,ColumnName
            ,ColId

			,Max_ColId
			,All_Col_Bits_On
			,new_Bit_On	)

	/*	1Regul indicates action can be at column level,
		2Simpl indicates action is at the object level */
		SELECT	id
				,case
					when columns is null then '2Simpl'
					else '1Regul'
				end

				,Null
				,val1.name
				,'Ob'
				,val2.name

				,columns
				,user_name(objectproperty( id, 'ownerid' ))
				,object_name(id)
				,user_name(uid)

				,user_name(grantor)
				,case
					when columns is null then '.'
					else Null
				end
				,-123

				,Null
				,Null
				,Null
		FROM	sysprotects sysp
				,master.dbo.spt_values  val1
				,master.dbo.spt_values  val2
		where	(@OwnerName is null or user_name(objectproperty( id, 'ownerid' )) = @OwnerName)
		and	(@ObjectStatementName is null or object_name(id) =  @ObjectStatementName)
		and	(@username is null or user_name(uid) =  @username)
		and	(@grantorname is null or user_name(grantor) =  @grantorname)
		and	val1.type     = 'T'
		and	val1.number   = sysp.action
		and	val2.type     = 'T' --T is overloaded.
		and	val2.number   = sysp.protecttype
		and sysp.id != 0


		IF EXISTS (SELECT * From #t1_Prots)
		begin
			UPDATE	#t1_Prots set ObjType = ob.xtype
			FROM	sysobjects    ob
			WHERE	ob.id	=  #t1_Prots.Id


			UPDATE 	#t1_Prots
			set		Max_ColId = (select max(colid) from syscolumns sysc
								where #t1_Prots.Id = sysc.id)	-- colid may not consecutive if column dropped
			where Type1Code = '1Regul'


			/*	First bit set indicates actions pretains to new columns. (i.e. table-level permission)
				Set new_Bit_On accordinglly							*/
			UPDATE	#t1_Prots SET new_Bit_On =
			CASE	convert(int,substring(Columns_Orig,1,1)) & 1
				WHEN	1 then	1
				ELSE	0
			END
			WHERE	ObjType	<> 'V'	and	 Type1Code = '1Regul'


			/* Views don't get new columns	*/
			UPDATE #t1_Prots set new_Bit_On = 0
			WHERE  ObjType = 'V'


			/*	Indicate enties where column level action pretains to all
				columns in table All_Col_Bits_On = 1					*/
			UPDATE	#t1_Prots	set		All_Col_Bits_On = 1
			where	#t1_Prots.Type1Code	 =  '1Regul'
			and	not exists 
				(select *
				from syscolumns sysc, master..spt_values v
				where #t1_Prots.Id = sysc.id and sysc.colid = v.number
				and v.number <= Max_ColId		-- column may be dropped/added after Max_ColId snap-shot 
				and v.type = 'P' and
			/*	Columns_Orig where first byte is 1 means off means on and on mean off
				where first byte is 0 means off means off and on mean on	*/
					case convert(int,substring(#t1_Prots.Columns_Orig, 1, 1)) & 1
						when 0 then convert(tinyint, substring(#t1_Prots.Columns_Orig, v.low, 1))
						else (~convert(tinyint, isnull(substring(#t1_Prots.Columns_Orig, v.low, 1),0)))
					end & v.high = 0)


			/* Indicate entries where column level action pretains to
				only some of columns in table  All_Col_Bits_On  =  0*/
			UPDATE	#t1_Prots	set  All_Col_Bits_On  =  0
			WHERE	#t1_Prots.Type1Code  =  '1Regul'
			and	All_Col_Bits_On  is  null


			Update #t1_Prots
			set ColumnName  =
			case
				when All_Col_Bits_On = 1 and new_Bit_On = 1 then '(All+New)'
				when All_Col_Bits_On = 1 and new_Bit_On = 0 then '(All)'
				when All_Col_Bits_On = 0 and new_Bit_On = 1 then '(New)'
			end
			from	#t1_Prots
			where	ObjType    IN ('S ' ,'U ', 'V ')
			and	Type1Code = '1Regul'
			and   NOT (All_Col_Bits_On = 0 and new_Bit_On = 0)


			/* Expand and Insert individual column permission rows */
			INSERT	into   #t1_Prots
				(Id
				,Type1Code
				,ObjType
				,ActionName

				,ActionCategory
				,ProtectTypeName
				,OwnerName
				,ObjectName

				,GranteeName
				,GrantorName
				,ColumnName
				,ColId	)
		   SELECT	prot1.Id
					,'1Regul'
					,ObjType
					,ActionName

					,ActionCategory
					,ProtectTypeName
					,OwnerName
					,ObjectName

					,GranteeName
					,GrantorName
					,col_name ( prot1.Id ,val1.number )
					,val1.number
			from	#t1_Prots              prot1
					,master.dbo.spt_values  val1
					,syscolumns sysc
			where	prot1.ObjType    IN ('S ' ,'U ' ,'V ')
				and	prot1.All_Col_Bits_On = 0
				and prot1.Id	= sysc.id
				and	val1.type   = 'P'
				and	val1.number = sysc.colid
				and
				case convert(int,substring(prot1.Columns_Orig, 1, 1)) & 1
					when 0 then convert(tinyint, substring(prot1.Columns_Orig, val1.low, 1))
					else (~convert(tinyint, isnull(substring(prot1.Columns_Orig, val1.low, 1),0)))
				end & val1.high <> 0

			delete from #t1_Prots
					where	ObjType    IN ('S ' ,'U ' ,'V ')
							and	All_Col_Bits_On = 0
							and new_Bit_On = 0
		end
	end


	/* Handle statement permissions here*/
	IF (charindex('S',@permissionarea) > 0)
	begin
	   /*	All statement permissions are 2Simpl */
		INSERT	#t1_Prots
			 (	Id
				,Type1Code
				,ObjType
				,ActionName

				,ActionCategory
				,ProtectTypeName
				,Columns_Orig
				,OwnerName

				,ObjectName
				,GranteeName
				,GrantorName
				,ColumnName

				,ColId
				,Max_ColId
				,All_Col_Bits_On
				,new_Bit_On	)
		SELECT	id
				,'2Simpl'
				,Null
				,val1.name

				,'St'
				,val2.name
				,columns
				,'.'

				,'.'
				,user_name(sysp.uid)
				,user_name(sysp.grantor)
				,'.'
				,-123

				,Null
				,Null
				,Null
		FROM	sysprotects				sysp
				,master.dbo.spt_values	val1
				,master.dbo.spt_values  val2
		where	(@username is null or user_name(sysp.uid) = @username)
			and	(@grantorname is null or user_name(sysp.grantor) = @grantorname)
			and	val1.type     = 'T'
			and	val1.number   =  sysp.action
			and	(@ObjectStatementName is null or val1.name = @ObjectStatementName)
			and	val2.number   = sysp.protecttype
			and	val2.type     = 'T'
			and sysp.id = 0
	end


	IF NOT EXISTS (SELECT * From #t1_Prots)
	begin
		raiserror(15330,-1,-1)
		return (1)
	end


	/*	Calculate dynamic display col widths		*/
	SELECT
	@charMaxLenOwner       =
		convert ( varchar, max(datalength(OwnerName)))

	,@charMaxLenObject      =
		convert ( varchar, max(datalength(ObjectName)))

	,@charMaxLenGrantee     =
		convert ( varchar, max(datalength(GranteeName)))

	,@charMaxLenGrantor     =
		convert ( varchar, max(datalength(GrantorName)))

	,@charMaxLenAction      =
		convert ( varchar, max(datalength(ActionName)))

	,@charMaxLenColumnName  =
		convert ( varchar, max(datalength(ColumnName)))
	from	#t1_Prots


/*  Output the report	*/
EXECUTE(
'Set nocount off

SELECT	''Owner''		= substring (OwnerName   ,1 ,' + @charMaxLenOwner   + ')

		,''Object''		= substring (ObjectName  ,1 ,' + @charMaxLenObject  + ')

		,''Grantee''	= substring (GranteeName ,1 ,' + @charMaxLenGrantee + ')

		,''Grantor''	= substring (GrantorName ,1 ,' + @charMaxLenGrantor + ')

		,''ProtectType''= ProtectTypeName

		,''Action''		= substring (ActionName ,1 ,' + @charMaxLenAction + ')

		,''Column''		= substring (ColumnName ,1 ,' + @charMaxLenColumnName + ')
   from	#t1_Prots
   order by
		ActionCategory
		,Owner				,Object
		,Grantee			,Grantor
		,ProtectType		,Action
		,ColId  --Multiple  -123s  ( <0 )  possible

Set nocount on'
)

Return (0) -- sp_helprotect
go

checkpoint
go

raiserror(15339,-1,-1,'sp_helptext')
go
create procedure sp_helptext --- 1996/04/08 00:00
@objname nvarchar(776)
,@columnname sysname = NULL
as

set nocount on

declare @dbname sysname
,@BlankSpaceAdded   int
,@BasePos       int
,@CurrentPos    int
,@TextLength    int
,@LineId        int
,@AddOnLen      int
,@LFCR          int --lengths of line feed carriage return
,@DefinedLength int

/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/
,@SyscomText	nvarchar(4000)
,@Line          nvarchar(255)

Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores
                             trailing blank spaces*/
CREATE TABLE #CommentText
(LineId	int
 ,Text  nvarchar(255) collate database_default)

/*
**  Make sure the @objname is local to the current database.
*/
select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
        begin
                raiserror(15250,-1,-1)
                return (1)
        end

/*
**  See if @objname exists.
*/
if (object_id(@objname) is null)
        begin
		select @dbname = db_name()
		raiserror(15009,-1,-1,@objname,@dbname)
                return (1)
        end

-- If second parameter was given.
if ( @columnname is not null)
    begin
        -- Check if it is a table
        if (select count(*) from sysobjects where id = object_id(@objname) and xtype in ('S ','U ','TF'))=0
            begin
                raiserror(15218,-1,-1,@objname)
                return(1)
            end
        -- check if it is a correct column name
        if ((select 'count'=count(*) from syscolumns where name = @columnname and id = object_id(@objname) and number = 0) =0)
            begin
                raiserror(15645,-1,-1,@columnname)
                return(1)
            end
    if ((select iscomputed from syscolumns where name = @columnname and id = object_id(@objname) and number = 0) = 0)
		begin
			raiserror(15646,-1,-1,@columnname)
			return(1)
		end

        DECLARE ms_crs_syscom  CURSOR LOCAL
        FOR SELECT text FROM syscomments WHERE id = object_id(@objname) and encrypted = 0 and number =
                        (select colid from syscolumns where name = @columnname and id = object_id(@objname) and number = 0)
                        order by number,colid
        FOR READ ONLY

    end
else
    begin
        /*
        **  Find out how many lines of text are coming back,
        **  and return if there are none.
        */
        if (select count(*) from syscomments c, sysobjects o where o.xtype not in ('S', 'U')
            and o.id = c.id and o.id = object_id(@objname)) = 0
                begin
                        raiserror(15197,-1,-1,@objname)
                        return (1)
                end

        if (select count(*) from syscomments where id = object_id(@objname)
            and encrypted = 0) = 0
                begin
                        raiserror(15471,-1,-1)
                        return (0)
                end

        DECLARE ms_crs_syscom  CURSOR LOCAL
        FOR SELECT text FROM syscomments WHERE id = OBJECT_ID(@objname) and encrypted = 0
                ORDER BY number, colid
        FOR READ ONLY
    end

/*
**  Else get the text.
*/
SELECT @LFCR = 2
SELECT @LineId = 1


OPEN ms_crs_syscom

FETCH NEXT FROM ms_crs_syscom into @SyscomText

WHILE @@fetch_status >= 0
BEGIN

    SELECT  @BasePos    = 1
    SELECT  @CurrentPos = 1
    SELECT  @TextLength = LEN(@SyscomText)

    WHILE @CurrentPos  != 0
    BEGIN
        --Looking for end of line followed by carriage return
        SELECT @CurrentPos =   CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)

        --If carriage return found
        IF @CurrentPos != 0
        BEGIN
            /*If new value for @Lines length will be > then the
            **set length then insert current contents of @line
            **and proceed.
            */
            While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
            BEGIN
                SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
                INSERT #CommentText VALUES
                ( @LineId,
                  isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                SELECT @Line = NULL, @LineId = @LineId + 1,
                       @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
            END
            SELECT @Line    = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
            SELECT @BasePos = @CurrentPos+2
            INSERT #CommentText VALUES( @LineId, @Line )
            SELECT @LineId = @LineId + 1
            SELECT @Line = NULL
        END
        ELSE
        --else carriage return not found
        BEGIN
            IF @BasePos <= @TextLength
            BEGIN
                /*If new value for @Lines length will be > then the
                **defined length
                */
                While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
                BEGIN
                    SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0)  + @BlankSpaceAdded )
                    INSERT #CommentText VALUES
                    ( @LineId,
                      isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                    SELECT @Line = NULL, @LineId = @LineId + 1,
                        @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
                END
                SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
                if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
                BEGIN
                    SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1
                END
            END
        END
    END

	FETCH NEXT FROM ms_crs_syscom into @SyscomText
END

IF @Line is NOT NULL
    INSERT #CommentText VALUES( @LineId, @Line )

select Text from #CommentText order by LineId

CLOSE  ms_crs_syscom
DEALLOCATE 	ms_crs_syscom

DROP TABLE 	#CommentText

return (0) -- sp_helptext
go


--Running this create AFTER create sp_helpgroup.
raiserror(15339,-1,-1,'sp_helpuser')
go
CREATE PROCEDURE sp_helpuser  --- 1996/08/14 10:33
    @name_in_db       sysname    = NULL --User,Group,Alias
AS

Set nocount on
Set ansi_warnings off

Declare
    @RetCode               int
   ,@_rowcount             int

Declare
    @charMaxLen_UsName     varchar(11)
   ,@charMaxLen_GrName     varchar(11)
   ,@charMaxLen_LoName     varchar(11)
   ,@charMaxLen_DbName     varchar(11)

Declare
    @Name1Type             char(2)
   ,@CMaxUsUID             smallint

-----------------------  create holding table  --------------------
/*Create temp table before any DML to ensure dynamic*/

Create Table #tb1_uga
   (
    zUserName        sysname        collate database_default Null
   ,zGroupName       sysname        collate database_default Null
   ,zLoginName       sysname        collate database_default Null
   ,zDefDBName       sysname        collate database_default Null
   ,zUID             smallint       Null
   ,zSID             varbinary(85)  Null
   )

--------

Select
    @RetCode               = 0
   ,@Name1Type             = Null
   ,@CMaxUsUID			   = 16383


-------------  What type of value (U,G,A) was input?  --------------

-------- NULL

IF (@name_in_db IS Null)
   begin

   Select @Name1Type = '-'


   INSERT into  #tb1_uga
               (
                zUserName
               ,zGroupName
               ,zLoginName
               ,zDefDBName
               ,zUID
               ,zSID
               )
      SELECT
                   usu.name
                  ,case
					when (usg.uid is null) then 'public'
					else usg.name
				   end
                  ,lo.loginname
                  ,lo.dbname
                  ,usu.uid
                  ,usu.sid
         from
				   sysusers	usu left outer join
					(sysmembers mem inner join sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid
                   left outer join master.dbo.syslogins  lo on usu.sid = lo.sid
         where
				   (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1) and
				   (usg.issqlrole = 1 or usg.uid is null)


   GOTO LABEL_25NAME1TYPEKNOWN

   end


-------- USER

INSERT   into   #tb1_uga
               (
                zUserName
               ,zGroupName
               ,zLoginName
               ,zDefDBName
               ,zUID
               ,zSID
               )
      SELECT
                   usu.name
                  ,case
					when (usg.uid is null) then 'public'
					else usg.name
				   end
                  ,lo.loginname
                  ,lo.dbname
                  ,usu.uid
                  ,usu.sid
         from
				   sysusers	usu left outer join
					(sysmembers mem inner join sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid
                   left outer join master.dbo.syslogins  lo on usu.sid = lo.sid
         where
				   (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1) and
				   (usg.issqlrole = 1 or usg.uid is null) and
                   usu.name    = @name_in_db


Select @_rowcount = @@rowcount


IF (@_rowcount > 0)
   begin
   Select @Name1Type = 'US'

   GOTO LABEL_25NAME1TYPEKNOWN

   end


 -------- ALIAS

INSERT   into   #tb1_uga
               (
                zUserName
               ,zGroupName
               ,zLoginName
               ,zDefDBName
               ,zUID
               ,zSID
               )

	SELECT
                   usu.name
                  ,case
					when (usg.uid is null) then 'public'
					else usg.name
				   end
                  ,lo.loginname
                  ,lo.dbname
                  ,usu.uid
                  ,usu.sid
         from	   (SELECT sid, altuid FROM sysusers WHERE isaliased = 1) al inner join
				   (sysusers	usu left outer join
					(sysmembers mem inner join sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid
                   left outer join master.dbo.syslogins  lo on usu.sid = lo.sid) on al.altuid  = usu.uid
         where
				   (usu.islogin = 1 and usu.isaliased = 0) and
				   (usg.issqlrole = 1 or usg.uid is null) and
                   al.sid     = suser_sid(@name_in_db)


Select @_rowcount = @@rowcount


IF (@_rowcount > 0)
   begin
   Select @Name1Type = 'AL'

   GOTO LABEL_25NAME1TYPEKNOWN

   end


-------- GROUP
IF EXISTS
      (SELECT * FROM sysusers
         WHERE  name = @name_in_db
         AND (issqlrole = 1)
      )
   begin
   Select @Name1Type = 'GR'

   Execute sp_helpgroup @name_in_db

   GOTO LABEL_75FINAL  --Done

   end

-------- Error
Raiserror(15198,-1,-1 ,@name_in_db)  --Input Name is unfound
Select @RetCode = @RetCode | 1

GOTO LABEL_75FINAL

--------


LABEL_25NAME1TYPEKNOWN:


-----------------------  Printout the report  -------------------------

-------- Preparations for dynamic exec

SELECT
          @charMaxLen_UsName  = convert( varchar,
                  isnull( max( datalength( zUserName)),8))

         ,@charMaxLen_GrName  = convert( varchar,
                  isnull( max( datalength( zGroupName)),9))

         ,@charMaxLen_LoName  = convert( varchar,
                  isnull( max( datalength( zLoginName)),9))

         ,@charMaxLen_DbName  = convert( varchar,
                  isnull( max( datalength( zDefDBName)),9))
   from
          #tb1_uga


-------- Dynamic EXEC() to printout report


EXECUTE(
'
SELECT
             ''UserName''  =
                     substring(zUserName ,1,' + @charMaxLen_UsName + ')

            ,''GroupName'' =
                     substring(zGroupName,1,' + @charMaxLen_GrName + ')

            ,''LoginName'' =
                     substring(zLoginName,1,' + @charMaxLen_LoName + ')

            ,''DefDBName'' =
                     substring(zDefDBName,1,' + @charMaxLen_DbName + ')

            ,''UserID''    = convert(char(6),zUID)

            ,''SID''   = zSID
      from
             #tb1_uga
      order by
             1
'
)

-----------------------  A little extra nice-to-have

IF (@Name1Type IN ('-','US'))
   begin

   IF EXISTS (SELECT * FROM #tb1_uga tb1 ,(SELECT altuid FROM sysusers WHERE isaliased = 1) al, sysusers us
                       WHERE tb1.zUID = us.uid and us.uid = al.altuid
             )
      begin

      SELECT   'LoginName' = suser_sname(al.sid)
              ,'UserNameAliasedTo' = tb1.zUserName
         from  #tb1_uga tb1 ,(SELECT sid, altuid FROM sysusers WHERE isaliased = 1) al, sysusers us
         WHERE tb1.zUID = us.uid and us.uid = al.altuid
         order by 1

      end
   end


-----------------------  Finalization  ----------------------


LABEL_75FINAL:


IF (object_id('tempdb..#tb1_uga') IS not Null)
            Drop Table #tb1_uga

return (0) -- sp_helpuser
go


raiserror(15339,-1,-1,'sp_indexoption')
go
create procedure sp_indexoption
    @IndexNamePattern      nvarchar(776)
   ,@OptionName            varchar(35)
   ,@OptionValue           varchar(12)
as
	-- DECLARE VARIABLES
	DECLARE @tabid  int
			,@indid  int
			,@uid int
			,@intOptionValue  int
			,@flagbit  int
			,@tablename  nvarchar(776)

    -- DISALLOW USER TRANSACTION --
	Set nocount on
	set implicit_transactions off
	IF @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sp_indexoption')
		RETURN @@ERROR
	end

	-- VALIDATE OPTION VALUE
	SELECT @intOptionValue =
		CASE WHEN (lower(@OptionValue) in ('1' ,'on' ,'yes' ,'true')) THEN 1
			WHEN (lower(@OptionValue) in ('0' ,'off' ,'no' ,'false')) THEN 0
		ELSE NULL END

	-- CONVERT ANY OLD-STYLE PARAM TO NEW-STYLE, THEN VALIDATE OPTION NAME
	IF lower(@OptionName) IN ('allowrowlocks','allowpagelocks')
		SELECT @OptionName = 'dis'+@OptionName,
				@intOptionValue = 1-@intOptionValue
	SELECT @flagbit = CASE lower(@OptionName) WHEN 'disallowrowlocks' THEN 1
						WHEN 'disallowpagelocks' THEN 2
						ELSE NULL END

	-- ERROR IF INVALID OPTION NAME OR VALUE
	IF @intOptionValue IS NULL OR @flagbit IS NULL
	begin
		raiserror(15600,-1,-1, 'sp_indexoption')
		RETURN @@ERROR
	end

	-- FIRST CHECK IF GIVEN AN TABLE NAME --
	SELECT @tabid = id, @uid = uid FROM sysobjects
		WHERE id = OBJECT_ID(@IndexNamePattern, 'local') AND xtype = 'U'
	IF @tabid IS NULL
	BEGIN
		-- NOW SEE IF WE HAVE TABLE.INDEX NAME, AND RESOLVE --
		SELECT @tablename =
				IsNull(QuoteName(parsename(@IndexNamePattern, 4),'[')+'.','.') +
				IsNull(QuoteName(parsename(@IndexNamePattern, 3),'[')+'.','.') +
				IsNull(QuoteName(parsename(@IndexNamePattern, 2),'['),'')
		SELECT @tabid = OBJECT_ID(@tablename, 'U')
		SELECT @uid = ObjectProperty(@tabid, 'OwnerId'),
				@indid = IndexProperty(@tabid, parsename(@IndexNamePattern, 1), 'IndexId')
		IF @indid IN (0,255)
			SELECT @indid = NULL
	END
	ELSE
		SELECT @tablename = @IndexNamePattern, @indid = 0	-- indicate all-indexes-for-table

	-- WE KNOW NOW IF WE HAVE A VALID TABLE/INDEX --
	IF @tabid IS NULL OR @uid IS NULL OR @indid IS NULL
	BEGIN
		raiserror(15388,-1,-1,@IndexNamePattern)
		RETURN @@ERROR
	END

	-- DO THE WORK (DBCC LOCKOBJECTSCHEMA will check permissions) --
	BEGIN TRAN
	DBCC LOCKOBJECTSCHEMA (@tablename)
	dbcc invalidate_textptr_objid(@tabid)	-- Invalidate inrow text pointers for table
	UPDATE sysindexes SET lockflags = (lockflags & ~@flagbit) | (@flagbit * @intOptionValue)
				WHERE id = @tabid AND (indid = @indid OR @indid = 0)
	COMMIT TRAN

	-- RETURN SUCCESS
	RETURN 0 -- sp_indexoption
go

-------------------------------------------------------------

raiserror(15339,-1,-1,'sp_lock')
go
create procedure sp_lock --- 1996/04/08 00:00
@spid1 int = NULL,		/* server process id to check for locks */
@spid2 int = NULL		/* other process id to check for locks */
as

set nocount on
/*
**  Show the locks for both parameters.
*/
if @spid1 is not NULL
begin
	select 	convert (smallint, req_spid) As spid,
		rsc_dbid As dbid,
		rsc_objid As ObjId,
		rsc_indid As IndId,
		substring (v.name, 1, 4) As Type,
		substring (rsc_text, 1, 16) as Resource,
		substring (u.name, 1, 8) As Mode,
		substring (x.name, 1, 5) As Status

	from 	master.dbo.syslockinfo,
		master.dbo.spt_values v,
		master.dbo.spt_values x,
		master.dbo.spt_values u

	where   master.dbo.syslockinfo.rsc_type = v.number
			and v.type = 'LR'
			and master.dbo.syslockinfo.req_status = x.number
			and x.type = 'LS'
			and master.dbo.syslockinfo.req_mode + 1 = u.number
			and u.type = 'L'

			and req_spid in (@spid1, @spid2)
end

/*
**  No parameters, so show all the locks.
*/
else
begin
	select 	convert (smallint, req_spid) As spid,
		rsc_dbid As dbid,
		rsc_objid As ObjId,
		rsc_indid As IndId,
		substring (v.name, 1, 4) As Type,
		substring (rsc_text, 1, 16) as Resource,
		substring (u.name, 1, 8) As Mode,
		substring (x.name, 1, 5) As Status

	from 	master.dbo.syslockinfo,
		master.dbo.spt_values v,
		master.dbo.spt_values x,
		master.dbo.spt_values u

	where   master.dbo.syslockinfo.rsc_type = v.number
			and v.type = 'LR'
			and master.dbo.syslockinfo.req_status = x.number
			and x.type = 'LS'
			and master.dbo.syslockinfo.req_mode + 1 = u.number
			and u.type = 'L'
	order by spid
end

return (0) -- sp_lock
go

raiserror(15339,-1,-1,'sp_getapplock')
go
create procedure sp_getapplock --- 1999/04/14 00:00
 @Resource nvarchar (255) = NULL,           -- Resource to lock
 @LockMode varchar (32),                    -- Lock mode
 @LockOwner varchar (32) = 'Transaction',   -- Lock Owner - [D = Transaction]
 @LockTimeout int = NULL                    -- Lock timeout [D = Session setting]
as

  declare @mode integer
  declare @owner integer
  declare @result integer
  declare @dbid integer

  select @mode =
   CASE @LockMode
     When ('Shared')            Then 3
     When ('Update')            Then 4
     When ('Exclusive')         Then 5
     When ('IntentExclusive')   Then 8
     When ('IntentShared')      Then 6
     Else -1
   END

  if @mode = -1
  begin
    raiserror(15625, -1, -1, @LockMode, N'@LockMode')
    return (-999)
  end

  select @owner =
   CASE @LockOwner
    When ('Transaction')    Then 1
    When ('Session')        Then 3
    Else -1
   END

  if @owner = -1
  begin
    raiserror(15625, -1, -1, @LockOwner, N'@LockOwner')
    return (-999)
  end

  if @LockTimeout is null
  begin
    set @LockTimeout = @@LOCK_TIMEOUT
  end

  select @dbid = db_id ()

  if @owner = 1 and @@trancount = 0
  begin
    raiserror(15626, -1, -1)
    return (-999)
  end

  exec @result = master.dbo.xp_userlock 0, @dbid, @Resource, @mode, @owner, @LockTimeout

  return @result
go

checkpoint
go

raiserror(15339,-1,-1,'sp_releaseapplock')
go
create procedure sp_releaseapplock --- 1999/04/14 00:00
 @Resource nvarchar (255) = NULL,	    -- Resource to unlock
 @LockOwner varchar (32) = 'Transaction'    -- Lock Owner - [D = Transaction]
as

  declare @owner integer
  declare @result integer
  declare @dbid integer

  select @owner =
	CASE @LockOwner
	 When ('Transaction')   Then 1
	 When ('Session')       Then 3
	 Else -1
	END

  if @owner = -1
  begin
    raiserror(15625, -1, -1, @LockOwner, N'@LockOwner')
    return (-999)
  end

  select @dbid = db_id ()

  exec @result = master.dbo.xp_userlock 1, @dbid, @Resource, 0, @owner

  return @result
go



raiserror(15339,-1,-1,'sp_logdevice')
go
create procedure sp_logdevice
@dbname sysname,
@devicename sysname
as
declare @stmt nvarchar(1150)
declare @countrows int
declare @size nvarchar (10)
declare @maxsize nvarchar (10)
declare @growth nvarchar (10)
declare @filename sysname
set nocount on

-- Make sure the database exists
--
if not exists (select * from master.dbo.sysdatabases where name = @dbname)
	begin
		raiserror(15010,-1,-1,@dbname)
		return (1)
	end

-- Make sure the file exists and it should be in sysdevices as this is only
-- for older syntax.
--
if not exists (select * from master.dbo.sysdevices where name = @devicename)
	begin
		raiserror(15012,-1,-1,@devicename)
		return (1)
	end

-- Calculate the specs of the current file and save it into a temp table
--
create table #tempsize (size int, growth int, maxsize int, filename sysname collate database_default )
select @stmt = 'INSERT #tempsize SELECT size, growth, maxsize, filename FROM '
				+ @dbname + '.dbo.sysfiles WHERE name = '''
				+ @devicename + ''''
exec (@stmt)
select @countrows = count (*) from #tempsize

-- disconnect with devices and database
--
if @countrows <> 1
	begin
		raiserror(15012,-1,-1,@devicename)
		return (1)
	end

select @stmt = 'ALTER DATABASE ' + @dbname + ' REMOVE FILE ' + @devicename
exec (@stmt)
if @@error <> 0
	begin
		raiserror(15319,-1,-1,@dbname,@devicename)
		return(1)
	end
select @size = convert(nvarchar(10), size/128),
  @maxsize = convert(nvarchar(10), maxsize),
  @growth = convert(nvarchar(10), growth * 8),
  @filename = filename
from #tempsize

if (@maxsize = '-1')
begin
	select @maxsize = 'UNLIMITED'
end
else
begin
	select @maxsize = (convert (int, @maxsize)) / 128
end

select @stmt = 'ALTER DATABASE ' + @dbname
			+ ' ADD LOG FILE (NAME = ['
			+ @devicename + '], FILENAME = ['
			+ @filename + '], SIZE = '
			+ @size + ', MAXSIZE = '
			+ @maxsize + ', FILEGROWTH = '
			+ @growth + 'KB)'
exec (@stmt)
if @@error=0
      begin
      raiserror(15318,-1,-1,@dbname,@devicename)
      end
else
      begin
      raiserror(15319,-1,-1,@dbname,@devicename)
      return (1)
      end

return (0) -- sp_logdevice
GO


raiserror(15339,-1,-1,'sp_helpremotelogin')
go
create procedure sp_helpremotelogin --- 1996/04/08 00:00
@remoteserver sysname = NULL,	/* remote server name */
@remotename sysname = NULL		/* remote login name */
as
set nocount on

/*
**  If no server given, get 'em all.
*/
if not exists (select * from master.dbo.sysservers s, master.dbo.sysremotelogins r
	where s.srvid = r.remoteserverid
		and (@remoteserver is null or s.srvname = @remoteserver ))
	begin
		if @remoteserver is null
			begin
				raiserror(15200,-1,-1)
				return (0)
			end

		raiserror(15201,-1,-1,@remoteserver)
		return (1)
	end

/*
**  If no remotename given, get 'em all.
*/
if not exists (select * from master.dbo.sysremotelogins
	where (@remotename is null or isnull(remoteusername, ' ') = @remotename))
	begin
		if @remotename is null
			begin
				raiserror(15202,-1,-1)
				return (1)
			end

		raiserror(15203,-1,-1,@remotename)
		return (1)

	end

/*
**  Check for empty results.
*/
if not exists (select *
	from master.dbo.sysremotelogins r, master.dbo.sysservers s
	where ( @remotename is null or isnull(r.remoteusername, ' ') = @remotename)
		and s.srvid = r.remoteserverid
		and (@remoteserver is null or s.srvname = @remoteserver))
	begin
		raiserror(15204,-1,-1,@remotename,@remoteserver)
		return (1)
	end

/*
**  Select the information.
*/
select server = substring(s.srvname, 1, 22),
	local_user_name =
		substring(isnull(suser_sname(r.sid), '** use local name **'), 1, 22),
	remote_user_name =
		substring(isnull(r.remoteusername, '** mapped locally **'), 1, 22),
	options = case datalength(v.name)
				when null then ''
				when 0 then ''
				else substring(v.name, 1, 9)
			  end
		from master.dbo.sysservers s, master.dbo.sysremotelogins r,
			master.dbo.spt_values v
	where s.srvid = r.remoteserverid
		and (@remoteserver is null or s.srvname = @remoteserver)
		and (@remotename is null or isnull(r.remoteusername, ' ') = @remotename)
		and v.type = 'F'
		and v.number = r.status
order by server, remote_user_name

return (0) -- sp_helpremotelogin
go



raiserror(15339,-1,-1,'sp_helpsort')
go
create procedure sp_helpsort --- 1996/04/08 00:00
AS
set nocount on

/*
** Now display the server default collation name
*/
declare @servercollation sysname
select @servercollation = convert(sysname, serverproperty('collation'))

if @servercollation is not NULL
	BEGIN
	select 'Server default collation' = description
		from ::fn_helpcollations() C
		where @servercollation = C.name
	END

set nocount off
return(0) -- sp_helpsort
go


checkpoint
go


raiserror(15339,-1,-1,'sp_helpsql')
go

create procedure sp_helpsql
@in_command varchar(30) = NULL
AS
print 'SP_HELPSQL is not supported in this release, please refer to Online Help.'
return(0)
go


raiserror(15339,-1,-1,'sp_monitor')
go
create procedure sp_monitor --- 1996/04/08 00:00
as

/*
**  Declare variables to be used to hold current monitor values.
*/
declare @now 		datetime
declare @cpu_busy 	int
declare @io_busy	int
declare @idle		int
declare @pack_received	int
declare @pack_sent	int
declare @pack_errors	int
declare @connections	int
declare @total_read	int
declare @total_write	int
declare @total_errors	int

declare @oldcpu_busy 	int	/* used to see if DataServer has been rebooted */
declare @interval	int
declare @mspertick	int	/* milliseconds per tick */

/*
**  If we're in a transaction, disallow this since it might make recovery
**  impossible.
*/
set implicit_transactions off
if @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sp_monitor')
		return (1)
	end

/*
**  Set @mspertick.  This is just used to make the numbers easier to handle
**  and avoid overflow.
*/
select @mspertick = convert(int, @@timeticks / 1000.0)

/*
**  Get current monitor values.
*/
select
	@now = getdate(),
	@cpu_busy = @@cpu_busy,
	@io_busy = @@io_busy,
	@idle = @@idle,
	@pack_received = @@pack_received,
	@pack_sent = @@pack_sent,
	@connections = @@connections,
	@pack_errors = @@packet_errors,
	@total_read = @@total_read,
	@total_write = @@total_write,
	@total_errors = @@total_errors

/*
**  Check to see if DataServer has been rebooted.  If it has then the
**  value of @@cpu_busy will be less than the value of spt_monitor.cpu_busy.
**  If it has update spt_monitor.
*/
select @oldcpu_busy = cpu_busy
	from master.dbo.spt_monitor
if @oldcpu_busy > @cpu_busy
begin
	update master.dbo.spt_monitor
		set
			lastrun = @now,
			cpu_busy = @cpu_busy,
			io_busy = @io_busy,
			idle = @idle,
			pack_received = @pack_received,
			pack_sent = @pack_sent,
			connections = @connections,
			pack_errors = @pack_errors,
			total_read = @total_read,
			total_write = @total_write,
			total_errors = @total_errors
end

/*
**  Now print out old and new monitor values.
*/
set nocount on
select @interval = datediff(ss, lastrun, @now)
	from master.dbo.spt_monitor
/* To prevent a divide by zero error when run for the first
** time after boot up
*/
if @interval = 0
	select @interval = 1
select last_run = lastrun, current_run = @now, seconds = @interval
	from master.dbo.spt_monitor

select
	cpu_busy = substring(convert(varchar(11),
		convert(int, ((@cpu_busy * @mspertick) / 1000)))
		+ '('
		+ convert(varchar(11), convert(int, (((@cpu_busy - cpu_busy)
		* @mspertick) / 1000)))
		+ ')'
		+ '-'
		+ convert(varchar(11), convert(int, ((((@cpu_busy - cpu_busy)
		* @mspertick) / 1000) * 100) / @interval))
		+ '%',
		1, 25),
	io_busy = substring(convert(varchar(11),
		convert(int, ((@io_busy * @mspertick) / 1000)))
		+ '('
		+ convert(varchar(11), convert(int, (((@io_busy - io_busy)
		* @mspertick) / 1000)))
		+ ')'
		+ '-'
		+ convert(varchar(11), convert(int, ((((@io_busy - io_busy)
		* @mspertick) / 1000) * 100) / @interval))
		+ '%',
		1, 25),
	idle = substring(convert(varchar(11),
        convert(int, ((convert(bigint,@idle) * @mspertick) / 1000)))
		+ '('
		+ convert(varchar(11), convert(int, (((@idle - idle)
		* @mspertick) / 1000)))
		+ ')'
		+ '-'
		+ convert(varchar(11), convert(int, ((((@idle - idle)
		* @mspertick) / 1000) * 100) / @interval))
		+ '%',
		1, 25)
from master.dbo.spt_monitor

select
	packets_received = substring(convert(varchar(11), @pack_received) + '(' +
		convert(varchar(11), @pack_received - pack_received) + ')', 1, 25),
	packets_sent = substring(convert(varchar(11), @pack_sent) + '(' +
		convert(varchar(11), @pack_sent - pack_sent) + ')', 1, 25),
	packet_errors = substring(convert(varchar(11), @pack_errors) + '(' +
		convert(varchar(11), @pack_errors - pack_errors) + ')', 1, 25)
from master.dbo.spt_monitor

select
	total_read = substring(convert(varchar(11), @total_read) + '(' +
		convert(varchar(11), @total_read - total_read) + ')', 1, 19),
	total_write = substring(convert(varchar(11), @total_write) + '(' +
		convert(varchar(11), @total_write - total_write) + ')', 1, 19),
	total_errors = substring(convert(varchar(11), @total_errors) + '(' +
		convert(varchar(11), @total_errors - total_errors) + ')', 1, 19),
	connections = substring(convert(varchar(11), @connections) + '(' +
		convert(varchar(11), @connections - connections) + ')', 1, 18)
from master.dbo.spt_monitor

/*
**  Now update spt_monitor
*/
update master.dbo.spt_monitor
	set
		lastrun = @now,
		cpu_busy = @cpu_busy,
		io_busy = @io_busy,
		idle = @idle,
		pack_received = @pack_received,
		pack_sent = @pack_sent,
		connections = @connections,
		pack_errors = @pack_errors,
		total_read = @total_read,
		total_write = @total_write,
		total_errors = @total_errors

return (0) -- sp_monitor
go

raiserror(15339,-1,-1,'sp_processmail')
go
create procedure sp_processmail --- 1996/06/19 17:30
	@subject varchar(255)=NULL,
	@filetype varchar(3)='txt',
	@separator varchar(3)='tab',
	@set_user varchar(132)='guest',
	@dbuse varchar(132)='master'
as

declare @status int
declare @msg_id varchar(94)
declare @originator varchar(255)
declare @cc_list varchar(255)
declare @msgsubject varchar(255)
declare @query varchar(8000)
declare @messages int
declare @mapifailure int
declare @resultmsg varchar(80)
declare @filename varchar(12)
declare @current_msg varchar(94)

select @messages=0
select @mapifailure=0

if @separator='tab' select @separator=CHAR(9)

/* get first message id */
exec @status = master.dbo.xp_findnextmsg
		@msg_id=@msg_id output,
		@unread_only='true'

if @status <> 0
	select @mapifailure=1

while (@mapifailure=0)
  begin

    if @msg_id is null break
    if @msg_id = '' break

    exec @status = master.dbo.xp_readmail
		@msg_id=@msg_id,
		@originator=@originator output,
		@cc_list=@cc_list output,
		@subject=@msgsubject output,
		@message=@query output,
		@peek='true',

		@suppress_attach='true'

    if @status <> 0
	begin
		select @mapifailure=1
		break
	end

    /* get new message id before processing & deleting current */
	select @current_msg=@msg_id
	exec @status = master.dbo.xp_findnextmsg
		@msg_id=@msg_id output,
		@unread_only='true'

    	if @status <> 0
	begin
		select @mapifailure=1
	end


    if ((@subject IS NULL) OR (@subject=@msgsubject))
    begin
	/* generate random filename */
	select @filename='SQL' + convert(varchar,ROUND(RAND()*100000,0)) + '.' + @filetype

	exec @status = master.dbo.xp_sendmail
			@recipients=@originator,
			@copy_recipients=@cc_list,
			@message=@query,
			@query=@query,
			@subject='Query Results',
			@separator=@separator,
			@width=256,
			@attachments=@filename,
			@attach_results='true',
			@no_output='false',
			@echo_error='true',
			@set_user=@set_user,
			@dbuse=@dbuse

	if @status <> 0
		begin
			select @mapifailure=1
			break
		end

	select @messages=@messages+1

	exec master.dbo.xp_deletemail @current_msg

    end /* end of xp_sendmail block */
  end  /* end of xp_findnextmsg loop */

  /* finished examining the contents of inbox;  now send results */
  if @mapifailure=1
      	begin
		raiserror(15079,-1,-1,@messages)
		return(1)
	end
  else
	return(0)
-- sp_processmail
go


raiserror(15339,-1,-1,'sp_recompile')
go
create procedure sp_recompile
    @objname	 	nvarchar(776)
as
    -- do sets and declares
    Set nocount on
    declare @objid      int,
            @curdbname  sysname

    -- CHECK VALIDITY OF OBJECT NAME --
    --  (1) Must exist in current database
    --  (2) Must be a table or an executable object
    select @objid = object_id(@objname, 'local')
    if @objid is null OR
        (ObjectProperty(@objid, 'IsTable') = 0 AND
         ObjectProperty(@objid, 'IsExecuted') = 0)
    begin
	    select @curdbname = db_name()
	    raiserror(15009,-1,-1 ,@objname, @curdbname)
	    return @@error
    end

    -- CHECK PERMISSION --
    if (is_member('db_owner') = 0) AND (is_member('db_ddladmin') = 0)
        AND (is_member(user_name(ObjectProperty(@objid, 'ownerid'))) = 0)
    begin
        raiserror(15247,-1,-1)
        return @@error
    end

    -- BUMP SCHEMA FOR RECOMPILE --
	DBCC LockObjectSchema(@objname)
    if @@error <> 0
        return (1)

	-- TH-TH-TH-THAT'S IT!
    raiserror(15070,-1,-1,@objname)
	return (0) -- sp_recompile
go

checkpoint
go

raiserror(15339,-1,-1,'sp_remoteoption')
go
create procedure sp_remoteoption --- 1996/04/08 00:00
	@remoteserver sysname = NULL,	/* server name to change */
	@loginame sysname = NULL,		/* user's remote name */
	@remotename sysname = NULL,		/* user's local user name */
	@optname varchar(35) = NULL,		/* option name to turn on/off */
	@optvalue varchar(10) = NULL		/* true or false */
as
	declare @optcount int			/* number of options like @optname */
	declare @sid	varbinary(85)

	-- NO SERVER NAME? SHOW SETTABLE OPTION ('trusted')
	if @remoteserver is null
	begin
		raiserror(15473,-1,-1)
		select remotelogin_option = name from master.dbo.spt_values
			where type = 'F_U' and number = 16
		return (0)
	end

	-- NO USER XACT --
	set implicit_transactions off
	if @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sp_remoteoption')
		return (1)
	end

	-- PERMISSIONS --
	if not (is_srvrolemember('securityadmin') = 1)
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

	-- VALIDATE SERVER NAME --
	declare @srvid smallint
	select @srvid = srvid from master.dbo.sysservers where srvname = @remoteserver
	if @srvid is null
	begin
		raiserror(15015,-1,-1,@remoteserver)
		return (1)
	end

	-- VALIDATE @loginame --
	if @loginame is not null
	begin
		select @sid = sid from master.dbo.syslogins where loginname = @loginame
					AND isntname = 0        -- cannot remap to NT login
		if @sid is null
		begin
			raiserror(15067,-1,-1,@loginame)
			return (1)
		end
	end

	-- VALIDATE <@sid, @remotename> PAIR FOR @srvid --
	if not exists (select * from master.dbo.sysxlogins where srvid = @srvid
				and ((@remotename is null AND name is null) OR name = @remotename)
				and ((@sid is null AND sid is null) OR sid = @sid))
	begin
		raiserror(15185,-1,-1,@remotename,@loginame,@remoteserver)
		return (1)
	end

	-- Check remaining parameters --
	if @optname is NULL or lower(@optvalue) not in ('true', 'false') or @optvalue is null
	begin
		raiserror(15220,-1,-1)
		return (1)
	end

	-- SEE IF @optname MATCHES THE 'trusted' OPTION --
	if not exists (select * from master.dbo.spt_values where name like '%' + @optname + '%'
			and type = 'F_U' and number = 16)
	begin
		raiserror(15221,-1,-1)
		return (1)
	end

	-- Now update sysremotelogins
	if lower(@optvalue) = 'true'
	begin
		update master.dbo.sysxlogins set xstatus = xstatus | 16, xdate1 = getdate()
			where srvid = @srvid
				and ((@remotename is null AND name is null) OR name = @remotename)
				and ((@sid is null AND sid is null) OR sid = @sid)
	end
	else	-- 'false'
	begin
		update master.dbo.sysxlogins set xstatus = xstatus & ~16, xdate1 = getdate()
			where srvid = @srvid
				and ((@remotename is null AND name is null) OR name = @remotename)
				and ((@sid is null AND sid is null) OR sid = @sid)
	end
	return (0) -- sp_remoteoption
go

checkpoint
go

raiserror(15339,-1,-1,'sp_invalidate_textptr')
go
create procedure sp_invalidate_textptr
	@TextPtrValue      varbinary(16) = 0x00
as
	dbcc invalidate_textptr(@TextPtrValue)
	return (0); -- sp_invalidate_textptr
go

raiserror(15339,-1,-1,'sp_tableoption')
go
create procedure sp_tableoption
    @TableNamePattern      nvarchar(776)
   ,@OptionName            varchar(35)
   ,@OptionValue           varchar(12)
as
	-- DECLARE AND INIT VARIABLES
	DECLARE @OPTpintable varchar(25)
			,@OPTbulklock varchar(25)
			,@OPTtextinrow varchar(25)
			,@CurrentDBId int
			,@TabId int
			,@intOptionValue int
			,@uid int
	SELECT @OPTpintable = 'pintable'
			,@OPTbulklock = 'table lock on bulk load'
			,@OPTtextinrow = 'text in row'
			,@CurrentDBId = db_id()

    -- DISALLOW USER TRANSACTION (except for in 'text in row') --
	Set nocount on
	set implicit_transactions off
	IF (@@trancount > 0 AND lower(@OptionName) <> @OPTtextinrow)
	begin
		raiserror(15002,-1,-1,'sp_tableoption')
		RETURN @@ERROR
	end

	-- VALIDATE OPTION VALUE
	SELECT @intOptionValue =
		CASE WHEN (lower(@OptionValue) in ('1' ,'on' ,'yes' ,'true')) THEN 1
			WHEN (lower(@OptionValue) in ('0' ,'off' ,'no' ,'false')) THEN 0
			WHEN (lower(@OptionName) = @OPTtextinrow AND ISNUMERIC (@OptionValue) <> 0)
			THEN convert (int, @OptionValue)
		ELSE NULL END

	-- ERROR IF INVALID OPTION NAME OR VALUE
	IF @intOptionValue IS NULL OR
		(lower(@OptionName) NOT IN (@OPTpintable, @OPTbulklock, @OPTtextinrow))
	begin
		raiserror(15600,-1,-1, 'sp_tableoption')
		RETURN @@ERROR
	end

	-- VERIFY WE HAVE A USER-TABLE BY THIS NAME IN THE DATABASE
	SELECT @TabId = id, @uid = uid FROM sysobjects
		WHERE id = OBJECT_ID(@TableNamePattern, 'local') AND xtype = 'U'
	IF @TabId IS NULL
	begin
		raiserror(15388,-1,-1,@TableNamePattern)
		RETURN @@ERROR
	end

	-- Check standard Table-DDL permissions
	IF not (is_member('db_owner') = 1) and
		not (is_member('db_ddladmin') = 1) and
		not (is_member(user_name(@uid)) = 1)
	begin
		raiserror(15247,-1,-1)
		RETURN @@ERROR
	end

	-- HANDLE TEXT-IN-ROW option
	IF (lower(@OptionName) = @OPTtextinrow)
	begin
		-- Set according to value given (Note: dbcc no_textptr does proper schema-locking)
		if (@intOptionValue != 0 and @intOptionValue != 1 and
			(@intOptionValue < 24 or @intOptionValue > 7000))
		BEGIN	-- Invalid value
			raiserror (15112,-1,-1)
			RETURN @@ERROR
		END

		-- invalidate inrow text pointer for the table
		--
		dbcc invalidate_textptr_objid(@TabId)

		BEGIN TRAN
		DBCC LOCKOBJECTSCHEMA(@TableNamePattern)
		dbcc no_textptr(@TabId, @intOptionValue)
		COMMIT TRAN
	end

	-- HANDLE TABLOCK-ON-BCP option
	ELSE IF (lower(@OptionName) = @OPTbulklock)
	BEGIN
		-- Make required change
		IF ObjectProperty(@TabId, 'TableIsLockedOnBulkLoad') <> @intOptionValue
		BEGIN
			BEGIN TRAN
			DBCC LOCKOBJECTSCHEMA(@TableNamePattern)
			UPDATE sysobjects SET status = (status & ~134217728) | (134217728 * @intOptionValue)
				WHERE id = @TabId
			COMMIT TRAN
		END
	END

	-- HANDLE PIN-TABLE option
	ELSE IF (lower(@OptionName) = @OPTpintable)
	BEGIN
		-- ADDITIONAL SECURITY: Must be sysadmin to pin pages
		IF (not (is_srvrolemember('sysadmin') = 1))
		begin
			raiserror(15247,-1,-1)
			RETURN @@ERROR
		end

		-- Make change if required
		IF ObjectProperty(@TabId, 'TableIsPinned') <> @intOptionValue
		BEGIN
			IF @intOptionValue = 1
				DBCC pintable(@CurrentDBId, @TabId)
			ELSE
				DBCC unpintable(@CurrentDBId, @TabId)
		END
	END

	-- Return success
	Return 0  --sp_tableoption
go

checkpoint
go

raiserror(15339,-1,-1,'sp_procoption')
go
create procedure sp_procoption
    @ProcName		nvarchar(776)
   ,@OptionName		varchar(35)
   ,@OptionValue	varchar(12)
as
	-- DECLARE VARIABLES
	DECLARE @tabid  int
			,@uid int
			,@intOptionValue  int
			,@dbname sysname

    -- DISALLOW USER TRANSACTION --
	Set nocount on
	set implicit_transactions off
	IF @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sp_procoption')
		RETURN @@ERROR
	end

	-- VALIDATE OPTION NAME AND VALUE
	SELECT @intOptionValue =
		CASE WHEN (lower(@OptionValue) in ('1' ,'on' ,'yes' ,'true')) THEN 1
			WHEN (lower(@OptionValue) in ('0' ,'off' ,'no' ,'false')) THEN 0
		ELSE NULL END
	IF @intOptionValue IS NULL OR @OptionName IS NULL OR lower(@OptionName) <> 'startup'
	BEGIN
		raiserror(15600,-1,-1, 'sp_procoption')
		RETURN @@ERROR
	END

	-- MUST BE sysadmin (Startup-procs run as sysadmin) --
	IF is_srvrolemember('sysadmin') = 0
	BEGIN
		raiserror(15247,-1,-1)
		RETURN @@ERROR
	END

	-- RESOLVE GIVEN OBJECT NAME --
	SELECT @tabid = id, @uid = uid FROM sysobjects
		WHERE id = OBJECT_ID(@ProcName, 'local') AND xtype IN ('X','P')

	-- VALID OBJECT IN DATABASE? --
	IF @tabid IS NULL
	BEGIN
		SELECT @dbname = db_name()
		raiserror(15009,-1,-1 ,@ProcName, @dbname)
		RETURN @@ERROR
	END

	-- STARTUP PROC MUST BE OWNED BY DBO IN MASTER --
	IF (db_id() <> 1 OR @uid <> 1)
	BEGIN
		raiserror(15398,-1,-1)
		RETURN @@ERROR
	END

	-- PROC CANNOT HAVE PARAMETERS --
	IF EXISTS ( SELECT * FROM syscolumns WHERE id = @tabid )
	BEGIN
		raiserror(15399,-1, -1)
		RETURN @@ERROR
	END

	-- Do the work
	BEGIN TRAN
	DBCC LockObjectSchema(@ProcName)
	UPDATE sysobjects SET status = (status & ~2) | (2 * @intOptionValue) WHERE id = @tabid

	-- Set Config option for startup procs
	UPDATE master.dbo.sysconfigures SET value =
			CASE WHEN EXISTS (SELECT * FROM sysobjects WHERE xtype IN ('X','P')
				AND ObjectProperty(id, 'ExecIsStartup') = 1)
			THEN 1 ELSE 0 END
		WHERE config = 1547

	-- If no error, commit and reconfigure
	IF (@@error <> 0)
	BEGIN
		ROLLBACK TRAN
		RETURN 1
	END
	COMMIT TRAN
	RECONFIGURE WITH OVERRIDE

	-- RETURN SUCCESS
	RETURN 0 -- sp_procoption
go

checkpoint
go


raiserror(15339,-1,-1,'sp_renamedb')
go
create procedure sp_renamedb --- 1996/08/20 13:52
@dbname sysname,			/* old (current) db name */
@newname sysname			/* new name we want to call it */
as
-- Use sp_rename instead.
declare @objid int			/* object id of the thing to rename */
declare @bitdesc varchar(30)		/* bit description for the db */
declare @curdbid int			/* id of database to be changed */
declare @execstring nvarchar (4000)

/*
**  If we're in a transaction, disallow this since it might make recovery
**  impossible.
*/
set implicit_transactions off
if @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sp_renamedb')
		return (1)
	end

/*
**  Only the SA can do this.
*/
if not (is_srvrolemember('dbcreator') = 1)
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

/*
**  Make sure the database exists.
*/
if not exists (select * from master.dbo.sysdatabases where name = @dbname)
	begin
		raiserror(15010,-1,-1,@dbname)
		return (1)
	end

/*
**  Make sure that the @newname db doesn't already exist.
*/
if exists (select * from master.dbo.sysdatabases where name = @newname)
	begin
		raiserror(15032,-1,-1,@newname)
		return (1)
	end

/*
**  Check to see that the @newname is valid.
*/
declare @returncode int
exec @returncode = sp_validname @newname
if @returncode <> 0
begin
	raiserror(15224,-1,15,@newname)
	return(1)
end

/*
**  Don't allow the names of master, tempdb, and model to be changed.
*/
if @dbname in ('master', 'model', 'tempdb')
	begin
		raiserror(15227,-1,-1,@dbname)
		return (1)
	end


	select @execstring = 'ALTER DATABASE '
		+ quotename( @dbname , '[')
		+ ' MODIFY NAME = '
		+ quotename( @newname , '[')

	exec (@execstring)

if @@error <>  0
	begin
		-- No need to raiserror as the CREATE DATABASE will do so
		return(1)
	end

return (0) -- sp_renamedb
go

raiserror(15339,-1,-1,'sp_remove_tempdb_file')
go
create procedure sp_remove_tempdb_file @filename sysname
as
declare @fileid smallint
set nocount on

select @fileid = fileid
	from sysaltfiles
	where dbid = 2  -- limit to tempdb files
	and name = @filename
if @fileid is null
begin
	-- file name does not exist
	raiserror(15311,-1,-1,@filename)
	return (1)
end
if @fileid < 3
begin
	-- file is one of the primary files
	raiserror(15312,-1,-1,@filename)
	return (1)
end

delete sysaltfiles where dbid = 2 and fileid = @fileid
if @@error>0
   begin
      raiserror(15321,-1,-1, @filename)
      return (1)
   end
else
   begin
      raiserror(15322,-1,-1, @filename)
      return (0)
   end

go


raiserror(15339,-1,-1,'sp_rename')
go
/*ANSI_NULLS on for  creation of sp_rename*/
Set ansi_nulls on
go
CREATE PROCEDURE sp_rename
	@objname	nvarchar(776),		-- up to 3-part "old" name
	@newname	sysname,			-- one-part new name
	@objtype	varchar(13) = null	-- identifying the name
as
/********1*********2*********3*********4*********5**
DOCUMENTATION:
   [1]  To rename a table, the @objname (meaning OldName) parm can be
passed in totally unqualified or fully qualified.
   [2]  The SA or DBO can rename objects owned by lesser users,
without the need for SetUser.
   [3]  The Owner portion of a qualified name can usually be
passed in in the omitted form (as in MyDb..MyTab or MyTab).  The
typical exception is when the SA/DBO is trying to rename a table
where the @objname is present twice in sysobjects as a table
owned only by two different lesser users; requiring an explicit
owner qualifier in @objname.
   [4]  An unspecified Owner qualifier will default to the
current user if doing so will either resolve what would
otherwise be an ambiguity within @objtype, or will result
in exactly one match.
   [5]  If Database is part of the qualified @objname,
then it must match the current database.  The @newname parm can
never be qualified.
   [6]  Here are the valid @objtype values.  They correspond to
system tables which track each type:
      'column'  'database'  'index'  'object'  'userdatatype'
The @objtype parm is sometimes required.  It is always required
for databases.  It is required whenever ambiguities would
otherwise exist.  Explicit use of @objtype is always encouraged.
   [7]  Parms can use quoted_identifiers.  For example:
   Execute sp_rename 'amy."his table"','"her table"','object'
*********1*********2*********3*********4*********5*/
Set nocount      on
Set ansi_padding on

Declare @objtypeIN		varchar(13),
		@ExecRC			integer,
		@CurrentDb		sysname,
		@CountNumNodes	integer,
		@UnqualOldName	sysname,
		@QualName1		sysname,
		@QualName2		sysname,
		@QualName3		sysname,
		@OwnAndObjName	nvarchar(517),	-- "[owner].[object]"
		@objid			integer,
		@xtype			nchar(2),
		@indid			smallint,
		@colid			smallint,
		@cnstid			integer,
		@parent_obj		integer,
		@xusertype		smallint,
		@ownerid		smallint,
		@objid_tmp		integer,
		@xtype_tmp		nchar(2),
		@retcode		int,
		@replinfo		int,
		@replbits		int
-- initial (non-null) settings
Select	@CurrentDb		= db_name(),
		@objtypeIN		= @objtype,
		@replbits		= 129	--Indicates table is used in replication

-- make type case insensitive
select @objtype = lower(@objtypeIN)

------------------------------------------------------------------------
-------------------  PHASE 10:  Simple parm edits  ---------------------
------------------------------------------------------------------------

-- Valid rename-type param?
IF (@objtype is not null AND
	@objtype not in ('column', 'database', 'index', 'object', 'userdatatype'))
begin
	raiserror(15249,-1,-1,@objtypeIN,0)
	return 1
end
-- null names?
IF (@newname IS null)
begin
	raiserror(15223,-1,11,'NewName')
	return 1
end
if (@objname IS null)
begin
	raiserror(15223,-1,-1,'OldName')
	return 1
end

---------------  Is NewName minimally valid?

--Check for valid rename name
exec @retcode = sp_validname @newname
if @retcode <> 0
begin
	raiserror(15224,-1,15,@newname)
	return 1
end

-------- Parse apart the perhaps dots-qualified old name.

select @UnqualOldName = parsename(@objname, 1),
        @QualName1 = parsename(@objname, 2),
        @QualName2 = parsename(@objname, 3),
        @QualName3 = parsename(@objname, 4)
IF (@UnqualOldName IS Null)
begin
	raiserror(15253,-1,-1,@objname)
	return 1
end

-- count name parts --
select @CountNumNodes = CASE WHEN @QualName3 IS NOT NULL THEN 4
                             WHEN @QualName2 IS NOT NULL THEN 3
                             WHEN @QualName1 IS NOT NULL THEN 2
                             ELSE 1 END
IF (@objtype  = 'database' AND @CountNumNodes > 1)
begin
	Raiserror(15395,-1,20,@objtypeIN)
	return 1
end
if (@objtype in ('object','userdatatype') AND @CountNumNodes > 3)
begin
	raiserror(15225,-1,-1,@objname, @CurrentDb, @objtypeIN)
	return 1
end


---------------------------------------------------------------------------
----------------------  PHASE 20:  Settle Parm1ItemType  ------------------
---------------------------------------------------------------------------

------------- database?
IF (@objtype  = 'database')
begin
	execute @ExecRC = sp_renamedb @UnqualOldName ,@newname -- de-docu old sproc
	IF @ExecRC <> 0
		return 1
	GOTO LABEL_51_AFTERUPDATES
end

-- assuming column/index-name, obtain object/column id's
if @QualName2 is not null
	select @objid = object_id(QuoteName(@QualName2) +'.'+ QuoteName(@QualName1))
else
	select @objid = object_id(QuoteName(@QualName1))

select @xtype = xtype, @replinfo = replinfo from sysobjects where id = @objid

------------ column?
if (@objtype = 'column' or @objtypeIN is null)
begin
	-- find column
	select @colid = NULL
	if (@xtype in ('U','V'))
		select @colid = colid from syscolumns
				where id = @objid and name = @UnqualOldName

	-- check for wrong param
	if ((@colid is not null AND @objtype <> 'column') OR
		(@colid is null AND @objtype = 'column'))
	begin
		raiserror(15248,-1,-1,@objtypeIN)
		return 1
	end

	-- remember if we've found a column
	IF (@colid is not null)
	begin
		if (@replinfo & @replbits <> 0)
			begin
				raiserror(15051,-1,-1)
				return (0)
			end
		select @objtype = 'column'
	end
end

------------ index?
if (lower(@objtype) = 'index' or @objtypeIN is null)
begin
	-- find index
	if (@xtype in ('U','V'))
		select @indid = indid from sysindexes
				where id = @objid and name = @UnqualOldName
					AND indid NOT IN (0, 255)

	-- check for wrong param
	if ((@indid is not null AND @objtype <> 'index') OR
		(@indid is null AND @objtype = 'index'))
	begin
		raiserror(15248,-1,-1,@objtypeIN)
		return 1
	end

	if (@indid is not null)
	begin
		select @objtype = 'index'
		select @cnstid = id, @xtype = xtype from sysobjects
			where name = @UnqualOldName AND parent_obj = @objid and xtype in ('PK','UQ')
	end
end

------------ object?
if (@objtype = 'object' or @objtypeIN is null)
begin
	-- get object id, type
	select @objid_tmp = object_id(@objname)

	select @xtype_tmp = xtype, @replinfo = replinfo
	from sysobjects where id = @objid_tmp

	-- if object is a system table, a Scalar function, or a table valued function, skip it.

	-- Cannot rename system table
	if @xtype_tmp = 'S'
		select @objid_tmp = NULL

	-- check for wrong param
	if ((@objid_tmp is not null AND @objtype <> 'object') OR
		(@objid_tmp is null AND @objtype = 'object'))
	begin
		raiserror(15248,-1,-1,@objtypeIN)
		return 1
	end

	if (@objid_tmp is not null)
	begin

		if (@xtype_tmp in ('U'))
		begin
			if (@replinfo & @replbits <> 0)
			begin
				raiserror(15051,-1,-1)
				return (0)
			end
		end

		select @objtype = 'object', @objid = @objid_tmp, @xtype = @xtype_tmp

		if (@xtype in ('PK','UQ'))
			select @parent_obj = parent_obj from sysobjects where id = @objid
	end
end


------------ type?
if (@objtype = 'userdatatype' or @objtypeIN is null)
begin
	select @xusertype = xusertype from systypes
		where name = @UnqualOldName and xusertype > 256
			AND (@QualName1 is null or uid = user_id(@QualName1))

	-- check for wrong param
	if ((@xusertype is not null AND @objtype <> 'userdatatype') OR
		(@xusertype is null AND @objtype = 'userdatatype'))
	begin
		raiserror(15248,-1,-1,@objtypeIN)
		return 1
	end

	if (@xusertype IS NOT null)
		select @objtype = 'userdatatype'
end

---------------------------------------------------------------------
-------------------  PHASE 30:  More parm edits  --------------------
---------------------------------------------------------------------

-- item type determined?
if (@objtype IS null)
begin
	raiserror(15225,-1,-1,@objname, @CurrentDb, @objtypeIN)
	return 1
end

-- was the original name valid given this type?
if (@objtype in ('object','userdatatype') AND @CountNumNodes > 3)
begin
	raiserror(15225,-1,-1,@objname, @CurrentDb, @objtypeIN)
	return 1
end

-- verify db qualifier is current db
if (@objtype in ('object','userdatatype'))
	select @QualName3 = @QualName2
if (isnull(@QualName3, @CurrentDb) <> @CurrentDb)
begin
	raiserror(15333,-1,-1,@QualName3)
	return 1
end

-- get owner id and check permissions
if (@objtype = 'userdatatype')
	select @ownerid = uid from systypes where xusertype = @xusertype
else
	select @ownerid = ObjectProperty(@objid, 'ownerid')
if (	(not (1 = is_member('db_owner')))
	AND (not (1 = is_member('db_ddladmin')))
	AND (not (1 = is_member(user_name(@ownerid)))) )
begin
	raiserror(15247,-1,-1)
	return 1
end

-- check if system object
if (ObjectProperty(@objid, 'IsMSShipped') = 1 OR
	ObjectProperty(@objid, 'IsSystemTable') = 1)
begin
	raiserror(15001,-1,-1, @objname)
	return 1
end

-- make sure orig no longer shows null
if @objtypeIN is null
	select @objtypeIN = @objtype

-- Check for name clashing with existing name(s)
if (@newname <> @UnqualOldName)
begin
	-- column name clash?
	if (@objtype = 'column')
		if (ColumnProperty(@objid, @newname, 'isidentity') is not null)
			select @UnqualOldName = NULL
	-- object name clash?
	if ( (@objtype = 'object' AND @xtype in ('PK','UQ'))
			OR @objtype = 'index')
		if exists (select * from sysindexes where id = @objid and name = @newname
					and indid not in (0,255))
			select @UnqualOldName = NULL
	-- index name clash?
	if (@objtype = 'object' OR @cnstid IS NOT null)
		if (object_id(QuoteName(user_name(@ownerid)) +'.'+ QuoteName(@newname)) is not null)
			select @UnqualOldName = NULL
	-- type name clash?
	if (@objtype = 'userdatatype')
		if exists (select * from systypes where name = @newname)
			select @UnqualOldName = NULL
	-- stop on clash
	if (@UnqualOldName is null)
	begin
		raiserror(15335,-1,-1,@newname,@objtypeIN)
		return 1
	end
end

--------------------------------------------------------------------------
--------------------  PHASE 32:  Temporay Table Isssue -------------------
--------------------------------------------------------------------------
-- Disallow renaming object to or from a temp name (starts with #)
if (@objtype = 'object' AND
	(substring(@newname,1,1) = N'#' OR
	substring(object_name(@objid),1,1) = N'#'))
begin
	raiserror(15600,-1,-1, 'sp_rename')
	return 1
end

--------------------------------------------------------------------------
--------------------  PHASE 34:  Cautionary messages  --------------------
--------------------------------------------------------------------------

if @objtype = 'column'
begin
	-- Check for Dependencies: No column rename if enforced dependency on column
	IF EXISTS (SELECT * FROM sysdepends WHERE depid = @objid AND depnumber = @colid AND deptype > 0)
	begin
		raiserror(15336,-1,-1, @objname)
		return 1
	end
end
else if @objtype = 'object'
begin
	-- Check for Dependencies: No RENAME or CHANGEOWNER of OBJECT when exists:
	IF EXISTS (SELECT * FROM sysdepends d WHERE
		d.depid = @objid		-- A dependency on this object
		AND d.deptype > 0		-- that is enforced
		AND @objid <> d.id		-- that isn't a self-reference (self-references don't use object name)
		AND @objid <>			-- And isn't a reference from a child object (also don't use object name)
			(SELECT o.parent_obj FROM sysobjects o WHERE o.id = d.id)
		)
	begin
		raiserror(15336,-1,-1, @objname)
		return 1
	end
end

-- WITH DEFERRED RESOLUTION, SYSDEPENDS IS NOT VERY ACCURATE, SO WE ALSO
--	RAISE THIS WARNING **UNCONDITIONALLY**, EVEN FOR NON-OBJECT RENAMES
raiserror(15477,-1,-1)

-- warn about dependencies...
if (@objtype = 'objects' and exists (select * from sysdepends where depid = @objid))
	raiserror(15337,-1,-1)

--------------------------------------------------------------------------
---------------------  PHASE 40:  Update system tables  ------------------
--------------------------------------------------------------------------

-- obtain owner-qual object name (for most below)
select @OwnAndObjName = QuoteName(user_name(@ownerid))+'.'+QuoteName(object_name(@objid))

-- DO THE UPDATES --
if (@objtype = 'userdatatype')						-------- change type name
	UPDATE systypes set name = @newname where xusertype = @xusertype
else if (@objtype = 'object')						-------- change object name
begin
	BEGIN  TRANSACTION
	-- Locks Object and increments schema_ver
	DBCC LockObjectSchema(@OwnAndObjName)
	-- update the object name
	UPDATE sysobjects set name = @newname where id = @objid
	-- update index-cnst name (no rows changed if not 'PK' or 'UQ')
	if (@xtype in ('PK','UQ'))
		UPDATE sysindexes set name = @newname where id = @parent_obj and name = @UnqualOldName
	-- update base/text index name (no rows changed if not there)
	else if (@xtype in ('U', 'TF'))
	begin
		UPDATE sysindexes set name = @newname where id = @objid AND indid = 0
		UPDATE sysindexes set name = convert(sysname,'t'+@newname)
							where id = @objid AND indid = 255
	end
	COMMIT TRANSACTION
end
else if (@objtype = 'index')						-------- change index name
begin
	BEGIN  TRANSACTION
	-- Locks Object and increments schema_ver.
	DBCC LockObjectSchema(@OwnAndObjName)
	-- update the index name
	UPDATE sysindexes set name = @newname where id = @objid and indid = @indid
	-- change object name if cnst
	if (@cnstid IS NOT null)
		UPDATE sysobjects set name = @newname where id = @cnstid
	COMMIT TRANSACTION
end
else if (@objtype = 'column')						-------- change column name
begin
	-- Use DBCC to check for column in use by check-constraint, computed-column, etc
	-- THIS IS NOT A DOCUMENTED DBCC: DO NOT USE DIRECTLY!
	DBCC RENAMECOLUMN ( @OwnAndObjName, @UnqualOldName, @newname )
end


-------------------------  Finalization  -----------------------
LABEL_51_AFTERUPDATES:
Raiserror(15338,-1,-1,@objtypeIN,@newname)
return 0 -- sp_rename
go
/*ANSI_NULLS off for after creation of sp_rename*/
Set ansi_nulls off
go



checkpoint
go

raiserror(15339,-1,-1,'sp_resetstatus')
Go

CREATE PROCEDURE sp_resetstatus  -- 1995/11/30 14:12 #12092
       @DBName          sysname
as

Set nocount on

Declare
       @msg             nvarchar(280)
      ,@RetCode         integer
      ,@_error          integer
      ,@_rowcount       integer
      ,@int1            integer
      ,@bitSuspect      integer
      ,@mode            integer
      ,@status          integer

Select
       @RetCode         = 0  -- 0=no_problem, 1=some_problem

---------------------  Restrict to SA  -------------------------

if (not (is_srvrolemember('sysadmin') = 1))
   begin
   RaisError(15247,-1,-1)
   Select @RetCode = 1
   GOTO LABEL_86BYEBYE
   end


------------------  Get SuspectBit id value  ------------------

SELECT       @bitSuspect = min(number)
      from
             master..spt_values
      where  type = 'D  '
      and    name = 'not recovered'  -- 256, Suspect

----------------------  Forbid active txn  ---------------------

--- (Prior spt_values Sel trips SET implicit_transactions!)


IF @@trancount > 0
   begin
   RaisError(15002,-1,-1,'sp_resetstatus')
   Select @RetCode = 1
   GOTO LABEL_86BYEBYE
   end


---------------  Obtain/Report pre-Update values  --------------------

SELECT
             @mode   = min(mode)
            ,@status = min(status)
      from
             master..sysdatabases
      where  name = @DBName

IF @@error <> 0 OR @status IS Null
   begin
   RaisError(15010,-1,-1,@DBName)
   Select @RetCode = 1
   GOTO LABEL_86BYEBYE
   end


Select @int1 = @status & @bitSuspect


Raiserror(15052,-1,-1 ,@DBName ,@mode ,@status ,@int1)

---------------------  Update sysdatabases row  ---------------------

BEGIN TRANSACTION


UPDATE
             master..sysdatabases
      set
             mode    = 0
            ,status  = status & (~ @bitSuspect)
      where  name    = @DBName
      and
            (mode   <> 0      OR
             status  & @bitSuspect > 0
            )

Select @_error = @@error ,@_rowcount = @@rowcount


IF @_error <> 0
   begin

   ROLLBACK TRANSACTION

   RaisError(15055,-1,-1)
   Select @RetCode = 1
   GOTO LABEL_86BYEBYE
   end


COMMIT TRANSACTION

-------- Report the results

IF @_rowcount = 0
   begin
   Raiserror(15056,-1,-1)
   end

ELSE
   begin

   Raiserror(15073,-1,-1, @DBName,@bitSuspect)

   Raiserror(15074,-1,-1)

   end


LABEL_86BYEBYE:

RETURN @RetCode

Go


raiserror(15339,-1,-1,'sp_add_file_recover_suspect_db')
Go
--
-- Name: sp_add_file_recover_suspect_db
-- Purpose: Adds a data or log file to a suspect database and runs
-- 		recovery on the database.  This SP should only be used
--		on databases that have been marked suspect due to
--		insufficient data (error 1105) or log (error 9002) space.
-- Note: This SP is not documented.  Only sp_add_data_file_recover_suspect_db
--	and sp_add_log_file_recover_suspect_db below are documented
--
create procedure sp_add_file_recover_suspect_db
	@dbName 	sysname			-- database name
	,@fileType	nvarchar(4)		-- "data" or "log"
	,@filegroup	nvarchar(260)		-- file group for new file
	,@name		nvarchar(260)		-- logical file name
	,@filename	nvarchar(260)		-- OS file name
	,@size		nvarchar(20) 	= NULL	-- initial file size
	,@maxsize	nvarchar(20) 	= NULL	-- maximum file size
	,@filegrowth	nvarchar(20) 	= NULL	-- growth increment

as

declare @currentStatus int
declare @suspectBit int
declare @dboOnlyBit int
declare @emergencyModeBit int
declare @returnCode int
declare @addCmd nvarchar(4000)
declare @isLog	int

set nocount on
select @suspectBit = 0x100
select @currentStatus = 0
select @returnCode = 0

---------------------  Restrict to SA  -------------------------

if (not (is_srvrolemember('sysadmin') = 1))
begin
   RaisError(15247,-1,-1)
   Select @returnCode = 1
   GOTO LABEL_FAILURE
end


------------------  Get Status Bit id values  ------------------

SELECT       @suspectBit = min(number)
      from
             master..spt_values
      where  type = 'D  '
      and    name = 'not recovered'  -- 256, Suspect

SELECT       @dboOnlyBit = min(number)
      from
             master..spt_values
      where  type = 'D  '
      and    name = 'dbo use only'  -- 2048, dbo only

SELECT       @emergencyModeBit = min(number)
      from
             master..spt_values
      where  type = 'D  '
      and    name = 'emergency mode'  -- 32768, dbo only

--print 'Bit values ' + convert(char(10), @suspectBit) + convert(char(10), @dboOnlyBit) + convert(char(10), @emergencyModeBit)

-- Determine if this is a data or log file
--
IF (UPPER (@fileType) = 'DATA')
begin
	select @isLog = 0
end
ELSE IF (UPPER (@fileType) = 'LOG')
begin
	select @isLog = 1
end
ELSE
begin
	print 'Must specify data or log file type'
	select @returnCode = 1
	GOTO LABEL_FAILURE
end



IF @@trancount > 0
begin
   RaisError(15002,-1,-1,'sp_add_file_recover_suspect_db')
   Select @returnCode = 1
   GOTO LABEL_FAILURE
end

-- check that current status includes suspect or emergency-mode
-- otherwise fail with database does not exist
--
select @currentStatus = status from master.dbo.sysdatabases where name = @dbName

if (@currentStatus is null)
begin
	RaisError(15010,-1,-1,@dbName)
	select @returnCode = 1
	goto LABEL_FAILURE
end

-- set new temporary status to dbo-only and emergency-mode
--
BEGIN TRAN
update master.dbo.sysdatabases set status = (status | @dboOnlyBit | @emergencyModeBit) where name = @dbName
IF @@error <> 0
begin
   ROLLBACK TRANSACTION
   RaisError(15055,-1,-1)
   Select @returnCode = 1
   GOTO LABEL_FAILURE
end
COMMIT TRAN
checkpoint

-- Build the Alter Database Add File string
--
select @addCmd = 'ALTER DATABASE ' + @dbName + ' ADD'
IF (@isLog = 1)
begin
	select @addCmd = @addCmd + ' LOG FILE'
end
ELSE
begin
	select @addCmd = @addCmd + ' FILE'
end
select @addCmd = @addCmd + '(NAME = [' + @name + '], FILENAME = ''' + @filename + ''''
if (@size IS NOT NULL)
begin
	select @addCmd = @addCmd + ', SIZE = ' + @size
end
if (@maxsize IS NOT NULL)
begin
	select @addCmd = @addCmd + ', MAXSIZE = ' + @maxsize
end
if (@filegrowth IS NOT NULL)
begin
	select @addCmd = @addCmd + ', FILEGROWTH = ' + @filegrowth
end
select @addCmd = @addCmd + ' )'
if (@filegroup IS NOT NULL)
begin
	select @addCmd = @addCmd + ' TO FILEGROUP [' + @filegroup + ']'
end
print @addCmd

EXECUTE (@addCmd)

-- restore status to what it was before adding the file
--
BEGIN TRAN
update master.dbo.sysdatabases set status = @currentStatus where name = @dbName
IF @@error <> 0
   begin

   ROLLBACK TRANSACTION

   RaisError(15055,-1,-1)
   Select @returnCode = 1
   GOTO LABEL_FAILURE
   end
COMMIT TRAN
checkpoint

-- Turn off suspect bit if it is on
--
if ((@currentStatus & @suspectBit) <> 0)
begin
    exec sp_resetstatus @dbName
end

-- Run recovery on the database
--
select @addCmd = 'dbcc dbrecover (' + @dbName + ')'
exec (@addCmd)


GOTO LABEL_SUCCESS

LABEL_FAILURE:
	--print 'Failed to add file to and recover the suspect database.'
	return @returnCode

LABEL_SUCCESS:
	--print 'Successfully added file to the database'
	return @returnCode
-- sp_add_file_recover_suspect_db
go


raiserror(15339,-1,-1,'sp_add_data_file_recover_suspect_db')
Go
--
