
/**********************************************************************
 *	Microsoft SQL Server SQL-DMO scripts, version 6.5.
 *	Date created:  3/17/96.
 *	Copyright Microsoft Corporation, 1992-1996
 **********************************************************************
 *	This script must be installed for the SQL-DMO (SQLOLE) objects and
 *	the SQL Enterprise Manager to run against a Microsoft SQL Server.
 *	This script is backwards-compatible with version 6.0; following
 *	installation, either the 6.0 or 6.5 versions of SQL-DMO and SQL
 *	Enterprise Manager will run against the server.
 **********************************************************************/














/* Preprocessor directives, will be blank space in output .sql file. */


/* All 6.x versions should be downward compatible, except for explicit switching on the uplevel #define. */


































































/* status values for these. */







/* bitmask values for same; power(2, DRI_*). */


















/* DRI-generated index masks, to apply to sysindexes.status */






/* sysobjects.category bit that indicates this is an MS-internal object. */


/* sysobjects.category bit for an sp_ that indicates it's a startup proc, or an xp that should ImpersonateClient. */



/* BIT_CLUSTERED indicates the key is clustered. */
/* EXCLUDE REPLICATION value in sysconstraints.status, and system-generated name. */





/* sysobjects.sysstat bits (lower 4) that mask off the object type. */


/* bit for DEFAULTS which are really DRI-created. */


/* bits for columns - don't conflict with bit_sysgenname for DRIDefaults. */






/* sysdatabases.category bits */



/* sysobjects.category bits (from ntdbms\object.h) */

















/* The parser can't '|' 0x-prefixed types as it thinks they're binary */







/**** daVinci additions ****/

/* sp_MStablekeys @flags bitmask param values. */


/* sp_MShelpindexes @flags bitmask param values. */


/* sp_MStablechecks @flags bitmask param values. */






/* From SQLOLE.H, MUST BE SYNC'd!!! */






































/* From perm.h */















/* Localized error-string files (PRE95_ERRMSG_*) for PRE95 servers. */
/* Set the include path to determine which lang-specific version is picked up. */

/* USA-localized strings for 4.21 localization of sqlole.cxx */
/* Should match the localized string in sysmessages (see sqlole.cxx for message number). */














use master
go

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
print ''
print 'Dumping transaction log...'
print ''
go
dump tran master with no_log
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/********************* Delete existing objects *********************************/
print ''
print 'Deleting existing objects...'
print ''
go





if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MShelpcolumns')
	drop procedure sp_MShelpcolumns
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MShelpindex')
	drop procedure sp_MShelpindex
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MShelptype')
	drop procedure sp_MShelptype
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSdependencies')
	drop procedure sp_MSdependencies
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MStablespace')
	drop procedure sp_MStablespace
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSindexspace')
	drop procedure sp_MSindexspace
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSuniquename')
	drop procedure sp_MSuniquename
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSkilldb')
	drop procedure sp_MSkilldb
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSdbuserprofile')
	drop procedure sp_MSdbuserprofile
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSobjectprivs')
	drop procedure sp_MSobjectprivs
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSloginmappings')
	drop procedure sp_MSloginmappings
go

















































if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSSQLOLE_version')
	drop procedure sp_MSSQLOLE_version
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSSQLOLE65_version')
	drop procedure sp_MSSQLOLE65_version
go

/********************* Create new objects *********************************/







/*******************************************************************************/
print ''
print 'Creating sp_MShelpcolumns'
print ''
go
create procedure sp_MShelpcolumns
@tablename varchar(92), @flags int = 0, @orderby varchar(10) = null
as
	if @flags is null
		select @flags = 0
	if (@tablename = '?')
	begin
		print ''
		print 'Usage:  sp_MShelpcolumns @tablename, @flags int = 0'
		print ' where @flags is a bitmask of:'
		print ' 0x0200		= No DRI (ignore Checks, Primary/Foreign/Unique Keys, etc.)'
		print ' 0x0400		= UDDTs --> Base type'
		print ' 0x0800		= 42Syntax (convert numeric/decimal, no identity)'
		print ' 0x80000		= TimestampToBinary (convert timestamp cols to binary(8))'
		print ' 0x40000000	= No Identity attribute'
		return 0
	end


	declare @42errmsg varchar(100)
	if (object_id(@tablename) is null)
	begin
		select @42errmsg = @tablename + ' does not exist.'
		RAISERROR 4215001 @42errmsg
		return 1
	end

	set nocount on

	create table #sphelpcols
	(
		col_name		varchar(30)		NOT NULL,
		col_id			tinyint			NOT NULL,
		col_type		smallint		NOT NULL,
		col_len			tinyint			NOT NULL,
		col_prec		tinyint			NULL,
		col_scale		tinyint			NULL,
		col_def			int				NULL,
		col_rul			int				NULL,
		col_null		bit				NOT NULL,	/* status & 8 */
		col_identity	bit				NOT NULL,	/* status & 128 */
		col_defname		varchar(61)		NULL,		/* fully-qual'd default name, or NULL */
		col_rulname		varchar(61)		NULL,		/* fully-qual'd rule name, or NULL */
		col_basetype	int				NULL,
		col_flags		int				NULL,
		col_seed		int				NULL,
		col_increment	int 			NULL,
		col_dridefname	varchar(30)		NULL,		/* DRI DEFAULT name */
		col_drideftext	varchar(255)	NULL		/* DRI DEFAULT text */
	)

	/* First load stuff so we can blot off inappropriate info and massage as per @flags */
	insert #sphelpcols
	select name, colid, usertype, length, NULL, NULL, cdefault, domain,
			convert(bit, status & 0x0008), convert(bit, status & 0x0080), NULL, NULL, type, 0, null, null, null, null
	from syscolumns
	where id = object_id(@tablename)

	/* Convert any timestamp column to binary(8) if they asked. */
	if (@flags is not null and @flags & 0x80000 != 0)
		update #sphelpcols set col_type = 3, col_len = 8, col_flags = col_flags | 0x0001 where col_type = 80

	/* Convert from the dblib-specific 'nullable' type to something people and the server recognize. */
	/* datetime --> datetimn */
	update #sphelpcols set col_type = 12 where col_type = 15
	/* decimal --> decimaln */
	update #sphelpcols set col_type = 24 where col_type = 26
	/* float --> floatn */
	update #sphelpcols set col_type = 8 where col_type = 14
	/* int --> intn*/
	update #sphelpcols set col_type = 7 where col_type = 13
	/* money --> moneyn*/
	update #sphelpcols set col_type = 11 where col_type = 17
	/* numeric --> numericn */
	update #sphelpcols set col_type = 10 where col_type = 25

	/* Set the physical base datatype to col_type if it's not a usertype, else to the actual physical basetype. */
	update #sphelpcols set col_basetype = col_type where col_type < 100
	update #sphelpcols set col_basetype = bt.usertype
		from systypes bt
		where col_type > 100 and bt.type = col_basetype and bt.usertype < 99 and bt.usertype not in (18, 80)

	/* Now do the same conversion for the physical base datatype. */
	/* datetime --> datetimn */
	update #sphelpcols set col_basetype = 12 where col_basetype = 15
	/* decimal --> decimaln */
	update #sphelpcols set col_basetype = 24 where col_basetype = 26
	/* float --> floatn */
	update #sphelpcols set col_basetype = 8 where col_basetype = 14
	/* int --> intn*/
	update #sphelpcols set col_basetype = 7 where col_basetype = 13
	/* money --> moneyn*/
	update #sphelpcols set col_basetype = 11 where col_basetype = 17
	/* numeric --> numericn */
	update #sphelpcols set col_basetype = 10 where col_basetype = 25

	/* Make a nice, presentable qualified rule/default name for those which are non-null */
	update #sphelpcols set col_defname = user_name(d.uid) + '.' + d.name
			from #sphelpcols c, sysobjects d where c.col_def is not null and d.id = c.col_def
	update #sphelpcols set col_rulname = user_name(r.uid) + '.' + r.name
			from #sphelpcols c, sysobjects r where c.col_rul is not null and r.id = c.col_rul

	/* Now see what our flags are, if anything.  Ignore the physical base type here -- these */
	/* flags are just for scripting, which doesn't use that. */
	if (@flags is not null and @flags != 0)
	begin
		if (@flags & 0x0400 != 0)
		begin
			/* Track from usertype --> b.<base>type --> u.usertype in systypes */
			update #sphelpcols set col_type = u.usertype from #sphelpcols c, systypes b, systypes u
			where c.col_type > 100 and b.usertype = c.col_type and b.type = u.type and u.usertype < 100 and u.usertype not in (18, 80)
		end
	end

	/* For scripting, set the col_flags */
	update #sphelpcols set col_flags = col_flags | 0x0001 where col_type in (1,2,3,4)

	/* OK, now put out the data */
	set nocount off
	if (@orderby is null or @orderby = 'id')
	begin
		select c.col_name, c.col_id, DataType = t.name, c.col_len, c.col_prec, c.col_scale,
				BaseType = b.name, c.col_defname, c.col_rulname, c.col_null, c.col_identity,
				c.col_flags, c.col_seed, c.col_increment, c.col_dridefname, c.col_drideftext
		from #sphelpcols c, systypes t, systypes b
		where t.usertype = c.col_type and b.usertype = c.col_basetype
		order by c.col_id
	end else begin
		select c.col_name, c.col_id, DataType = t.name, c.col_len, c.col_prec, c.col_scale,
				BaseType = b.name, c.col_defname, c.col_rulname, c.col_null, c.col_identity,
				c.col_flags, c.col_seed, c.col_increment, c.col_dridefname, c.col_drideftext
		from #sphelpcols c, systypes t, systypes b
		where t.usertype = c.col_type and b.usertype = c.col_basetype
		order by c.col_name
	end

/**********************************/
























































































































































go
/* End sp_MShelpcolumns */

/*******************************************************************************/
print ''
print 'Creating sp_MShelpindex'
print ''
go
create procedure sp_MShelpindex
@tablename varchar(92), @indexname varchar(30) = null, @flags int = null
as
	/* @flags is for daVinci */
	if (@flags is null)
		select @flags = 0



	select i.name, i.status, i.indid, 0,

		IndCol1 = index_col(@tablename, i.indid, 1), 
		IndCol2 = index_col(@tablename, i.indid, 2),
		IndCol3 = index_col(@tablename, i.indid, 3), 
		IndCol4 = index_col(@tablename, i.indid, 4),
		IndCol5 = index_col(@tablename, i.indid, 5), 
		IndCol6 = index_col(@tablename, i.indid, 6),
		IndCol7 = index_col(@tablename, i.indid, 7), 
		IndCol8 = index_col(@tablename, i.indid, 8),
		IndCol9 = index_col(@tablename, i.indid, 9), 
		IndCol10 = index_col(@tablename, i.indid, 10),
		IndCol11 = index_col(@tablename, i.indid, 11), 
		IndCol12 = index_col(@tablename, i.indid, 12),
		IndCol13 = index_col(@tablename, i.indid, 13), 
		IndCol14 = index_col(@tablename, i.indid, 14),
		IndCol15 = index_col(@tablename, i.indid, 15), 
		IndCol16 = index_col(@tablename, i.indid, 16)




	from sysindexes i



	where id = object_id(@tablename) and i.indid > 0 and i.indid < 255
		and (@indexname is null or i.name = @indexname)



	order by i.name
go
/* End sp_MShelpindex */

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
print ''
print 'Dumping transaction log...'
print ''
go
dump tran master with no_log
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/*******************************************************************************/
print ''
print 'Creating sp_MShelptype'
print ''
go
create procedure sp_MShelptype
@typename varchar(92) = null, @flags varchar(10) = null
as
	if (@typename = '?')
	begin
		print ''
		print 'Usage:  sp_MShelptype @typeename = null, @flags varchar(10) = null'
		print ' where @flags is either:'
		print ' sdt		= look in system datatypes'
		print ' uddt  	= look in user defined datatypes'
		print ' null	= look wherever its found'
		print ''
		return 0
	end

	/* Catch typos... */
	if (@flags is not null and @flags not in ('sdt', 'uddt'))
		select @flags = null

	declare @42errmsg varchar(100)

	/* Find out what type we're gonna be looking in, if they gave us a name. */
	if (@typename is not null)
	begin
		declare @usertype int
		select @usertype = usertype from systypes where name = @typename
		if (@usertype is not null)
		begin
			if (@usertype < 100)
			begin
				if (@flags is null)
					select @flags = 'sdt'
				if (@flags != 'sdt')
					select @usertype = null
			end else begin
				if (@flags is null)
					select @flags = 'uddt'
				if (@flags != 'uddt')
					select @usertype = null
			end
		end
		if (@usertype is null)
		begin
			select @42errmsg = @typename + ' does not exist.'
			RAISERROR 4215001 @42errmsg
			return 1
		end
	end

	/* Now go get the info, depending on the type they gave us. */
	if (@flags is null or @flags = 'sdt')
	begin
		/* Exclude the 'xxxxn' dblib-specific nullable types, and hardcode a check for variable length and numeric usertypes. */
		select 	SystemDatatypeName = t.name,
				ifvarlen_max = y.length,






				allownulls = t.allownulls,
				isnumeric = 0, allowidentity = 0

			from systypes t, systypes y
			where t.usertype < 100 and t.usertype not in (15,26,14,13,17,25) and (@typename is null or t.name like @typename)
			and y.usertype =* t.usertype and y.usertype in (1,2,3,4)
			order by t.name
	end

	/* Need a temp table so we can ownerqualify nonNULL rules/defaults. */
	create table #sphelptype (
		dt_usertype		int				NULL,
		dt_basetype		int				NULL,
		dt_rul 			int				NULL,
		dt_def 			int				NULL,
		dt_rulname 		varchar(61)		NULL,
		dt_defname		varchar(61)		NULL,
		dt_flags		int				NULL
	)

	if (@flags is null or @flags = 'uddt')
	begin
		set nocount on
		insert #sphelptype (dt_usertype, dt_basetype, dt_rul, dt_def, dt_flags)
			select t.usertype,
			(select distinct b.usertype from systypes b where b.type = t.type and b.usertype < 100 and b.usertype not in (18, 80)),
			t.domain, t.tdefault, 0
			from systypes t
			where t.usertype > 99 and (@typename is null or t.name like @typename)

		/* Make a nice, presentable qualified rule/default name for those which are non-null */
		update #sphelptype set dt_defname = user_name(d.uid) + '.' + d.name
				from #sphelptype c, sysobjects d where c.dt_def is not null and d.id = c.dt_def
		update #sphelptype set dt_rulname = user_name(r.uid) + '.' + r.name
				from #sphelptype c, sysobjects r where c.dt_rul is not null and r.id = c.dt_rul

		/* For scripting, set the dt_flags -- these apply to the BASE datatype. */
		update #sphelptype set dt_flags = dt_flags | 0x0001 where dt_basetype in (1,2,3,4)
		update #sphelptype set dt_flags = dt_flags | 0x0002 where dt_basetype in (10, 24)

		set nocount off
		select distinct UserDatatypeName = t.name,
				owner = user_name(t.uid),
				basetypename = (select distinct b.name from systypes b where b.usertype = s.dt_basetype),
				defaultname = dt_defname,
				rulename = dt_rulname,
				tid = t.usertype,








				length = t.length, nullable = t.allownulls,	dt_prec = null, dt_scale = null, dt_flags, allowidentity = 0

			from systypes t, #sphelptype s
			where t.usertype > 99 and (@typename is null or t.name like @typename)
				and dt_usertype = t.usertype
			order by t.name
	end
go
/* End sp_MShelptype */

/*******************************************************************************/
print ''
print 'Creating sp_MSdependencies'
print ''
go

create procedure sp_MSdependencies
@objname varchar(92) = null, @objtype int = null, @flags int = 0x01fd, @objlist varchar(30) = null
as
	if (@objname = '?')
	begin
		print 'sp_MSobject_dependencies name = NULL, type = NULL, flags = 0x01fd'
		print '  name:  name or null (all objects of type)'
		print '  type:  type number (see below) or null'
		print '	  if both null, get all objects in database'
		print '  flags is a bitmask of the following values:'
		print '	  0x10000  = return multiple parent/child rows per object'
		print '	  0x20000  = descending return order'
		print '	  0x40000  = return children instead of parents'
		print '	  0x80000  = Include input object in output result set'
		print '	  0x100000 = return only firstlevel (immediate) parents/children'
		print '	  0x200000 = return only DRI dependencies'
		print '	  power(2, object type number(s))  to return in results set:'
		print '		  0 (1  	- 0x0001)	 - datatype'
		print '		  1 (2  	- 0x0002)	 - system tables or MS-internal objects'
		print '		  2 (4  	- 0x0004)	 - view'
		print '		  3 (8  	- 0x0008)	 - user table'
		print '		  4 (16		- 0x0010)	 - procedure'
		print '		  5 (32		- 0x0020)	 - log'
		print '		  6 (64 	- 0x0040)	 - default'
		print '		  7 (128	- 0x0080)	 - rule'
		print '		  8 (256	- 0x0100)	 - trigger'
		print '	  shortcuts:'
		print '		  29	 (0x011c) - trig, view, user table, procedure'
		print '		  448	(0x00c1) - rule, default, datatype'
		print '		  509	(0x01fd) - all but systables/objects'
		print '		  511	(0x01ff) - all'
		return 0
	end














	/* If they want SQLOLEDep_DRIOnly, remove all but usertable objects from @flags */





	if (@objtype in (0, 5, 6, 7))
	begin
		/* Print only, do not raiserror as we may be calling this blindly and this is not a real error. */
		print 'Rules, defaults, and datatypes do not have dependencies.'
		return (0)
	end

	/*
	 * Create #t1 and #t2 as temp object holding areas.  Columns are:
	 *	 tid		- temp object id
	 *	 ttype	 - temp object type
	 *	 pid		- parent or child object id
	 *	 ptype	 - parent or child object type
	 *	 bDone	 - NULL means dependencies not yet evaluated, else nonNULL.
	 */
	declare @curid int, @curcat int, @rowsaffected int
	declare @allobjs int
	declare @delinputobj int
	select @allobjs = 0, @delinputobj = 0, @curid = NULL, @curcat = NULL
	create table #t1 (
		tid				int				NULL, 
		ttype			smallint		NULL, 
		tcat			smallint		NULL, 
		pid				int				NULL, 
		ptype			smallint		NULL, 
		pcat			smallint		NULL, 
		bDone			smallint		NULL
	)
	create table #t2 (
		tid				int				NULL, 
		ttype			smallint		NULL, 
		tcat			smallint		NULL, 
		pid				int				NULL, 
		ptype			smallint		NULL, 
		pcat			smallint		NULL, 
		bDone			smallint		NULL
	)
	create table #tempudt (
		dtype			int				NOT NULL
	)

	/* Worktables we'll use for optimization.  #t3 and #t4 push us over the 64 2K page limit in 4.21, */
	/* but the real gain is from #temptrig anyway. */










	create table #temptrig(
		id				int				NOT NULL, 
		deltrig			int				NOT NULL, 
		sysstat			smallint		NOT NULL, 
		category		int				NOT NULL
	)
	create clustered index #ci_temptrig on #temptrig (deltrig) with allow_dup_row

	declare @42errmsg varchar(100)

	/*
	 * If both name and type are null, this means get every object in the
	 * database matching the specification they passed in.  Otherwise,
	 * find the passed object or all objects of the passed type.  Start off
	 * loading parent info (pid, tid); these will be put into child as needed.
	 * If Objlist is specified we simply load its contents into #t1.
	 */










		if (@objname is null and @objtype is null)
		begin
			set nocount on
			select @allobjs = 1
			insert #t1 (pid, ptype, pcat) select o.id, o.sysstat & 0x0f, o.category & 0x0002 from sysobjects o
				where ((power(2, o.sysstat & 0x0f) & 0x01ff) <> 0) and (o.sysstat & 0x0f not in (6, 7))
		end else begin
			if (@objname is not null)
			begin
				select @curid = id, @objtype = o.sysstat & 0x0f, @curcat = o.category & 0x0002 from sysobjects o where id = object_id(@objname)
				if (@curid is null)
				begin
					select @42errmsg = @objname + ' does not exist.'
					RAISERROR 4215001 @42errmsg
					return 1
				end
				if (@flags & 0x80000 = 0)
					select @delinputobj = @curid
			end

			set nocount on
			if (@curid is null)
				insert #t1 (pid, ptype, pcat) select o.id, o.sysstat & 0x0f, o.category & 0x0002 from sysobjects o
					where o.sysstat & 0x0f = @objtype
			else
				insert #t1 (pid, ptype, pcat) values (@curid, @objtype, @curcat)
		end



	/*
	 * All initial objects are loaded as parents/children.  Now we loop, creating
	 * rows of child/parent relationships.  Use #t2 as a temp area for the selects
	 * to simulate recursion; when they find no rows, we're done with this step.
	 *
	 * Note that triggers are weird; they're part of a table definition but can
	 * also reference other tables, so we need to evaluate them both ways.  SQL
	 * Server stores the table for a trigger object as its deltrig; if a trigger
	 * references another table, that relationship is stored in sysdepends.
	 * This peculiarity of triggers requires separating the object-retrieval pass
	 * from the creation-sequence pass (below).  Also, the fact that trigger tables
	 * are stored in a non-indexed column (deltrig) requires us to use a worktable
	 * if we're returning triggers, so we don't continually tablescan sysobjects.
	 */

	if (@flags & power(2, 8) != 0)
		insert #temptrig select d.id, d.deltrig, d.sysstat, d.category from sysobjects d where d.sysstat & 0x0f = 8

	while (select count(*) from #t1 where bDone is null) > 0
	begin
		/*
		 * Remove Microsoft-internal or other system objects from #t1, unless
		 * @flags specified including system tables.  We do this here so that
		 * cascaded system dependencies are not included unless specifically
		 * requested.  For other restrictions, we wait until below so that all
		 * cascaded object types are fully evaluated.
		 */
		if (@flags & power(2, 1) = 0)
			delete #t1 where ttype = 1 or tcat = 0x0002 or pcat = 0x0002

		if (@flags & 0x40000 != 0)
		begin
			
				/* Table --> Triggers */
				if (@flags & power(2, 8) != 0)
					insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
						select distinct t.pid, t.ptype, t.pcat, o.id, o.sysstat & 0x0f, o.category & 0x0002 from #t1 t, #temptrig o
							where t.bDone is null and t.ptype = 3 and o.deltrig = t.pid

				/* Object --> sysdepends children */
				insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
					select distinct t.pid, t.ptype, t.pcat, d.id, o.sysstat & 0x0f, o.category & 0x0002
					from #t1 t, sysdepends d, sysobjects o
					where t.bDone is null and d.depid = t.pid and d.id = o.id
			







		end else begin
			
				/* Trigger --> Table */
				if (@flags & power(2, 3) != 0)
					insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
						select distinct t.pid, t.ptype, t.pcat, o.deltrig, u.sysstat & 0x0f, u.category & 0x0002
							  from #t1 t, sysobjects o, sysobjects u
							where t.bDone is null and t.ptype = 8 and o.id = t.pid and o.deltrig != 0 and u.id = o.deltrig

				/* Object --> sysdepends parents */
				insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
					select distinct t.pid, t.ptype, t.pcat, d.depid, o.sysstat & 0x0f, o.category & 0x0002
					from #t1 t, sysdepends d, sysobjects o
					where t.bDone is null and d.id = t.pid and d.depid = o.id
			







		end

		/*
		 * We have this generation of parents in #t2, so clear the current
		 * child generation's bDone flags.  Then insert from #t2; the current
		 * parent generation becomes the next loop's child generation, with
		 * bDone = null until next loop's dependencies are selected.
		 */
		update #t1 set bDone = 1
		insert #t1 select * from #t2 where #t2.tid not in
			(select tid from #t1 where #t1.tid = #t2.tid and #t1.pid = #t2.pid)
		truncate table #t2

		/* If they only want one level, we're done.	*/
		if (@flags & 0x100000 <> 0)
			update #t1 set bDone = 1
	end

	/*
	 * The inner loop above did not put parents with no parents into the
	 * child (tid) list.  Do that now, then remove all rows where tid is
	 * NULL, because these were initial objects which now have a tid row.
	 * Just in case, remove self-refs from #t1, and also remove rows from #t1
	 * with NULL pid if a row exists for that tid where the pid is nonNULL.
	 * Avoid nested self-joins by using worktables.
	 */







	insert #t1 (tid, ttype, tcat) select distinct pid, ptype, pcat from #t1 t
		where not exists (select tid from #t1 where tid = t.pid)


	/*
	 * Because triggers can go in both directions, we'll need to check for
	 * circular dependencies on parent evaluation.  Since any tables referenced
	 * by the trigger must exist before the trigger can be created, remove rows
	 * where the trigger is the parent.
	 */
	if (@flags & 0x40000 = 0)
		delete #t1 where ptype = 8








	delete from #t1 where tid is null or tid = pid or (pid is null and tid in
		(select tid from #t1 where pid is not null))


	/*
	 * If we're to get all objects, get all UDDTs (which aren't in sysobjects)
	 * and Rules/Defaults, assuming we're returning those types.
	 */
	if (@allobjs <> 0)
	begin
		if (@flags & power(2, 0) != 0)
			insert #tempudt
				select usertype from systypes where usertype > 99
		if (@flags & (power(2, 7) | power(2, 6)) != 0)
			insert #t2 (tid, ttype, tcat)
				select id, sysstat & 0x0f, 0 from sysobjects
				where sysstat in (7, 6)
				
	end else begin
		/*
		 * Not getting all objects.  Get any datatypes that
		 * are referenced by objects in #t1.  We don't care about specific
		 * datatype dependencies, we just want to know which ones are needed.
		 */
		if (@flags & power(2, 0) != 0)
			insert #tempudt select distinct usertype from syscolumns
				where usertype > 99 and id in (select tid from #t1)

		/*
		 * Load rules and defaults needed by datatypes and other #t1 objects
		 * into #t2.  Don't track specific object dependencies with these;
		 * we just want to know which ones are needed.  For defaults only, eliminate
		 * those which are constraints.
		 */
		if (@flags & power(2, 7) != 0)
		begin
			insert #t2 (tid, ttype, tcat)
				select distinct s.domain, 7, 0 from systypes s, #tempudt t
					where s.domain != 0 and s.usertype = t.dtype
						and s.domain not in (select tid from #t1)
			insert #t2 (tid, ttype, tcat)
				select distinct s.domain, 7, 0 from syscolumns s, #t1 t
					where s.domain != 0 and s.id = t.tid
						and s.domain not in (select tid from #t1)
		end
		if (@flags & power(2, 6) != 0)
		begin
			insert #t2 (tid, ttype, tcat)
				select distinct s.tdefault, 6, 0 from systypes s, #tempudt t
					where s.tdefault != 0 and s.usertype = t.dtype
						and s.tdefault not in (select tid from #t1)
						
			insert #t2 (tid, ttype, tcat)
				select distinct s.cdefault, 6, 0 from syscolumns s, #t1 t
					where s.cdefault != 0 and s.id = t.tid
						and s.cdefault not in (select tid from #t1)
						
		end
	end		/* Not getting all objects */

	/*
	 * Now that we've got all objects we want, eliminate those we don't
	 * want to return.  If @inputobj and they don't want it returned,
	 * remove it from the table.  Then eliminate object types they don't
	 * want returned.  Make sure that in doing so we retain all parent
	 * objects of the types we do want -- it is possible at this point
	 * that a tid we want has no rows except those with pids we don't want.
	 */
	if (@flags & 0x01ff != 0x01ff or @delinputobj != 0)
	begin
		delete #t1 where @flags & power(2, ttype) = 0 or tid = @delinputobj

		/*
		 * Be sure that the insert does not duplicate rows that will survive the
		 * following delete -- these are rows where the pid is not @delinputobj
		 * and ptype is either null or a type we'll keep (if ptype is null then
		 * pid hasn't been set so no need for more complex checking).
		 */
		insert #t1 (tid, ttype, tcat) select distinct tid, ttype, tcat from #t1
			where (@flags & power(2, ptype) = 0 or pid = @delinputobj)
				and tid not in (select tid from #t1 where ptype is null or
					(pid != @delinputobj and @flags & power(2, ptype) != 0))
		delete #t1 where @flags & power(2, ptype) = 0 or pid = @delinputobj
	end

	/*
	 * To determine creation order, find all objects which are not yet bDone
	 * and have no parents or whose parents are all bDone, and set their bDone
	 * to the next @curid.  This will leave bDone as the ascending order in
	 * which objects must be created (topological sort).  Again, use worktables
	 * to remove nested self-joins.
	 */
	update #t1 set bDone = 0
	select @curid = 1, @rowsaffected = 1
	while (@rowsaffected <> 0)
	begin


















		if (@flags & 0x40000 != 0)
			update #t1 set bDone = @curid from #t1 where bDone = 0
				and tid not in (select pid from #t1 where pid is not null and bDone = 0)
		else
			update #t1 set bDone = @curid from #t1 where bDone = 0
				and tid not in (select tid from #t1 where pid is not null and pid in (select tid from #t1 where bDone = 0))


		select @rowsaffected = @@rowcount, @curid = @curid + 1
	end

	/* For SQL60 only, we need to check circular dependencies (DRI for tables is the only way to get them). */
	/* This will have occurred if we still have any rows in #t1 where bDone = 0, after the above loop. */







	/*
	 * Finally, return the objects.  Rules/Defaults must be created first so they're returned first,
	 * followed by UDDTs. followed by all other (sysdepends/DRI) dependencies.  @curid is the bDone
	 * value; we need to increment the #t1 value so our multi-result-set is in the proper sequence.
	 * Of course, these never have parents, so don't return them if asking for children.
	 */
	if (@flags & 0x40000 = 0) begin
		select @curid = 1
		if ((@flags & (power(2, 7) | power(2, 6)) != 0) and exists (select * from #t2)) begin
			update #t1 set bDone = bDone + 1
			select distinct oType = power(2, o.sysstat & 0x0f), oRuleDefName = o.name, oOwner = user_name(o.uid), oSequence = convert(smallint, @curid)
				from sysobjects o, #t2 t
				where o.id = t.tid
				order by power(2, o.sysstat & 0x0f), o.name
			select @curid = @curid + 1
		end
		if ((@flags & power(2, 0) != 0) and exists (select * from #tempudt)) begin
			update #t1 set bDone = bDone + 1
			select distinct oType = power(2, 0), oUDDTName = c.name, oOwner = user_name(c.uid), oSequence = convert(smallint, @curid)
				from systypes c, #tempudt t, sysobjects p
				where c.usertype = t.dtype
				order by c.name
			select @curid = @curid + 1
		end
	end

	/*
	 * Select dependency-style objects, returning parents if desired.
	 * Normally sorting is in terms of who must be created first, i.e. ascending:  parent-->child-->grandchild.
	 * Descending order (child-->parent-->grandparent) would be used for a graphical-dependencies evaluator showing
	 * the parents.  Therefore we invert bDone if descending sort.  bDone is 1-based; min + max - bDone gives inversion.
	 * Note:  Always return at least this empty set.
	 */
	if (@flags & 0x20000 != 0) begin
		select @curid = max(bDone) + min(bDone) from #t1
		update #t1 set bDone = convert(smallint, @curid) - bDone
	end
	if (@flags & 0x10000 != 0)
		select distinct oType = power(2, o.sysstat & 0x0f), oObjName = o.name, oOwner = user_name(o.uid),
				RelType = power(2, p.sysstat & 0x0f), RelName = p.name, RelOwner = user_name(p.uid),
				oSequence = t.bDone
			from sysobjects o, sysobjects p, #t1 t
			where o.id = t.tid and p.id =* t.pid
			order by t.bDone, power(2, o.sysstat & 0x0f), o.name
	else
		select distinct oType = power(2, o.sysstat & 0x0f), oObjName = o.name, oOwner = user_name(o.uid),
				oSequence = t.bDone
			from sysobjects o, #t1 t
			where o.id = t.tid
			order by t.bDone, power(2, o.sysstat & 0x0f), o.name

go
/* End sp_MSdependencies */

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
print ''
print 'Dumping transaction log...'
print ''
go
dump tran master with no_log
go
/************* END DUMP THE TRANSACTION LOG **********************************/

/*******************************************************************************/
print ''
print 'Creating sp_MStablespace'
print ''
go

create procedure sp_MStablespace
@name varchar(92), @id int = null
as
	declare @rows int, @datasizeused int, @indexsizeused int, @pagesize int
	declare @dbname varchar(30)
	select @dbname = db_name()
	declare @42errmsg varchar(100)

	if (@id is null)
		select @id = id from sysobjects where id = object_id(@name) and sysstat & 0x0f in (1, 3)
	if (@id is null)
	begin
		select @42errmsg = 'Object ''' + @name + ''' is not a table in database ''' + @dbname + ''''
		RAISERROR 4215009 @42errmsg
		return 1
	end

	/* rows */
	SELECT @rows = rows
		FROM sysindexes
		WHERE indid < 2 and id = @id

	/* data */
	SELECT @datasizeused =
	(SELECT sum(dpages)
	 FROM sysindexes
	 WHERE indid < 2 and id = @id)
	+
	(SELECT isnull(sum(used), 0)
	 FROM sysindexes
	 WHERE indid = 255 and id = @id)

	/* index */
	SELECT @indexsizeused =
	(SELECT sum(used)
	 FROM sysindexes
	 WHERE indid in (0, 1, 255) and id = @id)
	 - @datasizeused

	/* Pagesize on this server (sysindexes stores size info in pages) */
	select @pagesize = v.low / 1024 from master..spt_values v where v.number=1 and v.type='E'

	select Rows = @rows, DataSpaceUsed = @datasizeused * @pagesize, IndexSpaceUsed = @indexsizeused	* @pagesize
go

/* End sp_MStablespace */

/*******************************************************************************/
print ''
print 'Creating sp_MSindexspace'
print ''
go

CREATE PROCEDURE sp_MSindexspace
	@tablename varchar(92), @index_name varchar(30) = NULL
AS
BEGIN
  DECLARE @table_id int
  DECLARE @index_id int
  DECLARE @msg varchar(255)
  declare @42errmsg varchar(100)

  /* Make sure @tablename is local to the current database */
  IF (@tablename like '%.%.%') AND
     (substring(@tablename, 1, charindex('.', @tablename) - 1) <> db_name())
  BEGIN
    select @42errmsg = 'Table or view name must be in the ''current'' database.'
    RAISERROR 4215078 @42errmsg
    RETURN(1)
  END
  /* Make sure that @tablename and @index_name exist */
  SELECT @table_id = id
  FROM sysobjects
  WHERE (id = object_id(@tablename))
    AND (sysstat & 0xf = 3)
  IF (@table_id = NULL)
  BEGIN
    select @42errmsg = @tablename + ' does not exist.'
    RAISERROR 4215001 @42errmsg
    RETURN(1)
  END
  IF (@index_name <> NULL)
  BEGIN
    SELECT @index_id = indid
    FROM sysindexes
    WHERE (name = @index_name)
      AND (id = object_id(@tablename))
    IF (@index_id = NULL)
    BEGIN
      SELECT @msg = @tablename + '.' + @index_name
      select @42errmsg = @msg + ' does not exist.'
      RAISERROR 4215001 @42errmsg
      RETURN(1)
    END
  END
  /* Ok, we're good to go */
  IF (user_id() = 1) 
    CHECKPOINT
  IF (@index_name = NULL)
  BEGIN
    CREATE TABLE #IndexSizeTemp (
		IndexID			tinyint			NOT NULL, 
		IndexName		varchar(30)		NOT NULL, 
		IndexSize		int				NOT NULL, 
		Comments		varchar(28)		NOT NULL
	)  
    INSERT INTO #IndexSizeTemp
    SELECT indid, name, 0, ''
    FROM sysindexes 
    WHERE (id = object_id(@tablename))
      AND ((indid > 0) AND (indid < 255)) 
    UPDATE #IndexSizeTemp
    SET IndexSize = used * 2,
        Comments = '(None)' 
    FROM sysindexes si, #IndexSizeTemp ist
    WHERE (id = object_id(@tablename))
      AND (indid > 1) AND (indid < 255)
      AND (si.indid = ist.IndexID)
    UPDATE #IndexSizeTemp
    SET IndexSize = (used - dpages - isnull((SELECT sum(used) 
                                             FROM sysindexes
                                             WHERE (indid > 1) AND (indid < 255) 
                                               AND (id = object_id(@tablename))), 0)) * 2,
        Comments = 'Size excludes actual data.' 
    FROM sysindexes si, #IndexSizeTemp ist
    WHERE (id = object_id(@tablename))
      AND (indid = 1)
      AND (si.indid = ist.IndexID)
    SELECT 'Index ID' = IndexID, 'Index Name' = IndexName, 'Size (KB)' = IndexSize, Comments 
    FROM #IndexSizeTemp
    ORDER BY IndexID
    DROP TABLE #IndexSizeTemp
  END
  ELSE
  BEGIN 
    DECLARE @indid int
    SELECT @indid = indid 
    FROM sysindexes 
    WHERE (id = object_id(@tablename))
      AND (name = @index_name)
    /* The non-clustered index case */
    IF ((@indid > 1) AND (@indid < 255))
    BEGIN
      SELECT 'Size (KB)' = used * 2 
      FROM sysindexes 
      WHERE (id = object_id(@tablename))
        AND (name = @index_name)
      RETURN(0) 
    END
    /* The clustered index case */
    IF (@indid = 1)
    BEGIN
      SELECT 'Size (KB)' =
             (used - dpages - isnull((SELECT sum(used) 
                                      FROM sysindexes
                                      WHERE (indid > 1) AND (indid < 255) 
                                        AND (id = object_id(@tablename))), 0)) * 2 
      FROM sysindexes 
      WHERE (id = object_id(@tablename))
        AND (name = @index_name)
    END
  END
  RETURN(0)
END
go
/* End sp_MSindexspace */

/*******************************************************************************/










































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































/*******************************************************************************/

print ''
print 'Creating sp_MSloginmappings'
print ''
go



create proc sp_MSloginmappings
	@loginname varchar(30) = null, @flags int = 0
as
	/*
	 * @flags bits:
	 *		0x01	- current db only
	 */
	/* 
	 * Added @dbname so dbo can see everyone in current database.
	 * Use hacky 4.21 syntax so it will run there, instead of a case..when.
	 */
	declare @42errmsg varchar(100)
	declare @checkmultilogin int
	select @checkmultilogin = 1
	if ((@flags & 0x01 <> 0) and user_id() = 1) 
		select @checkmultilogin = 0

	declare @logincount int
	select @logincount = 0
	if (@loginname is not null)
		select @logincount = count(*) from syslogins where name like @loginname

	/* Gotta be sa or dbo to see other than just current login. */
	declare @numlogins int, @whereloginname varchar(100), @name varchar(30), @retval int
	if (@loginname is null)
		select @numlogins = 2
	else
		select @numlogins = count(*) from syslogins where name like @loginname

	if (@numlogins = 0) begin
		select @42errmsg = 'Login ''' + @loginname + ''' not found.'
		RAISERROR 4214220 @42errmsg		/* Login not found */
		return 1
	end
	if (@checkmultilogin <> 0) begin
		if (suser_id() <> 1 and (@numlogins > 1 or suser_id() <> suser_id(@loginname))) begin
			select @42errmsg = 'Logins other than the current user can only be seen by the SA.'
			RAISERROR 4214301 @42errmsg				/* Only sa can see other than the current login */
			return 1
		end
	end
	if (@loginname is not null)
		select @whereloginname = ' where name like ''' + @loginname + ''''

	/*
	 * This proc returns a result set with one or more rows for each database for which a login is a user or aliased to one.
	 * If loginname is specified, the results are limited to that login.  First load a temp table with all logins that are
	 * in a db, then add those which aren't mapped to any db.
	 */
	create table #loginmappings(
		LoginName			varchar(30)		NOT NULL, 
		DBName				varchar(30)		NULL, 
		UserName			varchar(30)		NULL, 
		AliasName			varchar(30)		NULL
	)
	if (@flags & 0x01 <> 0) begin
		INSERT #loginmappings select l.name, db_name(), u.name, null from master..syslogins l, sysusers u where l.suid = u.suid
		UNION select l.name, db_name(), null, u.name from master..syslogins l, sysusers u, sysalternates a where l.suid = a.suid and u.suid = a.altsuid















	end

	/* 
	 * Now bring them out by loginname, each in its own result set.
	 * If this is for all logins, we'll return all logins; if for curdb, 
	 * only those in #loginmappings (i.e. only those mapped in curdb).
	 */
















	/* 
	 * 4.21 will require a fake cursor.  sa's suid is the lowest; 1.  So start at 0.
	 */
	declare @suid int
	select @suid = 0
	while 1 = 1 begin
		select @suid = min(l.suid) from master..syslogins l where l.suid > @suid
				and (@loginname is null or l.name like @loginname)
		if @suid is null
			return 0
		select @name = l.name from master..syslogins l where l.suid = @suid
		/* This is for CURDB only . */
		if exists (select * from #loginmappings where LoginName = @name)
			select * from #loginmappings where LoginName = @name
	end


go
/* End sp_MSloginmappings */

/*******************************************************************************/
print ''
print 'Creating sp_MSuniquename'
print ''
go

create procedure sp_MSuniquename 
	@seed varchar(30), @start int = null
as
	/* Return a unique name for sysobjects, based on a passed-in seed. */
	set nocount on
	declare @i int, @append varchar(10), @seedlen int, @temp varchar(30), @recalcseedlen int, @seedcharlen int
	select @i = 1, @seedlen = datalength(@seed), @recalcseedlen = 1, @seedcharlen = 0
	if (@start is not null and @start >= 0)
		select @i = @start
	while 1 < 2
	begin
		/* This is probably overkill, but start at max length of seed name, leaving room under SYSNAMELEN for @append. */
		/* We'll work our way back along the string if more room needed (pathological user). */
		select @append = ltrim(str(@i)) + '__' + ltrim(str(@@spid))
		if (@recalcseedlen = @i or @seedcharlen = 0)
		begin
			while @recalcseedlen <= @i
				select @recalcseedlen = @recalcseedlen * 10
			select @seedcharlen = @seedlen
			if ((@seedlen + datalength(@append)) > 30) begin
				select @seedlen = 30 - datalength(@append)

				/* For 6.5, get the charlen of this datalength for the substring() call. */
				select @seedcharlen = @seedlen





			end		/* Recalc seedlen */
		end		/* Check seedlen */

		select @temp = substring(@seed, 1, @seedcharlen) + @append

		/* If I don't set a limit somewhere, it's gonna look hung -- I'd rather get a nonunique error. */
		if object_id(@temp) is null or @i > 999999		/* if increased, watch out for overflow of @recalcseedlen */
		begin
			set nocount off
			select Name = @temp, Next = @i + 1
			return 0
		end
		select @i = @i + 1
	end
go
/* End sp_MSuniquename */

/*******************************************************************************/
print ''
print 'Creating sp_MSkilldb'
print ''
go

sp_configure updat, 1
go
reconfigure with override
go

create proc sp_MSkilldb
	@dbname varchar(30)
as
	declare @42errmsg varchar(100)
	if (@@trancount > 0) begin
		select @42errmsg = 'The procedure ''sp_MSkilldb'' cannot be executed from within a transaction.'
		RAISERROR 4215002 @42errmsg
		return 1
	end

	if (suser_id() <> 1) begin
		select @42errmsg = 'You must be System Administrator (SA) to execute this stored procedure.'
		RAISERROR 4215003 @42errmsg
		return 1
	end

	/* Set this db to suspect, then let dbcc dbrepair kill it for us. */
	update master..sysdatabases set status = status | 0x0100
		where name = @dbname
	if (@@rowcount = 0) begin
		select @42errmsg = 'Database ''' + @dbname + ''' not found.'
		RAISERROR 4214211 @42errmsg
		return 1
	end
	dbcc dbrepair(@dbname, dropdb)
	return 0
go

sp_configure updat, 0
go
reconfigure with override
go

/* End sp_MSkilldb */

/*******************************************************************************/
print ''
print 'Creating sp_MSdbuserprofile'
print ''
go

create proc sp_MSdbuserprofile
	@mode varchar(10) = 'init', @qual varchar(30) = '%'
as

/* Order of privilege evaluation is:  user granted/revoked, then group granted/revoked, then public granted/revoked */







	set nocount on

	/* If 'srv', we're selecting the server (master db) user profile - currently, just create db priv. */
	declare @bits int, @gid int, @prot int
	if (lower(@mode) like 'serv%') begin
		select @bits = 0x0000
		if (suser_id() = 1) begin
			/* sa has everything */
			select @bits = 0x0007
		end else begin
			select @prot = null select @prot = protecttype from sysprotects where action = 203 and uid = user_id()
			if (@prot is null) select @prot = protecttype from sysprotects where action = 203 and uid = (select gid from sysusers where uid = user_id())
			if (@prot is null) select @prot = protecttype from sysprotects where action = 203 and uid = user_id('public')
			if (@prot = 205) select @bits = @bits | 0x0002

			select @prot = null select @prot = protecttype from sysprotects where action = 224 and uid = user_id() and id = object_id('sp_addextendedproc')
			if (@prot is null) select @prot = protecttype from sysprotects where action = 224 and uid = (select gid from sysusers where uid = user_id()) and id = object_id('sp_addextendedproc')
			if (@prot is null) select @prot = protecttype from sysprotects where action = 224 and uid = user_id('public') and id = object_id('sp_addextendedproc')
			if (@prot = 205) select @bits = @bits | 0x0004
		end
		select @bits
		return 0
	end






























































































	if (lower(@mode) like 'ini%')
		select @mode = 'select'


	/* All other modes require a creation of a profile bitmask. */
	if (user_id() = 1) begin
		/* sa/Dbo has everything. */
		select @bits = 0x00ff
	end else begin
		/* Not dbo so get individual privileges */
		select @bits = 0x0000, @gid = gid from sysusers where uid = user_id()
		select @prot = null select @prot = protecttype from sysprotects where action = 198 and uid = user_id()
		if (@prot is null) select @prot = protecttype from sysprotects where action = 198 and uid = (select gid from sysusers where uid = user_id())
		if (@prot is null) select @prot = protecttype from sysprotects where action = 198 and uid = user_id('public')
		if (@prot = 205) select @bits = @bits | 0x0002

		select @prot = null select @prot = protecttype from sysprotects where action = 207 and uid = user_id()
		if (@prot is null) select @prot = protecttype from sysprotects where action = 207 and uid = (select gid from sysusers where uid = user_id())
		if (@prot is null) select @prot = protecttype from sysprotects where action = 207 and uid = user_id('public')
		if (@prot = 205) select @bits = @bits | 0x0004

		select @prot = null select @prot = protecttype from sysprotects where action = 222 and uid = user_id()
		if (@prot is null) select @prot = protecttype from sysprotects where action = 222 and uid = (select gid from sysusers where uid = user_id())
		if (@prot is null) select @prot = protecttype from sysprotects where action = 222 and uid = user_id('public')
		if (@prot = 205) select @bits = @bits | 0x0008

		select @prot = null select @prot = protecttype from sysprotects where action = 228 and uid = user_id()
		if (@prot is null) select @prot = protecttype from sysprotects where action = 228 and uid = (select gid from sysusers where uid = user_id())
		if (@prot is null) select @prot = protecttype from sysprotects where action = 228 and uid = user_id('public')
		if (@prot = 205) select @bits = @bits | 0x0010

		select @prot = null select @prot = protecttype from sysprotects where action = 233 and uid = user_id()
		if (@prot is null) select @prot = protecttype from sysprotects where action = 233 and uid = (select gid from sysusers where uid = user_id())
		if (@prot is null) select @prot = protecttype from sysprotects where action = 233 and uid = user_id('public')
		if (@prot = 205) select @bits = @bits | 0x0020

		select @prot = null select @prot = protecttype from sysprotects where action = 235 and uid = user_id()
		if (@prot is null) select @prot = protecttype from sysprotects where action = 235 and uid = (select gid from sysusers where uid = user_id())
		if (@prot is null) select @prot = protecttype from sysprotects where action = 235 and uid = user_id('public')
		if (@prot = 205) select @bits = @bits | 0x0040

		select @prot = null select @prot = protecttype from sysprotects where action = 236 and uid = user_id()
		if (@prot is null) select @prot = protecttype from sysprotects where action = 236 and uid = (select gid from sysusers where uid = user_id())
		if (@prot is null) select @prot = protecttype from sysprotects where action = 236 and uid = user_id('public')
		if (@prot = 205) select @bits = @bits | 0x0080
	end









	if (lower(@mode) like '%sel%') begin
		set nocount off
		if (lower(@qual) like 'bin%')		/* For visual debugging */
			select 'profile bitmask' = convert(binary(4), @bits)
		else
			select 'profile bitmask' = @bits
	end 
go
/* End sp_MSdbuserprofile */

/*******************************************************************************/
print ''
print 'Creating sp_MSobjectprivs'
print ''
go

create proc sp_MSobjectprivs
	@objname varchar(92) = null,
	@mode varchar(10) = 'object',	/* or 'user' */

/*
 * Note:  This was expanded for 6.5 due to changes in sysprotects.columns usage, affecting
 * CPermission::ListPrivilegeColumns.  The following additional parameters are for this.
 */
	@objid int = null,				/* ID of the object we're querying */
	@srvpriv int = null,			/* privilege that we're querying for (e.g. select) */
	@prottype int = null,			/* Protect type, e.g. GRANT/REVOKE */
	@grantee varchar(30) = null		/* Grantee name. */
as
	/* If @objid is not null, this is for the new query for perm cols. */
	if (@objid is not null) begin
		select u.name, o.name, a = col_name(p.id, a.number), a.low, a.high, a.number
			from master.dbo.spt_values a, sysprotects p, sysobjects o, sysusers u 
			where p.id = @objid and p.action = @srvpriv and p.protecttype = @prottype
			and p.uid = user_id(@grantee)
			and p.columns != 0x01 and o.id = p.id and u.uid = o.uid 
				and convert(tinyint, substring(isnull(p.columns, 0x01), a.low, 1)) & 








					a.high <> 0	

					and a.type = 'P' and a.number <= (select count(*) from syscolumns where id = @objid) order by a
		return 0
	end

	set nocount on

	/* 
	 * To get around a 4.21 subquery bug where returning count(*) of 0 (for proc cols)
	 * causes the result set to return no rows, we need two passes; one to get the
	 * objects, and another to explicitly use a value (@cols) instead of a subquery.
	 */
	declare @id int, @uid int, @cols int
	select @id = null, @uid = null
	if (@mode like 'us%')
		select @uid = user_id(@objname)
	else
		select @id = object_id(@objname)
	if (@id is null and @uid is null) begin
		declare @42errmsg varchar(100)
		select @42errmsg = @objname + ' does not exist.'
		RAISERROR 4215001 @42errmsg
		return 1
	end

	/* Get a temp list of objects we're interested in.  Do not include repl_* users. */
	create table #objs(
		id					int				NOT NULL
	)
	insert #objs select distinct p.id from sysprotects p 
		where (@id is null or p.id = @id)
		and (@uid is null or p.uid = @uid)
		and p.action in (193, 195, 196, 197, 224, 26) and p.uid not in (16382, 16383)

	/* Temp table will hold output for final select */
	create table #output (
		action				int				NOT NULL,
		colid				int				NULL,
		uid					int				NOT NULL,
		protecttype			int				NOT NULL,
		id					int				NOT NULL
	)

	/* Use a "fake cursor" by deleting successive id's from #objs, as this must run on 4.21 */
	select @id = min(id) from #objs
	while (@id is not null) begin
		select @cols = count(*) from syscolumns c where c.id = @id
		insert #output select p.action, a.number, p.uid, p.protecttype, p.id
			from master.dbo.spt_values a, sysprotects p
			where convert(tinyint, substring( isnull(p.columns, 0x01), a.low, 1)) & a.high !=0
			and (p.id = @id)
			and (@uid is null or p.uid = @uid)
			and a.number <= @cols
			and a.type = 'P'
		/* Increment our "fake cursor" column and get the next one. */
		delete #objs where id = @id
		select @id = min(id) from #objs
	end

	/*
	 * Organize so that the non-collist privileges are returned first.. this allows
	 * scripting to combine them.  sysprotects.action is tinyint, so the hibyte won't conflict.
	 */

	update #output set action = action | 0x10000000 where colid <> 0

	/*
	 * Order output by uid so Public will script before other groups (we need to script privs for public before
	 * other groups, before users; otherwise sysprotects doesn't hold onto things right).  Sub-order is by object id
	 * so we know when we're done with one object and onto the next, then by protecttype to group all GRANTs and
	 * REVOKEs together, and lastly by action (including ORDER_ACTION_BIT so scripting can be more efficient)
	 * because we may have multiple rows for columns.
	 */
	set nocount off
	select p.action & ~convert(int, 0x10000000), 'column' = col_name(p.id, p.colid), p.uid, 'username' = user_name(p.uid), 
			p.protecttype, o.name, 'owner' = user_name(o.uid), p.id 
		from #output p, sysobjects o 
		where o.id = p.id 
		order by p.uid, p.id, p.protecttype, p.action
go
/* End sp_MSobjectprivs */

/*******************************************************************************/
/* Need to create the version proc here so we can set its category bit */
print ''
print 'Creating sp_MSSQLOLE65_version'
print ''
go

create procedure sp_MSSQLOLE65_version
as
	/* Values for this are same as @@microsoft_version:  0xrraaiibb (reserved, major, minor, build). */
	declare @i int
	select @i = 0x00063204	/* Must be in hex! */
	/* Select the numeric value, and a conversion to make it readable */
	select 'Microsoft SQLOLE Scripts' = @i, 'Version' = convert(binary(4), @i)
go

create procedure sp_MSSQLOLE_version
as











	/* For 4.21 or 6.0, we allow any version of SQLOLE to connect. */
	exec sp_MSSQLOLE65_version
go

/********************* Grant privileges *********************************/
print ''
print 'Granting execute permissions on procedures'

grant execute on sp_MShelpcolumns to public
grant execute on sp_MShelpindex to public
grant execute on sp_MShelptype to public
grant execute on sp_MSdependencies to public
grant execute on sp_MStablespace to public
grant execute on sp_MSindexspace to public
grant execute on sp_MSuniquename to public
grant execute on sp_MSkilldb to public
grant execute on sp_MSdbuserprofile to public
grant execute on sp_MSobjectprivs to public
grant execute on sp_MSloginmappings to public

















grant execute on xp_msver to public

grant execute on sp_MSSQLOLE_version to public
grant execute on sp_MSSQLOLE65_version to public
go

/********************** Verify object creation and update category bit for objects *********************************/

/***************************************************
 ******************* SQL60 *************************
 ***************************************************
 */

























































/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
print ''
print 'Dumping transaction log...'
print ''
go
dump tran master with no_log
go

/***************************************************
 ******************* Pre-SQL60 *********************
 ***************************************************
 */
print ''
print 'Setting category bit for objects'
print ''
go
sp_configure 'allow updates', 1
go
reconfigure with override
go

update master..sysobjects set category = category | 0x0002 where sysstat & 0x0f = 4 and name in (
	'sp_MShelpcolumns',
	'sp_MShelpindex',
	'sp_MShelptype',
	'sp_MSdependencies',
	'sp_MStablespace',
	'sp_MSindexspace',
	'sp_MSuniquename',
	'sp_MSkilldb',
	'sp_MSdbuserprofile',
	'sp_MSobjectprivs',
	'sp_MSloginmappings',
	'sp_MSSQLOLE_version',
	'sp_MSSQLOLE65_version'
	)
go

sp_configure 'allow updates', 0
go
reconfigure with override
go

/********************* Check for successful install *********************************/

set nocount on
create table #spmissing
(
	name				varchar(30)		NOT NULL,
	type				varchar(10)		NOT NULL
)
go

if not exists (select * from master.dbo.sysobjects where name = 'sp_MShelpcolumns' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MShelpcolumns', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MShelpindex' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MShelpindex', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MShelptype' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MShelptype', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdependencies' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MSdependencies', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MStablespace' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MStablespace', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSindexspace' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MSindexspace', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSuniquename' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MSuniquename', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSkilldb' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MSkilldb', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSdbuserprofile' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MSdbuserprofile', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSobjectprivs' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MSobjectprivs', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSloginmappings' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MSloginmappings', 'procedure')

if exists (select * from #spmissing)
begin
	print ''
	print ''
	print ' =====================  ERRORS!  ===================='
	print '       The following objects were not created.'
	print '       SQLOLE will not run against this server.'
	print ''
	select * from #spmissing
	drop procedure sp_MSSQLOLE65_version
end
go
drop table #spmissing
go
set nocount off
go

/************************** Check for admin/OM objects on a 4.21 server. *****************************/

print ''
print 'Pre-SQL60 server:  Setting category bit for objects supporting prior versions of GUI tools.'
print ''
go
sp_configure 'allow updates', 1
go
reconfigure with override
go

print ''
print 'SQL Administrator objects...'
print ''
go
update master..sysobjects set category = category | 0x0002 where sysstat & 0x0f = 4 and name in (
		'sp_MSdevice_list',
		'sp_MScheck_admin',
		'sp_MSdev_description',
		'sp_MSdev_databases',
		'sp_MSsys_monitor',
		'sp_MSnext_devnumber',
		'sp_MSdatabase_list',
		'sp_MSdatabase_avail',
		'sp_MSbackup_now',
		'sp_MSuser_list',
		'sp_MSmonitor',
		'sp_MSdb_devices',
		'sp_MSdb_options',
		'sp_MSdb_properties',
		'sp_MSdb_space',
		'sp_MSbackup_schedule',
		'sp_MScmd_permissions',
		'sp_MSuser_cmd_permissions',
		'sp_MSdb_options_bits',
		'sp_MSdev_mirror',
		'sp_MSdb_devices_segments',
		'sp_MSsegment_list',
		'sp_MSsegment_devices',
		'sp_MSseg_properties',
		'sp_MSseg_tables',
		'sp_MSthread_list',
		'sp_MShelpdb',
		'sp_MShelpremotelogin',
		'sp_MSlogdevice',
		'sp_MSAdmin_version',
		'sp_MShelpserver')
go
update master..sysobjects set category = category | 0x0002 where sysstat & 0x0f = 3 and name in (
		'MSscheduled_backups',
		'MSscheduled_backups_log',
		'MSsystem_monitor',
		'MSlast_monitor')
go

print ''
print 'SQL Object Manager objects...'
print ''
go
update master..sysobjects set category = category | 0x0002 where sysstat & 0x0f = 4 and name in (
		'sp_MScheck_OM',
		'sp_MSuser_info',
		'sp_MStable_properties',
		'sp_MScolumn_properties',
		'sp_MSobject_list',
		'sp_MSobjecttype_name',
		'sp_MStable_permissions',
		'sp_MSobject_dependencies',
		'sp_MSOM_version')
go

sp_configure 'allow updates', 0
go
reconfigure with override
go



if (object_id('sp_MSSQLOLE65_version') is not null) begin
	print ''
	print ''
	print ' Successful installation.'
	exec sp_MSSQLOLE65_version
end

/************* DUMP THE TRANSACTION LOG **************************************/
/* Comment this out if you don't want your log dumped.  If you rerun this    */
/* script periodically, you will run out of transaction log space.           */
print ''
print 'Dumping transaction log...'
print ''
go
dump tran master with no_log
go
checkpoint
go
/************* END DUMP THE TRANSACTION LOG **********************************/

