











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


















/* The following defines have been put here specifically for Sybase scripts */
/* This is because Sybase does not like large hex values which are sometimes */
/* used by scripts for MS SQL Server */



































		



























































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

dump tran master with no_log
go

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

dump tran master with no_log
go

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

dump tran master with no_log
go

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

dump tran master with no_log
go

if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSobjectprivs')
	drop procedure sp_MSobjectprivs
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_MStablekeys')
	drop procedure sp_MStablekeys
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MStablechecks')
	drop procedure sp_MStablechecks
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSchecktext')
	drop procedure sp_MSchecktext
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MStablerefs')
	drop procedure sp_MStablerefs
go

dump tran master with no_log
go

if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MScolbindings')
	drop procedure sp_MScolbindings
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_MSSQLOLE65_version')
	drop procedure sp_MSSQLOLE65_version
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 **********************************/


/********************* 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 (@tablename = '?')
	begin
		print ''
		print 'Usage:  sp_MShelpcolumns @tablename, @flags int = 0'
		print ' where @flags is a bitmask of:'
		print ' 0x0200	= No DRI (ignore Identity, 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

	if (object_id(@tablename) is null)
	begin
		RAISERROR 17977, @tablename
		return 1
	end

	set nocount on

	create table #sphelpcols
	(
		col_name		varchar (30),
		col_id			tinyint,
		col_type		smallint,
		col_len			tinyint,
		col_prec		tinyint	null,
		col_scale		tinyint	null,
		col_def			int		null,
		col_rul			int		null,
		col_null		bit,				/* status & 8 */
		col_identity	bit,				/* 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, prec, scale, cdefault, domain,
			convert(bit, status & 0x08), convert(bit, status & 0x80), null, null, type, 0, null, null, null, null
	from syscolumns
	where id = object_id(@tablename)

	/* If they have a column dridefault, then this will show up in syscolumns.cdefault.  Filter that; */
	/* we'll put in the real dridefault below if they don't have NoDRI set. */
	update #sphelpcols set col_def = null where 0 < (select count(*) from sysprocedures p, #sphelpcols c
		where p.id = c.col_def and p.sequence = 0 and p.status & 4096 = 4096)

	/* set col_rul = NULL if col_rul is really a check */
	update #sphelpcols set col_rul = null where 0 < (select count(*) from sysconstraints s, #sphelpcols c
		where s.constrid = c.col_rul and s.status & 0x80 = 0x80)

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

	/* Clear out identity if so requested.*/
	if (@flags is not null and @flags & 1073741824 != 0)
		update #sphelpcols set col_identity = 0


	/* 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 = 26 where col_type = 27
	/* 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 = 28
	/* nchar --> char */
	update #sphelpcols set col_type = 1 where col_type = 24
	/* nvarchar --> varchar */
	update #sphelpcols set col_type = 2 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 = 26 where col_basetype = 27
	/* 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 = 28
	/* nchar --> char */
	update #sphelpcols set col_type = 1 where col_type = 24
	/* nvarchar --> varchar */
	update #sphelpcols set col_type = 2 where col_type = 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 & 1024 != 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

		if (@flags & 2048 != 0)
		begin
			update #sphelpcols				/* No IDENTITY */
				set col_identity = 0
			update #sphelpcols				/* Convert numeric and decimal with prec < 8 to real */
				set col_type = 23 where col_type in (10, 26) and col_prec < 8
			update #sphelpcols				/* Convert numeric and decimal with prec >= 8 to float */
				set col_type = 8 where col_type in (10, 26)	and col_prec >= 8
		end

	end

	/* Get seed and increment for the IDENTITY column, if any. */
	update #sphelpcols set col_seed = 1, col_increment = 1 where col_identity != 0

	if (@flags is null or @flags & 512 = 0) begin
		update #sphelpcols set col_dridefname = object_name(s.cdefault) from syscolumns s, sysprocedures p, #sphelpcols c
			where s.id = object_id(@tablename) and s.colid = c.col_id and p.id = s.cdefault 
				and p.sequence = 0 and p.status & 4096 = 4096

		/* Determine if the column is in the primary key */
		declare @ii int, @indid int, @keycnt int
		select @indid = indid, @keycnt = keycnt from sysindexes i where i.id = object_id(@tablename) and i.status2 & 0x02 <> 0 and i.status & 2048 <> 0
		if (@indid is not null) begin
			if (@indid <> 1) select @keycnt = @keycnt - 1	/* keycnt includes RID if nc index */
			select @ii = 1
			while @ii <= @keycnt begin
				update #sphelpcols set col_flags = col_flags | 0x04 where index_col(@tablename, @indid, @ii) = col_name
				select @ii = @ii + 1
			end
		end

		/* We'll put out the default text if it's all in one row (most likely); otherwise leave it */
		/* blank for refetching in its entirety via sp_helptext. */
		/* In Sybase the syscomments text also has the word "DEFAULT" used during the table declaration - we strip it out */
		update #sphelpcols set col_drideftext = ltrim(stuff(t.text,1,7,"")) from syscomments t, syscolumns s, sysprocedures p, #sphelpcols c
			where s.id = object_id(@tablename) and s.colid = c.col_id and t.id = s.cdefault 
				and 1 = (select count(*) from syscomments where syscomments.id = s.cdefault)
				and p.id = s.cdefault and p.sequence = 0 and p.status & 4096 = 4096

	end

	/* Clear out precision/scale for nonnumerics */
	update #sphelpcols set col_prec = null, col_scale = null where col_basetype not in (10, 26)


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

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


/************* 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_MShelpindex'
print ''
go


create procedure sp_MShelpindex
@tablename varchar(92), @indexname varchar(30) = null
as

	select i.name, i.status, i.indid, 0,
		index_col(@tablename, i.indid, 1), index_col(@tablename, i.indid, 2),
		index_col(@tablename, i.indid, 3), index_col(@tablename, i.indid, 4),
		index_col(@tablename, i.indid, 5), index_col(@tablename, i.indid, 6),
		index_col(@tablename, i.indid, 7), index_col(@tablename, i.indid, 8),
		index_col(@tablename, i.indid, 9), index_col(@tablename, i.indid, 10),
		index_col(@tablename, i.indid, 11), index_col(@tablename, i.indid, 12),
		index_col(@tablename, i.indid, 13), index_col(@tablename, i.indid, 14),
		index_col(@tablename, i.indid, 15), 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

	
	/* 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
			RAISERROR 17977, @typename
			return 1
		end
	end

	create table #sphelptype1 (
		dt_name		varchar(30)	null,
		dt_max		int		null,
		dt_nulls	int		null,
		dt_numeric	int		null,
		dt_ident	int		null
	)

	/* 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. */
		set nocount on
		insert #sphelptype1 (dt_name, dt_max, dt_nulls, dt_numeric, dt_ident)
			select t.name,
			y.length,
			t.allownulls,
			0, 0
			from systypes t, systypes y
			where t.usertype < 100 and t.usertype not in (15,27,14,13,17,28) and (@typename is null or t.name like @typename)
			and y.usertype =* t.usertype and y.usertype in (1,2,3,4)
		
		update #sphelptype1 set dt_numeric = 1 from systypes t where dt_name = t.name and t.usertype in (26,10)
		update #sphelptype1 set dt_ident = 1 from systypes t where dt_name = t.name and t.usertype in (26,7,10,6,5)

		select 	SystemDatatypeName = dt_name,
				ifvarlen_max = dt_max,
				allownulls = dt_nulls,
				isnumeric =  dt_numeric,
				allowidentity = dt_ident
		from #sphelptype1
		order by SystemDatatypeName
	end

	/* Need a temp table so we can ownerqualify nonNULL rules/defaults. */
	create table #sphelptype2 (
		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,
		dt_len		int		null,
		dt_prec		int		null,
		dt_scale	int		null,
		dt_ident	int		null
	)

	if (@flags is null or @flags = 'uddt')
	begin
		set nocount on
		insert #sphelptype2 (dt_usertype, dt_basetype, dt_rul, dt_def, dt_flags, dt_len, dt_prec, dt_scale, dt_ident)
			select t.usertype,
			t.usertype,
			t.domain, t.tdefault, 0, 0,null,null,0
			from systypes t
			where t.usertype > 99 and (@typename is null or t.name like @typename)

		update #sphelptype2 set dt_basetype = b.usertype from systypes t, systypes b 
			where  t.usertype = dt_usertype and b.type = t.type and b.usertype < 100 and b.usertype not in (18, 80, 24, 25)

		/* Make a nice, presentable qualified rule/default name for those which are non-null */
		update #sphelptype2 set dt_defname = user_name(d.uid) + '.' + d.name
				from #sphelptype2 c, sysobjects d where c.dt_def is not null and d.id = c.dt_def
		update #sphelptype2 set dt_rulname = user_name(r.uid) + '.' + r.name
				from #sphelptype2 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 #sphelptype2 set dt_flags = dt_flags | 0x1 where dt_basetype in (1,2,3,4)
		update #sphelptype2 set dt_flags = dt_flags | 0x2 where dt_basetype in (10, 24)

		update #sphelptype2 set dt_len = systypes.length from systypes where dt_basetype in (1,2,3,4) and systypes.usertype > 99 and (@typename is null or systypes.name like @typename)
				and dt_usertype = systypes.usertype
			

		update #sphelptype2 set dt_prec = systypes.prec from systypes where dt_basetype in (10, 26) and systypes.usertype > 99 and (@typename is null or systypes.name like @typename)
				and dt_usertype = systypes.usertype

		update #sphelptype2 set dt_scale = systypes.scale from systypes where dt_basetype in (10, 26) and systypes.usertype > 99 and (@typename is null or systypes.name like @typename)
				and dt_usertype = systypes.usertype

		update #sphelptype2 set dt_ident = 1	from systypes where dt_basetype in (26,7,10,6,5) and systypes.usertype > 99 and (@typename is null or systypes.name like @typename)
				and dt_usertype = systypes.usertype	and systypes.scale = 0

		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 = dt_len,
				nullable = t.allownulls,
				dt_prec = dt_prec,
				dt_scale = dt_scale,
				dt_flags,
				allowidentity = dt_ident



			from systypes t, #sphelptype2 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 */

/************* 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_MSdependencies'
print ''
go

create procedure sp_MSdependencies
@objname varchar(92) = null, @objtype int = null, @flags int = 509
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 this proc is called in a tight loop, it tends to fill up the log in a small tempdb too fast 
	 * for the trunc. log on chkpt thread to keep up.  So help it out here.
	 */

	dump tran tempdb with no_log


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

	if (@flags & 2097152 <> 0)
		select @flags = (@flags & ~convert(int, 511)) | power(2, 3)


	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)

	/* 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 #t3 (tid int)
	create clustered index #ci_t3 on #t3(tid) with allow_dup_row
	create table #t4 (tid int)
	create clustered index #ci_t4 on #t4(tid) with allow_dup_row

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

	

	/*
	 * 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 (@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, 0 from sysobjects o
			where ((power(2, o.sysstat & 0x0f) & 511) <> 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 = 0 from sysobjects o where id = object_id(@objname)
			if (@curid is null)
			begin
				RAISERROR 17977, @objname
				return 1
			end
			if (@flags & 524288  = 0)
				select @delinputobj = @curid
		end

		set nocount on
		if (@curid is null)
			insert #t1 (pid, ptype, pcat) select o.id, o.sysstat & 0x0f, 0 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, 0 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 & 262144 != 0)
		begin
			if (@flags & 2097152 = 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, 0 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, 0
					from #t1 t, sysdepends d, sysobjects o
					where t.bDone is null and d.depid = t.pid and d.id = o.id
			end

			/* Object --> sysreferences children (FK tables referencing this one) */
			insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
				select distinct t.pid, t.ptype, t.pcat, r.tableid, o.sysstat & 0x0f, 0
				from #t1 t, sysreferences r, sysobjects o
				where t.bDone is null and r.reftabid = t.pid and r.tableid = o.id

		end else begin
			if (@flags & 2097152 = 0) 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, 0
							  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, 0
					from #t1 t, sysdepends d, sysobjects o
					where t.bDone is null and d.id = t.pid and d.depid = o.id
			end

			/* Object --> sysreferences parents (PK/UQ tables referenced by one) */
			insert #t2 (tid, ttype, tcat, pid, ptype, pcat)
				select distinct t.pid, t.ptype, t.pcat, r.reftabid, o.sysstat & 0x0f, 0
				from #t1 t, sysreferences r, sysobjects o
				where t.bDone is null and r.tableid = t.pid and r.reftabid = 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 & 1048576 <> 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.
	 */

	truncate table #t3
	insert #t3 select tid from #t1 where tid is not null
	update statistics #t3 #ci_t3
	insert #t1 (tid, ttype, tcat, bDone) select distinct pid, ptype, pcat, 0 from #t1 t
		where t.pid is not null and not exists (select * from #t3 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 & 262144 = 0)
		delete #t1 where ptype = 8


	truncate table #t3
	insert #t3 select tid from #t1 where tid is not null and pid is not null
	update statistics #t3 #ci_t3
	delete #t1 where #t1.tid is null or #t1.tid = #t1.pid 
		or (#t1.pid is null and exists (select * from #t3 where tid = #t1.tid))



	/*
	 * 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) != 0)
			insert #t2 (tid, ttype, tcat)
				select o.id, o.sysstat & 0x0f, 0 from sysobjects o
				where o.sysstat in (7)
				and o.id not in (select constrid from sysconstraints where status & 0x80 <> 0) 

		if (@flags & power(2, 6) != 0)
			insert #t2 (tid, ttype, tcat)
				select o.id, o.sysstat & 0x0f, 0 from sysobjects o
				where o.sysstat in (6)
				and o.id not in (select id from sysprocedures where sequence = 0 and status & 4096 <> 0) 
	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)
						and not exists (select * from sysconstraints where constrid = s.domain)
		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)
						and s.tdefault not in (select id from sysprocedures 
												where sequence = 0 and status & 4096 <> 0) 

			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)
						and s.cdefault not in (select id from sysprocedures 
												where sequence = 0 and status & 4096 <> 0)
		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 & 511 != 511 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 & 262144 != 0) begin
			truncate table #t3
			insert #t3 select pid from #t1 where pid is not null and bDone = 0
			update statistics #t3 #ci_t3
			update #t1 set bDone = @curid where bDone = 0 and tid not in (select tid from #t3)
		end else begin
			truncate table #t3
			truncate table #t4
			insert #t3 select tid from #t1 where bDone = 0				/* Parents not yet done */
			update statistics #t3 #ci_t3
			insert #t4 select tid from #t1								/* TIDs with (parents not yet done) */
				where pid is not null and pid in (select tid from #t3)
			update statistics #t4 #ci_t4
			update #t1 set #t1.bDone = @curid where #t1.bDone = 0 		/* TIDs who are not (TIDs with (parents not yet done)) */
				and not exists (select * from #t4 where tid = #t1.tid)
		end



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

	if exists (select * from #t1 where bDone = 0) begin
		/* Raise a circular dependency error here */
		return 1
	end


	/*
	 * 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 & 262144 = 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 & 131072 != 0) begin
		select @curid = max(bDone) + min(bDone) from #t1
		update #t1 set bDone = convert(smallint, @curid) - bDone
	end
	if (@flags & 65536 != 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_MStablekeys'
print ''
go



create procedure sp_MStablekeys
@tablename varchar(92) = null, @colname varchar(30) = null, @type int = 0x0e, @keyname varchar(30) = null
as
	/* This proc returns the table's DRI keys.  @type is the type(s) of key(s) to return. */
	/* Make sure @type is only the key types (DRI_PRIMARYKEY, DRI_UNIQUE, DRI_REFERENCE). */
	select @type = @type & 0x0e

	set nocount on
	create table #spkeys
	(
		cType				tinyint,			/* key Type */
		cName				varchar(30),		/* key Name */
		cFlags				int NULL,			/* e.g., 1 = clustered for PK/Unique */
		cColCount			int NULL,			/* number of columns (or column pairs) in the key */
		cFillFactor			tinyint NULL,		/* Fill factor of index creation */
		cRefTable			varchar(64) NULL,	/* owner-qual Referenced table name for FKs */
		cRefKey				varchar(32) NULL,	/* name of referenced key in referenced table */
		cKeyCol1			varchar(32) NULL,	/* key column names */
		cKeyCol2			varchar(32) NULL,	/* key column names */
		cKeyCol3			varchar(32) NULL,	/* key column names */
		cKeyCol4			varchar(32) NULL,	/* key column names */
		cKeyCol5			varchar(32) NULL,	/* key column names */
		cKeyCol6			varchar(32) NULL,	/* key column names */
		cKeyCol7			varchar(32) NULL,	/* key column names */
		cKeyCol8			varchar(32) NULL,	/* key column names */
		cKeyCol9			varchar(32) NULL,	/* key column names */
		cKeyCol10			varchar(32) NULL,	/* key column names */
		cKeyCol11			varchar(32) NULL,	/* key column names */
		cKeyCol12			varchar(32) NULL,	/* key column names */
		cKeyCol13			varchar(32) NULL,	/* key column names */
		cKeyCol14			varchar(32) NULL,	/* key column names */
		cKeyCol15			varchar(32) NULL,	/* key column names */
		cKeyCol16			varchar(32) NULL,	/* key column names */
		cRefCol1			varchar(32) NULL,	/* referenced column names */
		cRefCol2			varchar(32) NULL,	/* referenced column names */
		cRefCol3			varchar(32) NULL,	/* referenced column names */
		cRefCol4			varchar(32) NULL,	/* referenced column names */
		cRefCol5			varchar(32) NULL,	/* referenced column names */
		cRefCol6			varchar(32) NULL,	/* referenced column names */
		cRefCol7			varchar(32) NULL,	/* referenced column names */
		cRefCol8			varchar(32) NULL,	/* referenced column names */
		cRefCol9			varchar(32) NULL,	/* referenced column names */
		cRefCol10			varchar(32) NULL,	/* referenced column names */
		cRefCol11			varchar(32) NULL,	/* referenced column names */
		cRefCol12			varchar(32) NULL,	/* referenced column names */
		cRefCol13			varchar(32) NULL,	/* referenced column names */
		cRefCol14			varchar(32) NULL,	/* referenced column names */
		cRefCol15			varchar(32) NULL,	/* referenced column names */
		cRefCol16			varchar(32) NULL,	/* referenced column names */
		cIndexID			int	NULL			/* ID of this key's index, if PK/UQ */
	)

	declare @cType int, @cName varchar(30), @cFlags int, @cRefTable varchar(64), @fillfactor tinyint
	declare @objid int, @constid int, @indid int, @keycnt int, @q1 varchar(255), @q2 varchar(255)

	/* First see if @keyname was defined, and override @tablename and @type if so. */
	if (@keyname is not null)
	begin
		select @objid = id from sysindexes where name = @keyname
		if (@objid is null)
			select @objid = tableid from sysconstraints where constrid = object_id(@keyname)
	
		if (@objid is null)	begin
			RAISERROR 17977, @keyname
			return 1
		end
		select @type = 0
		if exists (select * from sysindexes where name = @keyname and status & 2048 <> 0)
			select @type = 0x2
		if exists (select * from sysindexes where name = @keyname and status & 2048 = 0 
						and status2 & 0x2 <> 0)
			select @type = 0x4
		if exists (select * from sysconstraints where constrid = object_id(@keyname))
			select @type = @type | 0x8


		/* Now get the tablename for the index_col below */
		select @tablename = user_name(uid) + '.' + name from sysobjects where id = @objid
	end else begin
		/* Want all keys for this table (of @type type). */
		select @objid = object_id(@tablename)
		if (@objid is null)	begin
			RAISERROR 17977, @tablename
			return 1
		end
		if exists (select id from sysobjects where id = @objid and sysstat & 0x0f not in (1, 3))	begin
			RAISERROR 17761, @tablename
			return 1
		end
		if @colname is not null and not exists (select * from syscolumns where id = @objid and name = @colname) begin
			RAISERROR 17977, @colname
			return 1
		end
	end



	/* First do Primary key and Unique key */
	if (@type & 0x2 <> 0) begin
		insert #spkeys 
		select 1, name, 0, keycnt, 0, null, null,
			index_col(@tablename, indid, 1),index_col(@tablename, indid, 2),index_col(@tablename, indid, 3),index_col(@tablename, indid, 4),
			index_col(@tablename, indid, 5),index_col(@tablename, indid, 6),index_col(@tablename, indid, 7),index_col(@tablename, indid, 8),
			index_col(@tablename, indid, 9),index_col(@tablename, indid, 10),index_col(@tablename, indid, 11),index_col(@tablename, indid, 12),
			index_col(@tablename, indid, 13),index_col(@tablename, indid, 14),index_col(@tablename, indid, 15),index_col(@tablename, indid, 16),
			null, null, null, null,	null, null, null, null,	null, null, null, null,	null, null, null, null, indid
		from sysindexes where id = @objid and status2 & 0x2 <> 0 and status & 2048 <> 0
	end

	if (@type & 0x4 <> 0) begin
		insert #spkeys 
		select 2, name, 0, keycnt, 0, null, null,
			index_col(@tablename, indid, 1),index_col(@tablename, indid, 2),index_col(@tablename, indid, 3),index_col(@tablename, indid, 4),
			index_col(@tablename, indid, 5),index_col(@tablename, indid, 6),index_col(@tablename, indid, 7),index_col(@tablename, indid, 8),
			index_col(@tablename, indid, 9),index_col(@tablename, indid, 10),index_col(@tablename, indid, 11),index_col(@tablename, indid, 12),
			index_col(@tablename, indid, 13),index_col(@tablename, indid, 14),index_col(@tablename, indid, 15),index_col(@tablename, indid, 16),
			null, null, null, null,	null, null, null, null,	null, null, null, null,	null, null, null, null, indid
		from sysindexes where id = @objid and status2 & 0x2 <> 0 and status & 2048 = 0
	end

	/* Now set cFlags */
	update #spkeys set cFlags = 131072 from sysindexes i, #spkeys k where i.name = k.cName and i.id = @objid and i.status2 & 8 <> 0
	update #spkeys set cFlags = cFlags | 1 where cIndexID = 1

	update #spkeys set cColCount = cColCount - 1 where cIndexID <> 1

	/* Now do Foreign Keys */
	if (@type & 0x8 <> 0) begin
		insert #spkeys
		select 3, object_name(r.constrid), 0, r.keycnt, 0, user_name(o.uid) + '.' + o.name, i.name, 
			col_name(r.tableid, r.fokey1),col_name(r.tableid, r.fokey2),col_name(r.tableid, r.fokey3),col_name(r.tableid, r.fokey4),
			col_name(r.tableid, r.fokey5),col_name(r.tableid, r.fokey6),col_name(r.tableid, r.fokey7),col_name(r.tableid, r.fokey8),
			col_name(r.tableid, r.fokey9),col_name(r.tableid, r.fokey10),col_name(r.tableid, r.fokey11),col_name(r.tableid, r.fokey12),
			col_name(r.tableid, r.fokey13),col_name(r.tableid, r.fokey14),col_name(r.tableid, r.fokey15),col_name(r.tableid, r.fokey16),
			col_name(r.reftabid, r.refkey1),col_name(r.reftabid, r.refkey2),col_name(r.reftabid, r.refkey3),col_name(r.reftabid, r.refkey4),
			col_name(r.reftabid, r.refkey5),col_name(r.reftabid, r.refkey6),col_name(r.reftabid, r.refkey7),col_name(r.reftabid, r.refkey8),
			col_name(r.reftabid, r.refkey9),col_name(r.reftabid, r.refkey10),col_name(r.reftabid, r.refkey11),col_name(r.reftabid, r.refkey12),
			col_name(r.reftabid, r.refkey13),col_name(r.reftabid, r.refkey14),col_name(r.reftabid, r.refkey15),col_name(r.reftabid, r.refkey16),
			null
		from sysreferences r, sysobjects o, sysindexes i
		where r.tableid = @objid and o.id = r.reftabid and i.id = r.reftabid and i.indid = r.indexid
	end

	/* Now output the data */
	set nocount off
	if @colname is null
		select * from #spkeys order by cType, cName
	else
		select * from #spkeys where
			cKeyCol1 = @colname or cKeyCol2 = @colname or cKeyCol3 = @colname or cKeyCol3 = @colname or
			cKeyCol5 = @colname or cKeyCol6 = @colname or cKeyCol7 = @colname or cKeyCol8 = @colname or
			cKeyCol9 = @colname or cKeyCol10 = @colname or cKeyCol11 = @colname or cKeyCol12 = @colname or
			cKeyCol13 = @colname or cKeyCol14 = @colname or cKeyCol15 = @colname or cKeyCol16 = @colname
	 	order by cType, cName
go
/* End sp_MStablekeys */

/************* 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_MStablechecks'
print ''
go


create procedure sp_MStablechecks
	@tablename varchar(92), @flags int = null
as
	declare @id int
	select @id = object_id(@tablename)
	if (@id is null) begin
		RAISERROR 17977, @tablename
		return 1
	end

	/* We will ignore the flag parameter for sybase version of this proc.*/
	create table #sphelpcheck
	(
		id int null,
		text varchar(255) null
	)

	insert #sphelpcheck 
		select distinct t.id, t.text
		from syscomments t, sysconstraints c
		where t.id in (select constrid from sysconstraints where tableid = @id and status & 0x80 <> 0)
			and t.id = c.constrid
			and 1 = (select count(*) from syscomments where id = t.id)


	insert #sphelpcheck 
		select distinct t.id, null
		from syscomments t, sysconstraints c
		where t.id in (select constrid from sysconstraints where tableid = @id and status & 0x80 <> 0)
			and t.id = c.constrid
			and 1 < (select count(*) from syscomments where id = t.id)

	/* We'll put out the check text if it's all in one row (most likely); otherwise leave it */
	/* blank for refetching in its entirety via sp_helptext. */
	select object_name(c.id), ltrim(stuff(c.text,1,5 + charindex('CHECK', UPPER(c.text)),'')), 0
	from #sphelpcheck c
go
/* End sp_MStablechecks */

/************* 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_MSchecktext'
print ''
go

create procedure sp_MSchecktext
	@rulename varchar(92)
as
	declare @objid int
	select @objid = object_id(@rulename)
	if (@objid is null) begin
		RAISERROR 17977, @rulename
		return 1
	end

	create table #spchecktext
	(
		text varchar(255) null
	)

	insert #spchecktext
		select ltrim(stuff(c.text,1,5 + charindex('CHECK', UPPER(c.text)),''))
		from syscomments c
		where c.id = @objid and c.colid = 1

	insert #spchecktext
		select c.text
		from syscomments c
		where c.id = @objid and c.colid > 1

	select text, 0, 'C' from #spchecktext
go
/* End sp_MSchecktext */


/************* 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_MStablerefs'
print ''
go

create procedure sp_MStablerefs
	@tablename varchar(92),					/* table whose references are being evaluated */
	@type varchar(20) = 'actualtables',		/* '[actual | all][tables | keys]'; all candidates, or only those actually referenced */
	@direction varchar(20) = 'primary',		/* look for references to 'primary' or from 'foreign' */
	@reftable varchar(92) = null			/* limit scope to this table, if non-null */
as
	if (@tablename = '?') begin
		PRINT ''
		PRINT 'sp_MStablerefs:'
		PRINT '@tablename varchar(92),					/* table whose references are being evaluated */'
		PRINT '@type varchar(92) = [actualtables],		/* [[actual | all][tables | keys]]; all candidates, or only those actually referenced */'
		PRINT '@direction varchar(20) = [primary],		/* look for references to [primary] or from [foreign] */'
		PRINT '@reftable varchar(92) = null				/* limit scope to this table, if non-null */'
		return 0
	end

	declare @id int, @refid int
	select @id = object_id(@tablename), @refid = object_id(@reftable)
	if (@tablename is not null and @id is null) begin
		RAISERROR 17977, @tablename
		return 1
	end
	if (@reftable is not null and @refid is null) begin
		RAISERROR 17977, @reftable
		return 1
	end

	create table #sprefs (
		id int, 				/* id of reftable */
		name varchar(30) null,	/* name of index */
		referenced bit			/* well, is it? */
	)

	declare @dotables bit, @doall bit, @doprimary bit
	select @dotables = 0
	if (@type like 'allt%' or @type like 'actualt%')
		select @dotables = 1
	select @doall = 0
	if (@type like 'all%')
		select @doall = 1
	select @doprimary = 0
	if (@direction like 'p%')
		select @doprimary = 1

	if (@dotables = 1) begin
		if (@doprimary = 1) begin
			/* Get all candidate tables (those with Primary/Unique keys in sysconstraints). */
			insert #sprefs
				select distinct id, 'N/A', 0 from sysindexes where status2 & 0x2 <> 0

			/* Update the referenced bit if this table references it. */
			update #sprefs set referenced = 1
				where id in (select reftabid from sysreferences where tableid = @id)
		end else begin
			/* All user tables are foreign-key candidate tables. */
			insert #sprefs
				select distinct id, 'N/A', 0 from sysobjects where sysstat & 0x0f = 3 

			/* Update the referenced bit if it references this table. */
			update #sprefs set referenced = 1
				where id in (select tableid from sysreferences where reftabid = @id)
		end	/* direction */

	end else begin	/* keys */
		if (@doprimary = 1) begin
			/* Get all candidate tables (those with Primary/Unique keys in sysconstraints) and the keys. */
			insert #sprefs
				select distinct id, name, 0 from sysindexes where status2 & 0x2 <> 0

			/* Follow r.rkeyindid back to sysindexes to get the name and then 'rconstid' to see if this table references it. */
			update #sprefs set referenced = 1 from #sprefs s, sysreferences r, sysindexes i
				where r.tableid = @id
				and i.id = r.reftabid and i.status2 & 0x2 <> 0
				and s.name = i.name

		end else begin
			/* First add tables with FOREIGN keys defined. */
			insert #sprefs
				select distinct tableid, object_name(constrid), 0 from sysconstraints where status & 0x40 <> 0

			/* All user tables are foreign-key candidate tables, so add any tables we haven't yet, if @doall. */
			/* (This would be used for 'push' key definition; defining FK's from the standpoint of the PK table). */
			insert #sprefs
				select distinct id, null, 0 from sysobjects where sysstat & 0x0f = 3 
					and @doall = 1 and id not in (select id from #sprefs)

			/* Update the referenced bit if it references this table. */
			update #sprefs set referenced = 1
				where object_id(name) in (select constrid from sysreferences where reftabid = @id)
		end	/* direction */
	end	/* tables or keys */
	
	/* Exclude system and MS-internal objects, or tables/keys that aren't in the @reftable we want, if any specified. */
	delete #sprefs where id in (select id from sysobjects where sysstat & 0x0f <> 3)
			or (@refid is not null and id != @refid)

	/* Output */
	if (@tablename is not null)
		select candidate_table = user_name(o.uid) + '.' + object_name(o.id), 
			candidate_key = s.name, s.referenced
			from #sprefs s, sysobjects o where o.id = s.id and (@doall = 1 or s.referenced = 1)
			order by object_name(o.id), user_name(o.uid), s.name
	else
		select candidate_table = user_name(o.uid) + '.' + object_name(o.id), 
			candidate_key = s.name
			from #sprefs s, sysobjects o where o.id = s.id
			order by object_name(o.id), user_name(o.uid), s.name
go
/* End sp_MStablerefs */

/************* 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_MSobjectprivs'
print ''
go

create proc sp_MSobjectprivs
	@objname varchar(92) = null, @mode varchar(10) = 'object'	/* or 'user' */
as
	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
		RAISERROR 17977, @objname
		return 1
	end

	/* Get a temp list of objects we're interested in.  Do not include repl_* users. */
	create table #objs(
		id int
	)
	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, 151) and p.uid not in (16382, 16383)

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

	/* 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 = 26 where action = 151
	update #output set action = action | 268435456 where colid <> 0
	update #output set protecttype = 205 where protecttype < 2
	update #output set protecttype = 206 where protecttype = 2

	/*
	 * 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, 268435456), '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 */

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

/*******************************************************************************/
print ''
print 'Creating sp_MScolbindings'
print ''
go

create proc sp_MScolbindings
	@tablename varchar(92) = null					/* table whose column bindings are to be found */
as
	declare @id int

	select @id = object_id(@tablename)
	if (@id = null) begin
		RAISERROR 17977, @tablename
		return 1
	end

	set nocount on
	create table #spcolbnd
	(
		cCol				varchar(32) NULL,	/* column name */
		cRuleID				int			NULL,	/* id of rule bound to col */
		cRuleName			varchar(32) NULL,	/* Rule name */
		cRuleOwn			varchar(32) NULL,	/* owner of rule */
		cDefID				int			NULL,	/* id of default bound to col */
		cDefName			varchar(32) NULL,	/* default name */
		cDefOwn				varchar(32) NULL	/* owner of default */
	)

	insert #spcolbnd
		select c.name, r.id, r.name, user_name(r.uid), d.id, d.name, user_name(d.uid) from syscolumns c, sysobjects r, sysobjects d 
		where c.id = @id and r.id =* c.domain and d.id =* c.cdefault 
		order by c.name

	delete #spcolbnd where cRuleName = null and cDefName = null
	delete #spcolbnd where cDefID in
		(select id from sysprocedures where sequence = 0 and status & 4096 <> 0)
	delete #spcolbnd where cRuleID in
		(select constrid from sysconstraints where status & 0x80 <> 0)

	set nocount off

	select cCol, null, cRuleName, cRuleOwn, cDefName, cDefOwn, null 
		from #spcolbnd
go
/* End sp_MScolbindings */

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


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







	/* 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 = 0
		if (suser_id() = 1) begin
			/* sa has everything */
			select @bits = 0x7
		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 = 1 or @prot = 0) select @bits = @bits | 0x2

			select @prot = null select @prot = protecttype from sysprotects where action = 224 and uid = user_id() and id = object_id('sp_addumpdevice')
			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_addumpdevice')
			if (@prot is null) select @prot = protecttype from sysprotects where action = 224 and uid = user_id('public') and id = object_id('sp_addumpdevice')
			if (@prot = 1 or @prot = 0) select @bits = @bits | 0x4
		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 = 255
	end else begin
		/* Not dbo so get individual privileges */
		select @bits = 0, @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 = 1 or @prot = 0) select @bits = @bits | 0x2

		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 = 1 or @prot = 0) select @bits = @bits | 0x4

		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 = 1 or @prot = 0) select @bits = @bits | 0x8

		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 = 1 or @prot = 0) select @bits = @bits | 0x10

		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 = 1 or @prot = 0) select @bits = @bits | 0x20

		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 = 1 or @prot = 0) select @bits = @bits | 0x40

		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 = 1 or @prot = 0) select @bits = @bits | 0x80
	end

	if (lower(@mode) like '%sel%') begin
		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 */



/*******************************************************************************/
/* Need to create the version proc here so we can set its category bit */
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 = 406018	/* Must be in hex (except for sybase where you translate hex to int)! */
	/* Select the numeric value, and a conversion to make it readable */
	select 'Microsoft SQLOLE Scripts' = @i, 'Version' = inttohex(406018)
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_MSobjectprivs to public
grant execute on sp_MStablekeys to public
grant execute on sp_MStablechecks to public
grant execute on sp_MSchecktext to public
grant execute on sp_MStablerefs to public
grant execute on sp_MScolbindings to public
grant execute on sp_MSdbuserprofile 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 'Checking objects created by script.'
print ''
go

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

set nocount on
create table #spmissing
(name varchar(30),
 type varchar(10)
)
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_MSobjectprivs' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MSobjectprivs', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MStablekeys' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MStablekeys', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MStablechecks' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MStablechecks', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MSchecktext' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MSchecktext', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MStablerefs' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MStablerefs', 'procedure')
if not exists (select * from master.dbo.sysobjects where name = 'sp_MScolbindings' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MScolbindings', '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_MSSQLOLE65_version' and sysstat & 0x0f = 4)
	insert into #spmissing values('sp_MSSQLOLE65_version', '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

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.           */
dump tran master with no_log
go
checkpoint
go
/************* END DUMP THE TRANSACTION LOG **********************************/

