/*------------------------------------------------------------------------------

SP4_SERV_UNI.SQL

THIS SCRIPT TAKES THE SERVER-SIDE SYSTEM-PROCS FROM 8.0SP3 to SP4.

Changes in this file are organized as follows (please maintain):
	System Tables (UPGRADE.SQL)
	System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL)
	Engine System Procs (U_TABLES.SQL / PROCSYST.SQL / ODSOLE.SQL)
	Schema Procs (OLEDBSCH.SQL / ANSIVIEW.SQL)
	ODBC/OLEDB Catalog Procs (INSTCAT.SQL)
	SEM SQLDMO System Procs (SQLDMO.SQL)

Changes to these scripts should NOT be placed in this file:
	Starfighter Procs (XPSTAR.SQL / INSTMSDB.SQL / SQLTRACE.SQL / WEB.SQL)
	Doc's Samples (INSTPUBS.SQL / INSTNWND.SQL)
	Replication Procs (REPLSYS.SQL / REPLCOM.SQL / REPLTRAN.SQL / REPLMERG.SQL)
These components will maintain separate upgrade scripts.

Notes:
+ Catalog-updates and sp_MS_upd_sysobj_category are enabled for the entire
	file.  Do not disable or re-enable them.  Please do not change set options.

------------------------------------------------------------------------------*/


--------------------------------------------------------------------------------
-- VERIFY Server is started in single-user-mode (catalog-updates enables), and
--	start marking of system-objects.
--------------------------------------------------------------------------------
execute sp_configure 'allow updates',1
go

reconfigure with override
go

exec sp_MS_upd_sysobj_category 1
go

--------------------------------------------------------------------------------
--	System Tables (UPGRADE.SQL)
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--	System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL) English
--------------------------------------------------------------------------------
DELETE sysmessages
 WHERE error in (
     801,
     954,
     955,
     1990,
     2507,
     2510,
     2778,
     2779,
     2781,
     2782,
     2783,
     2784,
     2785,
     5144, 
     5145,
     6610,
     6633,
     7987,
     8957,
     8991,
     17883,
     19016,
     20053,
     21423,
     21424,
     21426
    )
go
insert into master..sysmessages(error,severity,dlevel,description,msglangid)
	values
	(801,20,0,'Invalid buffer, status=%x.',1033)

insert into master..sysmessages(error,severity,dlevel,description,msglangid)
	values
	(954,16,0,'Database ''%.*ls'' has invalid schema.',1033)

insert into master..sysmessages(error,severity,dlevel,description,msglangid)
	values
	(955,16,0,'Database ''%.*ls'' exceeds size limit.',1033)

insert into master..sysmessages(error,severity,dlevel,description,msglangid)
	values
	(1990, 16, 0, N'Cannot define an index on a view with ignore_dup_key index option.', 1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(2507 ,16 ,0 ,'Repair statement not processed.  Database cannot be in read-only mode..' ,1033)	

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(2510, 16, 0, N'System table corrupt: Missing row in %ls for ''%ls'' is required for SQL Server operation.  For more information, see Microsoft Knowledge Base article 315523.', 1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(2778 ,16 ,0 ,'Cannot open metadata manifest resource in ''%.*ls''.' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(2779 ,16 ,0 ,'Metadata manifest ''%.*ls'' does not exist or has invalid signature.' ,1033)

insert into master..sysmessages(error,severity,dlevel,description,msglangid)
	values
	(2781,16,0,'Cannot validate database ''%.*ls''.',1033)

insert into master..sysmessages(error,severity,dlevel,description,msglangid)
	values
	(2782,16,0,'DDL statement is not allowed.',1033)

insert into master..sysmessages(error,severity,dlevel,description,msglangid)
	values
	(2783,16,0,'Cannot validate object ''%.*ls''.''%.*ls''.''%.*ls''.',1033)

insert into master..sysmessages(error,severity,dlevel,description,msglangid)
	values
	(2784,16,0,'Stored procedure ''%s'' is not allowed.',1033)

insert into master..sysmessages(error,severity,dlevel,description,msglangid)
	values
	(2785,16,0,'System database checksum file has invalid signature.',1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(5144, 10, 0, N'Autogrow of file ''%.*ls'' in database ''%.*ls'' cancelled or timed out after %d ms.  Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(5145, 10, 0, N'Autogrow of file ''%.*ls'' in database ''%.*ls'' took %d milliseconds.  Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.', 1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(6610 ,17 ,0 ,'Failed to load %ls.' ,1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(6633,16,0,'The version of MSXML3.DLL found is older than the minimum required version. Found version ''%d.%d.%d'', require version ''%d.%d.%d''.',1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(7987 ,22 ,0 ,'A possible database consistency problem has been detected on database ''%.*ls''.  DBCC CHECKDB and DBCC CHECKCATALOG should be run on database ''%.*ls''.' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(8957 ,10 ,1 ,'DBCC %ls (%ls%ls%ls) executed by %ls found %d errors and repaired %d errors.  Elapsed time: %d hours %d minutes %d seconds.' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(8991, 16, 1, '0x%p + 0x%p bytes is not a valid address range.' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
	values
	(17883, 10, 128, N'Process %1!ld!:%2!ld! (%3!lx!) UMS Context 0x%4!p! appears to be non-yielding on Scheduler %5!ld!', 1033)

insert into master..sysmessages(error,severity,dlevel,description,msglangid)
	values
	(19016, 16, 128, N'The SQL Server named pipe ''%1'' is in use by a different process and this could represent a security risk depending upon how SQL Server is used in your installation.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(20053,10,0,'An article with a different %s value already exists for object ''%s''.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(21423,10,0,'You do not have sufficient privileges to view the publication information. Your administrator may need to fix the PAL role on the publisher for publication ''%s''.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(21424,10,0,'Every SQL Server Subscriber of publication ''%s'' must be version %s or higher in order for compensation for errors to be turned off for its subscription.', 1033)

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
	values
	(21426,10,0,'The generation for a row that is being inserted/updated/deleted has already been localized. Please retry the merge synchronization process.', 1033)
go
--------------------------------------------------------------------------------
--	System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL) Localized
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--	Engine System Procs (U_TABLES.SQL / PROCSYST.SQL / ODSOLE.SQL)
--------------------------------------------------------------------------------

if object_id('sp_msupg_dosystabcatalogupgrades') is not null
	drop proc sp_msupg_dosystabcatalogupgrades
go
-- INTERNAL PROC: DO NOT DOCUMENT
create proc sp_msupg_dosystabcatalogupgrades
as
	-- 29Jan99 column level collations
	IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = 3 AND colid = 20)
		BEGIN
		BEGIN TRAN
		ALTER TABLE dbo.syscolumns ADD collationid int null 
		EXEC (N'UPDATE dbo.syscolumns SET collationid = null WHERE xtype NOT IN (35, 99, 167, 175, 231, 239)')
		COMMIT TRAN
		END
	ELSE
		BEGIN
		UPDATE dbo.syscolumns SET name = N'collationid' WHERE id = 3 AND colid = 20 AND name = 'collation'
		END

	IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = 4 AND colid = 12)
		BEGIN
		BEGIN TRAN
		ALTER TABLE dbo.systypes ADD collationid int null
		EXEC (N'UPDATE dbo.systypes SET collationid = null WHERE xtype NOT IN (35, 99, 167, 175, 231, 239)')
		COMMIT TRAN
		END
	ELSE
		BEGIN
		UPDATE dbo.syscolumns SET name = N'collationid' WHERE id = 4 AND colid = 12 and name = N'collation'
		END

	-- 18Jan99 Add sql_variant to systypes if not already present
	if not exists (select * from systypes where name = N'sql_variant')
	begin
		insert systypes (name, xtype, status, xusertype, length, xprec, xscale,
			tdefault, domain, uid, reserved)
			values (N'sql_variant', 98, 0, 98, 8016, 0, 0, 0, 0, 1, 0)
	end
	else
	begin
		update systypes set
				xtype = 98, status = 0, xusertype = 98, length = 8016,
				xprec = 0, xscale = 0, tdefault = 0, domain = 0, uid = 1,
				reserved = 0
			where name = N'sql_variant'
	end

	--20Jan99
	IF NOT EXISTS (SELECT * FROM systypes WHERE xtype = 127)
		INSERT systypes(name,xtype,status,xusertype,length,xprec,xscale,tdefault,domain,reserved,uid)
		VALUES ('bigint',127,0,127,8,19,0,0,0,0,1)
	ELSE
		update systypes set 
			name = 'bigint', status = 0, xusertype = 127, length = 8,
			xprec = 19, xscale = 0, tdefault = 0, domain = 0,
			reserved = 0,  uid = 1		
		where xtype = 127

	--20Jan99 change sysindexes rowcnt column type from binary to bigint
	UPDATE syscolumns SET xtype = 127,xusertype = 127, length = 8, xprec = 19, xscale = 0
	WHERE id = 2 AND colid = 12

	-- 18Feb99 Add sysfulltextnotify if not already present
	if object_id('sysfulltextnotify', 'S') is null
	begin
		BEGIN TRAN

		-- Upgrade from 7.0 requires rebuilding full-text catalogs.  Turn off sysdatabases
		-- flag indicating this database is enabled for full-text.
		update master.dbo.sysdatabases set status2 = status2 & ~536870912 where dbid = db_id()

		CREATE TABLE sysfulltextnotify (
				tableid			int NOT NULL,
				rowinfo			smallint NOT NULL,
				ftkey			varbinary(482) NOT NULL
				)
		CREATE UNIQUE CLUSTERED INDEX sysfulltextnotify
				ON sysfulltextnotify ( tableid, ftkey )	
		COMMIT TRAN
	end

	-- 2Apr99 Remap sysobjects::status bits
	if not exists (select id from sysobjects where id = 1 and (0x20000000 = status & 0x20000000))
	begin
		BEGIN TRAN
		-- remap A_N and Q_I for proc, func, view, trig, repl proc
		update sysobjects
		set status = (status & ~24)                                         | -- mask out old A_N, Q_I
					 (case (status & 16) when 16 then 536870912 else 0 end) | -- add new A_N if required
					 (case (status & 8) when 8 then 1073741824 else 0 end)  	 -- add new Q_I if required
		where xtype in ('P', 'FN', 'V', 'TR', 'RF')

		-- mask out TAB_ROWGUIDCOL, TAB_SELINTO for all tables
		-- set A_N and Q_I for all existing tables
		update sysobjects
		set status = (status & ~(64|1048576)) | 1610612736
		where xtype in ('S', 'U')

		COMMIT TRAN
	end

	-- 050499 Add sysproperties if not already present
	if object_id('sysproperties', 'S') is null
	begin
		BEGIN TRAN
		EXEC ('CREATE TABLE sysproperties (
			id				int			NOT NULL,
			smallid			smallint	NOT NULL,
			type			tinyint		NOT NULL,
			name			sysname		NOT NULL,
			value			sql_variant		NULL
				)'	)
		CREATE UNIQUE CLUSTERED INDEX sysproperties 
			ON sysproperties (id,type,smallid,name)
		COMMIT TRAN
	end

	-- 27APR1999 Drop sysallocations which was never put to use
	if object_id('sysallocations') is not null and
	   object_id('sysallocations') = 98
	begin
	    EXEC ('DROP TABLE sysallocations')
	end

	-- 30/04/1999 Reset PRC_SYSCATUP bit on system views.
	UPDATE sysobjects 
	SET status = status & ~1
	WHERE xtype = 'V' and status & 0x80000000 = 0x80000000

	-- 05/07/1999 Add column to sysdepends, change clustered index and clean up
	--         sysdepends
	if col_length('sysdepends', 'deptype') is null
	begin
		BEGIN TRAN

		-- new column
		EXEC ('ALTER TABLE sysdepends ADD deptype tinyint not null')
		EXEC ('UPDATE sysdepends set deptype = 0')

		-- redefined clustered index
		EXEC ('
		CREATE UNIQUE CLUSTERED INDEX sysdepends
			ON sysdepends ( deptype, depid, id, depnumber, number )
			WITH DROP_EXISTING, IGNORE_DUP_KEY
			')

		-- new nonclustered index
		EXEC ('
		CREATE UNIQUE NONCLUSTERED INDEX ncsysdepends1 
			ON sysdepends ( id, number, deptype, depid, depnumber ) 
			WITH IGNORE_DUP_KEY
			')

		COMMIT TRAN
	end

	-- cleanup sysdepends
	DELETE sysdepends FROM sysdepends d WHERE d.id not in (select id from sysobjects)
	DELETE sysdepends FROM sysdepends d WHERE d.depid not in (select id from sysobjects) and d.depid != 0

	-- 10Jul99 Add LCID column to syscolumns
	if not exists (SELECT * FROM dbo.syscolumns WHERE id = 3 AND name = 'language')
	begin
	 	BEGIN TRAN

		-- default to LANG_NEUTRAL for ALTER TABLE purposes.  This value will
		-- be updated later
	 	EXEC ('
		ALTER TABLE dbo.syscolumns ADD language int NOT null
			')

		EXEC ('
		UPDATE syscolumns SET language = 
					CASE 
						WHEN colstat & 16 <> 0 THEN (SELECT value FROM master.dbo.sysconfigures WHERE config = 1126) 
						ELSE 0 
					END
		    ')

		COMMIT TRAN
	end

	-- 22-Jul-1999: Remove INFORMATION_SCHEMA views from all databases but master
	if db_id() <> 1
	begin
		if object_id('INFORMATION_SCHEMA.SCHEMATA', 'V') is not NULL
			drop view INFORMATION_SCHEMA.SCHEMATA
		if object_id('INFORMATION_SCHEMA.TABLES', 'V') is not NULL
			drop view INFORMATION_SCHEMA.TABLES
		if object_id('INFORMATION_SCHEMA.TABLE_CONSTRAINTS', 'V') is not NULL
			drop view INFORMATION_SCHEMA.TABLE_CONSTRAINTS
		if object_id('INFORMATION_SCHEMA.TABLE_PRIVILEGES', 'V') is not NULL
			drop view INFORMATION_SCHEMA.TABLE_PRIVILEGES
		if object_id('INFORMATION_SCHEMA.COLUMNS', 'V') is not NULL
			drop view INFORMATION_SCHEMA.COLUMNS
		if object_id('INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE', 'V') is not NULL
			drop view INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
		if object_id('INFORMATION_SCHEMA.COLUMN_PRIVILEGES', 'V') is not NULL
			drop view INFORMATION_SCHEMA.COLUMN_PRIVILEGES
		if object_id('INFORMATION_SCHEMA.DOMAINS', 'V') is not NULL
			drop view INFORMATION_SCHEMA.DOMAINS
		if object_id('INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS', 'V') is not NULL
			drop view INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
		if object_id('INFORMATION_SCHEMA.KEY_COLUMN_USAGE', 'V') is not NULL
			drop view INFORMATION_SCHEMA.KEY_COLUMN_USAGE
		if object_id('INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS', 'V') is not NULL
			drop view INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
		if object_id('INFORMATION_SCHEMA.CHECK_CONSTRAINTS', 'V') is not NULL
			drop view INFORMATION_SCHEMA.CHECK_CONSTRAINTS
		if object_id('INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE', 'V') is not NULL
			drop view INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
		if object_id('INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE', 'V') is not NULL
			drop view INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
		if object_id('INFORMATION_SCHEMA.VIEWS', 'V') is not NULL
			drop view INFORMATION_SCHEMA.VIEWS
		if object_id('INFORMATION_SCHEMA.VIEW_TABLE_USAGE', 'V') is not NULL
			drop view INFORMATION_SCHEMA.VIEW_TABLE_USAGE
		if object_id('INFORMATION_SCHEMA.VIEW_COLUMN_USAGE', 'V') is not NULL
			drop view INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
		if object_id('INFORMATION_SCHEMA.ROUTINES', 'V') is not NULL
			drop view INFORMATION_SCHEMA.ROUTINES
		if object_id('INFORMATION_SCHEMA.PARAMETERS', 'V') is not NULL
			drop view INFORMATION_SCHEMA.PARAMETERS
		if object_id('INFORMATION_SCHEMA.ROUTINE_COLUMNS', 'V') is not NULL
			drop view INFORMATION_SCHEMA.ROUTINE_COLUMNS
		delete sysusers where uid = 3 and name = 'INFORMATION_SCHEMA'
	end

	UPDATE dbo.syscolumns SET name = N'reserved3' WHERE id = 2 AND colid = 14 and name = N'soid'
	UPDATE dbo.syscolumns SET name = N'reserved4' WHERE id = 2 AND colid = 15 and name = N'csid'
	EXEC('UPDATE dbo.sysindexes SET reserved3 = 0')
	EXEC('UPDATE dbo.sysindexes SET reserved4 = 0')

	-- Reset bits used to record determinism (objects & computed-columns) in interim builds
	UPDATE sysobjects SET status = status & ~268435456
	UPDATE syscolumns SET colstat = colstat & ~32 WHERE id IN (SELECT id FROM sysobjects WHERE xtype = 'U')
go
deny execute on sp_msupg_dosystabcatalogupgrades to public
go

if object_id('sp_msupg_recreatecatalogfaketables') is not null
	drop proc sp_msupg_recreatecatalogfaketables
go
-- INTERNAL PROC: DO NOT DOCUMENT
create proc sp_msupg_recreatecatalogfaketables
as
begin
	IF OBJECT_ID('sysindexkeys', 'S') IS NOT NULL
		DROP TABLE sysindexkeys
	CREATE TABLE sysindexkeys (
		id              int NOT NULL,
		indid           smallint NOT NULL,
		colid           smallint NOT NULL,
		keyno           smallint NOT NULL
		)
	GRANT SELECT ON sysindexkeys TO PUBLIC

	IF OBJECT_ID('sysforeignkeys', 'S') IS NOT NULL
		DROP TABLE sysforeignkeys
	CREATE TABLE sysforeignkeys (
		constid         int NOT NULL,
		fkeyid          int NOT NULL,
		rkeyid          int NOT NULL,
		fkey            smallint NOT NULL,
		rkey            smallint NOT NULL,
		keyno           smallint NOT NULL
		)
	GRANT SELECT ON sysforeignkeys TO PUBLIC

	IF OBJECT_ID('sysmembers', 'S') IS NOT NULL
		DROP TABLE sysmembers
	CREATE TABLE sysmembers (
		memberuid		smallint NOT NULL,
		groupuid		smallint NOT NULL
		)
	GRANT SELECT ON sysmembers TO PUBLIC

	IF OBJECT_ID('sysprotects', 'S') IS NOT NULL
		DROP TABLE sysprotects
	CREATE TABLE sysprotects (
		id              int NOT NULL,
		uid             smallint NOT NULL,
		action          tinyint NOT NULL,
		protecttype     tinyint NOT NULL,
		columns         varbinary(4000) NULL,
		grantor         smallint NOT NULL
		)
	GRANT SELECT ON sysprotects TO PUBLIC

	IF OBJECT_ID('sysfiles', 'S') IS NOT NULL
		DROP TABLE sysfiles
	CREATE TABLE sysfiles (
		fileid		smallint NOT NULL,
		groupid		smallint NOT NULL,
		size		int NOT NULL,
		maxsize		int NOT NULL,
		growth		int NOT NULL,
		status		int NOT NULL,
		perf		int NOT NULL,
		name		nchar (128) NOT NULL,
		filename	nchar (260) NOT NULL
		)
	GRANT SELECT ON sysfiles TO PUBLIC
end

go
deny execute on sp_msupg_recreatecatalogfaketables to public
go

/*
**	Update the max allowed network packet size to 32767, and lower any existing
**	config value that will be higher than the new max. First lower the config
**	value, so as to never create a situation where the config table has a value
**	that is greater than the max in spt_values.
*/
update master.dbo.sysconfigures
	set value = 32767
	where config = 505
		and value > 32767
		
update master.dbo.spt_values
	set high = 32767
	where name = 'network packet size (B)'
		and number = 505
		and type = 'C'
go

------------------------------- sp_monitor ------------------------------------

if object_id('sp_monitor','P') IS NOT NULL
	drop procedure 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 	decimal
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


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

raiserror(15339,-1,-1,'sp_addlogin')
go

create procedure sp_addlogin
    @loginame		sysname
   ,@passwd         sysname = Null
   ,@defdb          sysname = 'master'      -- UNDONE: DEFAULT CONFIGURABLE???
   ,@deflanguage    sysname = Null
   ,@sid			varbinary(16) = Null
   ,@encryptopt		varchar(20) = Null
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	Declare @ret    int    -- return value of sp call

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
	begin
	   dbcc auditevent (104, 1, 0, @loginame, NULL, NULL, @sid)
	   raiserror(15247,-1,-1)
	   return (1)
	end
	ELSE
	begin
	   dbcc auditevent (104, 1, 1, @loginame, NULL, NULL, @sid)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_addlogin')
		return (1)
	end

    -- VALIDATE LOGIN NAME AS:
    --  (1) Valid SQL Name (SQL LOGIN)
    --  (2) No backslash (NT users only)
    --  (3) Not a reserved login name
	execute @ret = sp_validname @loginame
	if (@ret <> 0)
        return (1)
    if (charindex('\', @loginame) > 0)
    begin
        raiserror(15006,-1,-1,@loginame)
        return (1)
    end

	--Note: different case sa is allowed.
	if (@loginame = 'sa' or lower(@loginame) in ('public'))
	begin
		raiserror(15405, -1 ,-1, @loginame)
		return (1)
	end

    -- LOGIN NAME MUST NOT ALREADY EXIST --
	if exists(select * from master.dbo.syslogins where loginname = @loginame)
	begin
		raiserror(15025,-1,-1,@loginame)
		return (1)
	end

	-- VALIDATE DEFAULT DATABASE --
	IF db_id(@defdb) IS NULL
	begin
		raiserror(15010,-1,-1,@defdb)
	    return (1)
	end

	-- VALIDATE DEFAULT LANGUAGE --
	IF (@deflanguage IS NOT Null)
	begin
		Execute @ret = sp_validlang @deflanguage
		IF (@ret <> 0)
			return (1)
	end
	ELSE
	begin
		select @deflanguage = name from master.dbo.syslanguages
		where langid = @@default_langid	--server default language

		if @deflanguage is null
			select @deflanguage = N'us_english'
	end

	-- VALIDATE SID IF GIVEN --
	if ((@sid IS NOT Null) and (datalength(@sid) <> 16))
	begin
		raiserror(15419,-1,-1)
	 	return (1)
	end
	else if @sid is null
		select @sid = newid()
	if (suser_sname(@sid) IS NOT Null)
	begin
		raiserror(15433,-1,-1)
	 	return (1)
	end

	-- VALIDATE AND USE ENCRYPTION OPTION --
	declare @xstatus smallint
	select @xstatus = 2	-- access
	if @encryptopt is null
		select @passwd = pwdencrypt(@passwd)
	else if @encryptopt = 'skip_encryption_old'
	begin
		select @xstatus = @xstatus | 0x800,	-- old-style encryption
			@passwd = convert(sysname, convert(varbinary(30), convert(varchar(30), @passwd)))
	end
	else if @encryptopt <> 'skip_encryption'
	begin
		raiserror(15600,-1,-1,'sp_addlogin')
		return 1
	end

    -- ATTEMPT THE INSERT OF THE NEW LOGIN --
	BEGIN TRAN
		INSERT INTO master.dbo.sysxlogins VALUES
	        (NULL, @sid, @xstatus, getdate(),
        	    getdate(), @loginame, convert(varbinary(256), @passwd),
	            db_id(@defdb), @deflanguage)

		-- check that there are no duplicate rows with the same name
		if @@error <> 0 or exists(select * from master.dbo.sysxlogins with (nolock) where srvid IS NULL and name = @loginame and sid <> @sid)
		begin
			raiserror(15025,-1,-1,@loginame)
			ROLLBACK TRAN
			return (1)
		end					 

	COMMIT TRAN

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

    -- FINALIZATION: RETURN SUCCESS/FAILURE --
	raiserror(15298,-1,-1)
	return  (0)	-- sp_addlogin
go

grant execute on sp_addlogin to public
go

---------------------------- sp_change_users_login ----------------------------

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

raiserror(15339,-1,-1,'sp_change_users_login')
go
CREATE PROCEDURE sp_change_users_login
    @Action               varchar(10)       -- REPORT / UPDATE_ONE / AUTO_FIX
   ,@UserNamePattern      sysname  = Null
   ,@LoginName            sysname  = Null
   ,@Password			  sysname  = Null
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @exec_stmt nvarchar(430)

	declare @ret            int,
            @FixMode        char(5),
            @cfixesupdate   int,        -- count of fixes by update
            @cfixesaddlogin int,        -- count of fixes by sp_addlogin
            @dbname         sysname,
            @loginsid       varbinary(85),
            @110name        sysname

    -- SET INITIAL VALUES --
    select  @dbname         = db_name(),
            @cfixesupdate   = 0,
            @cfixesaddlogin = 0

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

    -- INVALIDATE USE OF SPECIAL LOGIN/USER NAMES --
    if suser_sid(@LoginName) = 0x1	-- 'sa'
    begin
        raiserror(15287,-1,-1,@LoginName)
        return (1)
    end
    if user_id(@UserNamePattern) in (1,0,3,4) --dbo, public, INFORMATION_SCHEMA, system_function_schema
    begin
        raiserror(15287,-1,-1,@UserNamePattern)
        return (1)
    end

    -- HANDLE REPORT --
    if upper(@Action) = 'REPORT'
    begin

        -- VALIDATE PARAMS --
        if @UserNamePattern IS NOT Null or @LoginName IS NOT Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- GENERATE REPORT --
        select UserName = name, UserSID = sid from sysusers
            where issqluser = 1 and (sid is not null and sid <> 0x0)
                    and suser_sname(sid) is null
            order by name
        return (0)
    end

    -- HANDLE UPDATE_ONE --
    if upper(@Action) = 'UPDATE_ONE'
    begin

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

        -- ERROR IF PARAMS NULL --
        if @UserNamePattern IS Null or @LoginName IS Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- VALIDATE PARAMS --
        -- Can ONLY remap SQL Users to SQL Logins!  Should be no need
        --  for re-mapping NT logins, and if you try, you'll mess up
        --  the user status bits! 
        if not exists (select name from sysusers where
                name = @UserNamePattern             -- match user name
            and issqluser = 1)                      -- must be sql user
        begin
            raiserror(15291,-1,-1,'User',@UserNamePattern)
            return (1)
        end
        select @loginsid = sid from master.dbo.syslogins where
                loginname = @LoginName              -- match login name
            and isntname = 0                        -- cannot use nt logins
        if @loginsid is null
        begin
            raiserror(15291,-1,-1,'Login',@LoginName)
            return (1)
        end

        -- ERROR IF SID ALREADY IN USE IN DATABASE --
        if exists (select sid from sysusers where sid = @loginsid
                    and name <> @UserNamePattern)
        begin
		    raiserror(15063,-1,-1)
		    return (1)
        end

        -- CHANGE THE USERS LOGIN (SID) --
        update sysusers set sid = @loginsid, updatedate = getdate()
                where name = @UserNamePattern and issqluser = 1
                and sid <> @loginsid

        -- FINALIZATION: REPORT (ONLY IF NOT SUCCESSFUL) AND EXIT --
        if @@error <> 0 or @@rowcount <> 1
            raiserror(15295,-1,-1, 0)
        return (0)
    end

    -- ERROR IF NOT AUTO_FIX --
    if upper(@Action) <> 'AUTO_FIX'
    begin
        raiserror(15286,-1,-1,@Action)
        return (1)
    end

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

    -- VALIDATE PARAMS --
    if @UserNamePattern IS Null or @LoginName IS NOT Null
    begin
        raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
        return (1)
    end

    -- LOOP THRU ORPHANED USERS --
	select @exec_stmt = 'DECLARE ms_crs_110_Users cursor global for
            select name from sysusers
            where name = N' + quotename( @UserNamePattern , '''')+ '
                and issqluser = 1 and suser_sname(sid) is null'
    EXECUTE (@exec_stmt)
    OPEN ms_crs_110_Users

    WHILE (110=110)
    begin
        FETCH next from ms_crs_110_Users into @110name
        if (@@fetch_status <> 0)
        begin
            DEALLOCATE ms_crs_110_Users
            BREAK
        end

        -- IS NAME ALREADY IN USE? --
        -- if suser_sid(@110name) is null
		if not exists(select * from master.dbo.syslogins where loginname = @110name)
        begin

		   -- VALIDATE PARAMS --
			if @Password IS Null
			begin
				raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
				return (1)
			end

            -- ADD LOGIN --
            execute @ret = sp_addlogin @110name, @Password, @dbname
            if @ret <> 0 or suser_sid(@110name) is null
            begin
                raiserror(15497,16,1,@110name)
                deallocate ms_crs_110_Users
                return (1)
            end
            select @FixMode = '1AddL'
            raiserror(15293,-1,-1,@110name)
        end
        ELSE
        begin
            Select @FixMode = '2UpdU'
            Raiserror(15292,-1,-1,@110name)
        end

        -- REPORT ERROR & CONTINUE IF DUPLICATE SID IN DB --
        select @loginsid = suser_sid(@110name)
        if exists(select name from sysusers where sid = @loginsid)
        begin
            raiserror(15331,-1,-1,@110name)
            CONTINUE
        end

        -- UPDATE SYSUSERS ROW --
        update sysusers set sid = @loginsid, updatedate = getdate(), status = (status & ~1) | 2 where name = @110name
        if @@error <> 0
        begin
            raiserror(15498,17,127)
            deallocate ms_crs_110_Users
            return (1)
        end


        if @FixMode = '1AddL'
            Select @cfixesaddlogin = @cfixesaddlogin + 1
        else
            Select @cfixesupdate = @cfixesupdate + 1
    end -- loop 110

    -- REPORT AND RETURN SUCCESS --
    raiserror(15295,-1,-1,@cfixesupdate)
    raiserror(15294,-1,-1,@cfixesaddlogin)
    return (0) -- sp_change_users_login
go

grant execute on sp_change_users_login to public
go

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

raiserror(15339,-1,-1,'sp_addalias')
go
create procedure sp_addalias
    @loginame       sysname,    -- name of the pretender
    @name_in_db     sysname     -- user to whom to alias the login
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @sid        varbinary(85),
            @targuid    smallint,
            @newuid     smallint,
            @status     smallint,
            @dbname     sysname

    -- CHECK PERMISSIONS --
    create table #Trace_Status (TraceFlag int, Status int)
	DBCC TRACESTATUS('no_output', 4650) with NO_INFOMSGS
	if @@rowcount > 0
	begin
		insert into #Trace_Status exec('DBCC TRACESTATUS(4650) WITH NO_INFOMSGS')
	end
       
    if (not is_member('db_owner') = 1) and 
		((not exists (select * from #Trace_Status where TraceFlag = 4650 and Status = 1)) or (not is_member('db_accessadmin') = 1))
	begin
		drop table #Trace_Status
		raiserror(15247,-1,-1)
		return (1)
	end

	drop table #Trace_Status

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_addalias')
		return (1)
	end

    -- VALIDATE LOGIN NAME (OBTAIN SID) --
    select @status = CASE WHEN charindex('\', @loginame) > 0 THEN 12 ELSE 0 END
    if @status = 0
        select @sid = suser_sid(@loginame)          -- sql user
    -- retry sql user as nt with dflt domain
    if @sid is null
    begin
        select @sid = get_sid('\U'+@loginame, NULL) -- nt user
        if @sid is null
        begin
            if @status = 0
                raiserror(15007,-1,-1,@loginame)
            else
                raiserror(15401,-1,-1,@loginame)
            return (1)
        end
        select @status = 12
    end
    -- PREVENT USE OF CERTAIN LOGINS --
	else if @sid = 0x1
	begin
		raiserror(15405, -1, -1, @loginame)
		return (1)
	end

    -- VALIDATE NAME-IN-DB (OBTAIN TARGET UID) --
    select @targuid = uid from sysusers where name = @name_in_db
                        and (issqluser = 1 or isntuser = 1)
						and uid NOT IN (3,4)	-- INFORMATION_SCHEMA, system_function_schema
    if @targuid is null
	begin
		raiserror(15008,-1,-1,@name_in_db)
		return (1)
	end

    -- ERROR IF LOGIN ALREADY IN DATABASE --
    if exists (select sid from sysusers where sid = @sid)
    begin

        -- ERROR IF ALREADY ALIASED --
        if exists (select sid from sysusers where sid = @sid and isaliased = 1)
	    begin
		    raiserror(15022,-1,-1)
		    return (1)
	    end

        -- ERROR: LOGIN ALREADY A USER --
        select @name_in_db = name, @dbname = db_name() from sysusers where sid = @sid
        raiserror(15278,-1,-1,@loginame,@name_in_db,@dbname)
        return (1)
    end

    -- ALTER NAME TO AVOID CONFLICTS IN NAME SPACE --
    select @loginame = '\' + @loginame
    if user_id(@loginame) is not null
    begin
	    raiserror(15023,-1,-1,@loginame)
        return (1)
    end

    -- OBTAIN NEW UID (RESERVE 1-4) --
    if user_name(5) IS NULL
        select @newuid = 5
    else
		select @newuid = min(uid)+1 from sysusers
            where uid >= 5 and uid < (16384 - 1)    -- stay in users range
                and user_name(uid+1) is null        -- uid not in use
    if @newuid is null
	begin
		raiserror(15065,-1,-1)
		return (1)
	end

    -- INSERT SYSUSERS ROW --
    insert into sysusers select
        @newuid, @status | 16, @loginame, @sid, 0x00,
                getdate(), getdate(), @targuid, NULL

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0
        return (1)
    raiserror(15340,-1,-1)
    return (0) -- sp_addalias
go

grant execute on sp_addalias to public
go


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

------------------------------- sp_dropalias ----------------------------------

raiserror(15339,-1,-1,'sp_dropalias')
go
create procedure sp_dropalias
    @loginame   sysname     -- login who is currently aliased
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @sid        varbinary(85)

    -- CHECK PERMISSIONS --
    create table #Trace_Status (TraceFlag int, Status int)
	DBCC TRACESTATUS('no_output', 4650) with NO_INFOMSGS
	if @@rowcount > 0
	begin
		insert into #Trace_Status exec('DBCC TRACESTATUS(4650) WITH NO_INFOMSGS')
	end
       
    if (not is_member('db_owner') = 1) and 
		((not exists (select * from #Trace_Status where TraceFlag = 4650 and Status = 1)) or (not is_member('db_accessadmin') = 1))
	begin
		drop table #Trace_Status
		raiserror(15247,-1,-1)
		return (1)
	end

	drop table #Trace_Status

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_dropalias')
		return (1)
	end

	-- VALIDATE LOGIN NAME (OBTAIN SID) --
	if charindex('\', @loginame) = 0
		select @sid = suser_sid(@loginame)          -- sql user
	if @sid is null
	begin
		select @sid = get_sid('\U'+@loginame, NULL) -- nt user
		if @sid is null
			begin
			-- Check directly for alias in sysusers
			SELECT @sid = sid FROM sysusers WHERE isaliased = 1 AND name = '\'+@loginame
			if @sid is null
			begin
				if charindex('\', @loginame) = 0
				raiserror(15007,-1,-1,@loginame)
				else
				raiserror(15401,-1,-1,@loginame)
				return (1)
			end
		end
	end

    -- DELETE THE ALIAS (IF ANY) --
    delete from sysusers where sid = @sid and isaliased = 1

    -- ERROR IF NO ROW DELETED --
    if @@rowcount = 0
    begin
		raiserror(15134,-1,-1)
		return (1)
    end

    -- FINALIZATION: PRINT/RETURN SUCCESS --
	raiserror(15492,-1,-1)
	return (0) -- sp_dropalias
go

grant execute on sp_dropalias to public
go

------------------------------- sp_configure ----------------------------------

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

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

/*
**  Although the @configname is good, @configvalue wasn't in range.
**  If "network packet size (B)" is between 32768 and 65536, this range was
**	allowed prior to SP4 but is disallowed now, so instead of erroring out,
**	silently drop the value down to the new max allowed, which is 32767.
*/
if @confignum is NULL
   begin
      select @confignum = number, @configvalue = high
         from master.dbo.spt_values,master.dbo.sysconfigures c
         where type = 'C'
            and (@configvalue between (high + 1) and 65536)
            and name like '%' + @configname + '%'
            and number = c.config
            and number = 505  --"network packet size (B)"
            and
               ((c.status & 2 <> 0 and @show_advance = 1)
                  OR
               (c.status & 2  = 0)
               )
   end

/*
**  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

/*
** 	Dont flush the proc cache if we are setting "show advanced option" (518) or "allow updates" (102)
** 	Otherwise flush the procedure cache - this is to account for options which become
** 	effective immediately (ie. dont need a server restart).
*/
if @confignum <> 518 and @confignum <> 102 
	begin
	dbcc freeproccache
	end

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

return (0) -- sp_configure
go

grant execute on sp_configure to public
go








--------------------------------------------------------------------------------
--	ODBC/OLEDB Catalog Procs (INSTCAT.SQL)
--------------------------------------------------------------------------------
-- MDAC PS: 74292
update spt_server_info
	set attribute_value = @@version
	where attribute_id = 2 -- DBMS_VER
go
------------------------------------------
-- SQL 8 PS: 472372
update spt_server_info 
	set attribute_value = '8.00.2020'
	where attribute_id = 500 -- SYS_SPROC_VERSION
go
------------------------------------------
-- SQL 8 PS: 362254
update spt_datatype_info set ss_dtype = 
    isnull((select distinct xtype 
            from systypes 
            where TYPE_NAME = name OR TYPE_NAME = name+' identity' OR TYPE_NAME = name+'() identity'),
          0)
------------------------------------------
-- SQL 8 PS: 472545
if object_id('sp_databases', 'P') is not null
    drop proc sp_databases
go

raiserror(15339,-1,-1,'sp_databases')
go

/*  Procedure for 8.0 server */  
create proc sp_databases
as
    set nocount on
    declare @name sysname
    declare @SQL  nvarchar(600)

    /* Use temporary table to sum up database size w/o using group by */
    create table #databases (
                  DATABASE_ID int NOT NULL,
                  size int NOT NULL)

    declare c1 cursor for 
        select name from master.dbo.sysdatabases
            where has_dbaccess(name) = 1 -- Only look at databases to which we have access

    open c1
    fetch c1 into @name

    while @@fetch_status >= 0
    begin
        select @SQL = 'insert into #databases
                select '+ convert(sysname, db_id(@name)) + ', sum(size) from '
                + QuoteName(@name) + '.dbo.sysfiles'
        /* Insert row for each database */
        execute (@SQL)
        fetch c1 into @name
    end
    deallocate c1

    select  
        DATABASE_NAME = db_name(DATABASE_ID),
        DATABASE_SIZE = size*8,/* Convert from 8192 byte pages to K */
        REMARKS = convert(varchar(254),null)    /* Remarks are NULL */
    from #databases
    order by 1
go

grant execute on sp_databases to public
go
------------------------------------------
-- SQL 8 PS: 363921
if object_id('sp_tables_ex', 'P') is not null
    drop proc sp_tables_ex
go

raiserror(15339,-1,-1,'sp_tables_ex')
go

create procedure sp_tables_ex(
    @table_server       sysname,
    @table_name         sysname = null,
    @table_schema       sysname = null,
    @table_catalog      sysname = null,
    @table_type         sysname = null)
as
    if ((isnull(charindex('%', @table_name),0) = 0) and
        (isnull(charindex('[', @table_name),0) = 0) and
        (isnull(charindex('_', @table_name),0) = 0))
    begin   /*  If no wild carding */
        select
            TABLE_CAT = TABLE_CATALOG,
            TABLE_SCHEM = TABLE_SCHEMA,
            TABLE_NAME = TABLE_NAME,
            TABLE_TYPE = TABLE_TYPE,
            REMARKS = convert(nvarchar(255),DESCRIPTION)
        from master.dbo.SYSREMOTE_TABLES <@table_server,
                                          @table_catalog,
                                          NULL,
                                          @table_name,
                                          NULL>
        where (TABLE_SCHEMA like @table_schema
            or @table_schema is NULL or (TABLE_SCHEMA is NULL and @table_schema = N'%'))
            and (TABLE_CATALOG like @table_catalog
            or   @table_catalog is NULL or (TABLE_CATALOG is NULL and @table_catalog = N'%'))
            and (charindex ('''' + TABLE_TYPE + '''', @table_type) <> 0
            or   @table_type is NULL)
        order by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, TABLE_NAME
    end
    else
    begin   /*  If wild carding */
        select
            TABLE_CAT = TABLE_CATALOG,
            TABLE_SCHEM = TABLE_SCHEMA,
            TABLE_NAME = TABLE_NAME,
            TABLE_TYPE = TABLE_TYPE,
            REMARKS = convert(nvarchar(255),DESCRIPTION)
        from master.dbo.SYSREMOTE_TABLES <@table_server,
                                          @table_catalog,
                                          NULL,
                                          NULL,
                                          NULL>
        where (TABLE_SCHEMA like @table_schema
            or @table_schema is NULL or (TABLE_SCHEMA is NULL and @table_schema = N'%'))
            and (TABLE_CATALOG like @table_catalog
            or   @table_catalog is NULL or (TABLE_CATALOG is NULL and @table_catalog = N'%'))
            and (charindex ('''' + TABLE_TYPE + '''', @table_type) <> 0
            or   @table_type is NULL)
            and TABLE_NAME like @table_name
        order by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, TABLE_NAME
    end
go

grant execute on sp_tables_ex to public
go
------------------------------------------
-- MDAC PS: 73633
if object_id('sp_catalogs_rowset', 'P') is not null
    drop proc sp_catalogs_rowset
go

raiserror(15339,-1,-1,'sp_catalogs_rowset')
go

/*  Procedure for 8.0 server */
create procedure sp_catalogs_rowset
	(
	@catalog_name		sysname
	)    	
as
	select
		CATALOG_NAME	= name,
		DESCRIPTION	= convert(nvarchar(1),null)
	from 	master.dbo.sysdatabases
    where   name = @catalog_name AND has_dbaccess(name)=1
go

create procedure sp_catalogs_rowset;2
	(
	@dummy		int /* remove when Hydra bug 17032 is fixed */
	)
as
	select
		CATALOG_NAME	= name,
		DESCRIPTION	= convert(nvarchar(1),null)
	from 	master.dbo.sysdatabases
    where   has_dbaccess(name)=1
	order by 1
go

create procedure sp_catalogs_rowset;5
	(
	@server_name	sysname,
	@catalog_name	sysname = NULL
	)
as
	select	CATALOG_NAME,
		DESCRIPTION
	from master.dbo.SYSREMOTE_CATALOGS <@server_name, @catalog_name>
    where   has_dbaccess(CATALOG_NAME)=1
	order by 1
go

grant execute on sp_catalogs_rowset to public
go
------------------------------------------
-- MDAC PS: 85041
if object_id('sp_primary_keys_rowset', 'P') is not null
    drop proc sp_primary_keys_rowset
go

raiserror(15339,-1,-1,'sp_primary_keys_rowset')
go

/*  Procedure for 7.0 & 8.0 servers */  
create procedure sp_primary_keys_rowset
    (
    @table_name     sysname,
    @table_schema   sysname = null
    )
as
    select  TABLE_CATALOG   = db_name(),
            TABLE_SCHEMA    = user_name(o.uid),
            TABLE_NAME      = o.name,   
            COLUMN_NAME     = c.name,
            COLUMN_GUID     = convert(uniqueidentifier,null),
            COLUMN_PROPID   = convert(int,null),
            ORDINAL         = convert(int,
                                      case
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  1) then 1
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  2) then 2
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  3) then 3
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  4) then 4
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  5) then 5
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  6) then 6
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  7) then 7
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  8) then 8
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  9) then 9
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 10) then 10
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 11) then 11
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 12) then 12
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 13) then 13
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 14) then 14
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 15) then 15
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 16) then 16
                                      end
                                     ),
            PK_NAME         = i.name
    from    sysindexes i, syscolumns c, sysobjects o
    where   o.type in ('U')
    and     o.name = @table_name
    and     (@table_schema is null or @table_schema = user_name(o.uid))
    and     o.id = c.id
    and     o.id = i.id
    and     (i.status & 0x800) = 0x800 -- PRIMARY KEY
    and (c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  1) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  2) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  3) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  4) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  5) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  6) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  7) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  8) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  9) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 10) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 11) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 12) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 13) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 14) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 15) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 16)
        )
    and permissions(o.id) <> 0
    order by 2, 3
go

create procedure sp_primary_keys_rowset;2
    (
    @table_schema   sysname = null
    )
as
    select  TABLE_CATALOG   = db_name(),
            TABLE_SCHEMA    = user_name(o.uid),
            TABLE_NAME      = o.name,   
            COLUMN_NAME     = c.name,
            COLUMN_GUID     = convert(uniqueidentifier,null),
            COLUMN_PROPID   = convert(int,null),
            ORDINAL         = convert(int,
                                      case
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  1) then 1
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  2) then 2
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  3) then 3
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  4) then 4
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  5) then 5
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  6) then 6
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  7) then 7
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  8) then 8
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  9) then 9
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 10) then 10
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 11) then 11
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 12) then 12
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 13) then 13
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 14) then 14
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 15) then 15
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 16) then 16
                                      end
                                     ),
            PK_NAME         = i.name
    from    sysindexes i, syscolumns c, sysobjects o
    where   o.type in ('U')
    and     (@table_schema is null or @table_schema = user_name(o.uid))
    and     o.id = c.id
    and     o.id = i.id
    and     (i.status & 0x800) = 0x800 -- PRIMARY KEY
    and (c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  1) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  2) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  3) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  4) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  5) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  6) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  7) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  8) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  9) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 10) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 11) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 12) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 13) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 14) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 15) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 16)
        )
    and permissions(o.id) <> 0
    order by 2, 3
go

create procedure sp_primary_keys_rowset;3
    (
    @table_name     sysname = null,
    @table_schema       sysname = null
    )
as
IF @table_name is not NULL
    BEGIN
    select  TABLE_CATALOG   = db_name(),
            TABLE_SCHEMA    = user_name(o.uid),
            TABLE_NAME      = o.name,   
            COLUMN_NAME     = c.name,
            COLUMN_GUID     = convert(uniqueidentifier,null),
            COLUMN_PROPID   = convert(int,null),
            ORDINAL         = convert(int,
                                      case
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  1) then 1
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  2) then 2
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  3) then 3
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  4) then 4
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  5) then 5
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  6) then 6
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  7) then 7
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  8) then 8
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  9) then 9
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 10) then 10
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 11) then 11
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 12) then 12
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 13) then 13
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 14) then 14
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 15) then 15
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 16) then 16
                                      end
                                     ),
            PK_NAME         = i.name
    from    sysindexes i, syscolumns c, sysobjects o
    where   o.type in ('U')
    and     o.name = @table_name
    and     (@table_schema is null or @table_schema = user_name(o.uid))
    and     o.id = c.id
    and     o.id = i.id
    and     (i.status & 0x800) = 0x800 -- PRIMARY KEY
    and (c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  1) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  2) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  3) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  4) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  5) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  6) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  7) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  8) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  9) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 10) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 11) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 12) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 13) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 14) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 15) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 16)
        )
    and permissions(o.id) <> 0
    END
ELSE
    BEGIN
    select  TABLE_CATALOG   = db_name(),
            TABLE_SCHEMA    = user_name(o.uid),
            TABLE_NAME      = o.name,   
            COLUMN_NAME     = c.name,
            COLUMN_GUID     = convert(uniqueidentifier,null),
            COLUMN_PROPID   = convert(int,null),
            ORDINAL         = convert(int,
                                      case
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  1) then 1
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  2) then 2
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  3) then 3
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  4) then 4
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  5) then 5
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  6) then 6
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  7) then 7
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  8) then 8
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid,  9) then 9
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 10) then 10
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 11) then 11
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 12) then 12
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 13) then 13
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 14) then 14
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 15) then 15
                                      when c.name = index_col(user_name(o.uid)+'.'+o.name, i.indid, 16) then 16
                                      end
                                     ),
            PK_NAME         = i.name
    from    sysindexes i, syscolumns c, sysobjects o
    where   o.type in ('U')
    and     (@table_schema is null or @table_schema = user_name(o.uid))
    and     o.id = c.id
    and     o.id = i.id
    and     (i.status & 0x800) = 0x800 -- PRIMARY KEY
    and (c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  1) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  2) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  3) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  4) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  5) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  6) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  7) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  8) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid,  9) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 10) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 11) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 12) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 13) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 14) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 15) or
         c.name = index_col (user_name(o.uid)+'.'+o.name, i.indid, 16)
        )
    and permissions(o.id) <> 0
    END
go

create procedure sp_primary_keys_rowset;5
    (
    @table_server       sysname,
    @table_catalog      sysname = null,
    @table_name         sysname = null,
    @table_schema       sysname = null
    )
as
    select
        TABLE_CATALOG,  
        TABLE_SCHEMA,   
        TABLE_NAME, 
        COLUMN_NAME,    
        COLUMN_GUID,    
        COLUMN_PROPID,  
        ORDINAL
        --PK_NAME       
    from master.dbo.SYSREMOTE_PRIMARY_KEYS <@table_server,
                                            @table_catalog,
                                            @table_schema,
                                            @table_name>
    order by 1,2,3
go

grant execute on sp_primary_keys_rowset to public
go
------------------------------------------
-- SQL 8 PS: 469886
if object_id('sp_linkedservers_rowset', 'P') is not null
    drop proc sp_linkedservers_rowset
go

raiserror(15339,-1,-1,'sp_linkedservers_rowset')
go

/*  Procedure for 8.0 server */  

create proc sp_linkedservers_rowset
    (
    @srvname    sysname
    )
as
	select
		SVR_NAME 		    = srvname,
		SVR_PRODUCT		    = srvproduct,
		SVR_PROVIDERNAME	= providername,
		SVR_DATASOURCE		= datasource,
		SVR_PROVIDERSTRING  = case when is_srvrolemember('sysadmin') = 1 then providerstring else NULL end,
		SVR_LOCATION		= location,
		SVR_CATALOG		    = catalog
	from master.dbo.sysservers
	where srvname = @srvname and (srvstatus & 128) = 128
	order by 1
go

create proc sp_linkedservers_rowset;2
as
	select
		SVR_NAME 		    = srvname,
		SVR_PRODUCT		    = srvproduct,
		SVR_PROVIDERNAME	= providername,
		SVR_DATASOURCE		= datasource,
		SVR_PROVIDERSTRING  = case when is_srvrolemember('sysadmin') = 1 then providerstring else NULL end,
		SVR_LOCATION		= location,
		SVR_CATALOG		    = catalog
	from master.dbo.sysservers
	where (srvstatus & 128) = 128
	order by 1
go

grant execute on sp_linkedservers_rowset to public
go
------------------------------------------









--------------------------------------------------------------------------------
--	End of SQLDMO System Procedures (SQLDMO.SQL)
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
-- END OF FILE: Turn off marking of system objects.
--	DO NOT ADD ANYTHING AFTER THIS POINT
--------------------------------------------------------------------------------
exec sp_MS_upd_sysobj_category 2
go

exec sp_configure 'allow updates',0
go

reconfigure with override
go
