
/**********************************************************************
 * Microsoft SQL Server SQL-DMO scripts, version 7.0.
 * Date created:  3/17/96.
 * Date modified: 2/26/97.
 * Copyright Microsoft Corporation, 1992-1997
 **********************************************************************
 *	This script must be installed for the SQL-DMO (SQLDMO) objects and
 *	the SQL Enterprise Manager to run against a Microsoft SQL Server.
 **********************************************************************/
















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






/* For fetching from cursor */

















































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






/* Internal #defines for SQLDMO. */

/* Current SQLDMO version.  Also must be available to SQLDMO.odl. */



/* Make sure we use our own; shield SQLDMO from Starfighter changes. */







/* Our max name lengths. */
/* !! All lengths retrieved from server must be rounded UP TO THE NEXT 4-bytes. !! */
/* There must be at least one byte more than server max length, for the NULL byte. */

/* 7.0 Identifier length has been increased from 30 to 128 characters */
















/* Keep for Replication, but changed to SYSNAME length */
































/* 7.0 */



/* Reserve enough space for [] quoting character, and escape character in identifier */




















/* Specail Identifier length for mapping table when scripting from 6.x server, this is the sysname length for 6.x server */





use master
go

print ''
print 'Creating SQLDMO stored procedures...'
print ''

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

/********************** Include individual components definitions *********************************/




















































/* From perm.h */














/* Roles */


















/* File Growth */


/* Max. Column length */



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

if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MShelpcolumns')
	drop procedure sp_MShelpcolumns
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MShelpindex')
	drop procedure sp_MShelpindex
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MShelptype')
	drop procedure sp_MShelptype
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSdependencies')
	drop procedure sp_MSdependencies
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MStablespace')
	drop procedure sp_MStablespace
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSindexspace')
	drop procedure sp_MSindexspace
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSuniquename')
	drop procedure sp_MSuniquename
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSkilldb')
	drop procedure sp_MSkilldb
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSobjectprivs')
	drop procedure sp_MSobjectprivs
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSloginmappings')
	drop procedure sp_MSloginmappings
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MStablekeys')
	drop procedure sp_MStablekeys
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MStablechecks')
	drop procedure sp_MStablechecks
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MStablerefs')
	drop procedure sp_MStablerefs
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSsettopology')
	drop procedure sp_MSsettopology
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSmatchkey')
	drop procedure sp_MSmatchkey
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSforeachdb')
	drop procedure sp_MSforeachdb
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSforeachtable')
	drop procedure sp_MSforeachtable
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSforeach_worker')
	drop procedure sp_MSforeach_worker
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSSQLOLE_version')
	drop procedure sp_MSSQLOLE_version
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSSQLOLE65_version')
	drop procedure sp_MSSQLOLE65_version
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSSQLDMO70_version')
	drop procedure sp_MSSQLDMO70_version
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSscriptdatabase')
	drop procedure sp_MSscriptdatabase
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSscriptdb_worker')
	drop procedure sp_MSscriptdb_worker
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSdbuseraccess')
	drop procedure sp_MSdbuseraccess
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSdbuserpriv')
	drop procedure sp_MSdbuserpriv
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MShelpfulltextindex')
	drop procedure sp_MShelpfulltextindex
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MShelpfulltextscript')
	drop procedure sp_MShelpfulltextscript
go
/* sp_MSqv have been removed, but we want to keep this query, just in case there are left over from previous build */
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSqv')
	drop procedure sp_MSqv
go


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

/* New validation added for 7.0 */
/* if (@@microsoftversion >= SQL70_MINVERSION) begin                                       */
/*	 exec sp_MS_upd_sysobj_category 1                                                       */
/* end else begin                                                                          */
/*	 RAISERROR 55555 'You need a released version of SQL 7.0 to run this SQLDMO script'     */
/* end                                                                                     */
if (@@microsoftversion < 0x07000000) begin
	RAISERROR 55555 N'You need a released version of SQL 7.0 to run this SQLDMO script'
end

/*******************************************************************************/
print N''
print N'Creating sp_MShelpcolumns'
print N''
go
create procedure sp_MShelpcolumns
@tablename nvarchar(517), @flags int = 0, @orderby nvarchar(10) = null, @flags2 int = 0
as

   /* For non-string columns, sp_MShelpcolumns returns the length in syscolumns.length, */
   /* which is defined in BOL as "maximum physical storage length from systypes".       */
   /* For string columns (including types based on string types), sp_MShelpcolumns      */
   /* returns this maximum length in characters (i.e. it returns syscolumns.length      */
   /* adjusted to whether the column is based on char or nchar).                        */

   /*** @flags2 added for DaVinci uses.  If the bit isn't set, use 6.5 ***/
   /*** sp_MShelpcolumns '%s', null, 'id', 1                           ***/

/** For DaVinci **/
/** Use sp_help filtering of precision/scale (only fordecimal/numeric types; else use NULL). **/


	if @flags is null
		select @flags = 0
	if (@tablename = N'?')
	begin
		print N''
		print N'Usage:  sp_MShelpcolumns @tablename, @flags int = 0'
		print N' where @flags is a bitmask of:'
		print N' 0x0200		= No DRI (ignore Checks, Primary/Foreign/Unique Keys, etc.)'
		print N' 0x0400		= UDDTs --> Base type'
		print N' 0x80000		= TimestampToBinary (convert timestamp cols to binary(8))'
		print N' 0x40000000	= No Identity attribute'
		return 0
	end

	declare @objid int
	select @objid = object_id(@tablename)
	if (@objid is null)
	begin
		RAISERROR (15001, -1, -1, @tablename)
		return 1
	end

	set nocount on

	create table #sphelpcols
	(
	   col_name			nvarchar(128)			NOT NULL,
	   col_id			int					NOT NULL,
	   col_typename		nvarchar(128)			NOT NULL,
	   col_len			int					NOT NULL,
	   col_prec			int					NULL,
	   col_scale		int					NULL,
	   col_numtype		smallint			NOT NULL,	/* For DaVinci to get sp_help-type filtering of prec/scale */
	   col_null			bit					NOT NULL,	/* status & 8 */
	   col_identity		bit					NOT NULL,	/* status & 128 */
	   col_defname		nvarchar(257)			NULL,		/* fully-qual'd default name, or NULL */
	   col_rulname		nvarchar(257)			NULL,		/* fully-qual'd rule name, or NULL */
	   col_basetypename	nvarchar(128)			NOT NULL,
	   col_flags		int					NULL,		/* COL_* bits */
	   col_seed			numeric (28)      NULL,
	   col_increment	int 				NULL,
	   col_dridefname	nvarchar(128)			NULL,		/* DRI DEFAULT name */
	   col_dridefid	int					NULL,         /* remember the DRI DEFAULT id in syscomments, so we can retrieve it later */
      col_iscomputed int               NOT NULL,
	   col_objectid	int					NOT NULL,     /* column object id, need it to get computed text from syscomments */
	   col_NotForRepl	bit					NOT NULL,     /* Not For Replication setting */
	   col_fulltext	bit					NOT NULL,     /* FullTextIndex setting */
	   col_AnsiPad 	bit               NULL,         /* Ansi_Padding setting */
      /* following columns are repeating the info from col_defname and col_rulname                  */
      /* because we can not change data in col_defname and col_rulname, since daVinci is using them */
	   col_DOwner     nvarchar(128)			NULL,		/* non-DRI DEFAULT owner, or NULL */
	   col_DName      nvarchar(128)			NULL,		/* non-DRI DEFAULT name, or NULL */
	   col_ROwner     nvarchar(128)			NULL,		/* non-DRI RULE owner, or NULL */
	   col_RName      nvarchar(128)			NULL,		/* non-DRI RULE name, or NULL */
	)

   /* Do not store the computed text in this temp table, because one extra join causes big performance hit */
	/* First load stuff so we can blot off inappropriate info and massage as per @flags */
	insert #sphelpcols
		select c.name, c.colid, st.name,
         case when bt.name in (N'nchar', N'nvarchar') then c.length/2 else c.length end,
			ColumnProperty(@objid, c.name, N'Precision'),
			ColumnProperty(@objid, c.name, N'Scale'),
				-- col_numtype for DaVinci:  use sp_help-type prec/scale filtering for @flags2 & 1
			case when (@flags2 & 1 <> 0 and bt.name in (N'tinyint',N'smallint',N'decimal',N'int',N'real',N'money',N'float',N'numeric',N'smallmoney'))
					then 1 else 0 end,
				-- Nullable
			convert(bit, ColumnProperty(@objid, c.name, N'AllowsNull')),
				-- Identity
			case when (@flags & 0x40000000 = 0) then convert(bit, ColumnProperty(@objid, c.name, N'IsIdentity')) else 0 end,
				-- Non-DRI Default (make sure it's not a DRI constraint).
			case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else user_name(d.uid) + N'.' + d.name end,
				-- Non-DRI Rule
			case when (c.domain = 0) then null else user_name(r.uid) + N'.' + r.name end,
				-- Physical base datatype
			bt.name,
				-- Initialize flags to whether it's a length-specifiable type, or a numeric type, or 0.
			case when st.name in (N'char',N'varchar',N'binary',N'varbinary',N'nchar',N'nvarchar') then 0x0001
					when st.name in (N'decimal',N'numeric') then 0x0002
					else 0 end
					-- Will be NULL if column is not UniqueIdentifier.
					+ case isnull(ColumnProperty(@objid, c.name, N'IsRowGuidCol'), 0) when 0 then 0 else 0x0008 end,
				-- Identity seed and increment
			case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then ident_seed(@tablename) else null end,
			case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then ident_incr(@tablename) else null end,
				-- DRI Default name
			case when (@flags & 0x0200 = 0 and c.cdefault is not null and (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0))
					then object_name(c.cdefault) else null end,
				-- DRI Default text, if it does not span multiple rows (if it does, SQLDMO will go get them all).
			case when (@flags & 0x0200 = 0 and c.cdefault is not null and (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0))
					then t.id else null end,
         c.iscomputed,
         c.id,
				-- Not For Replication
			convert(bit, ColumnProperty(@objid, c.name, N'IsIdNotForRepl')),
         convert(bit, ColumnProperty(@objid, c.name, N'IsFulltextIndexed')),
         convert(bit, ColumnProperty(@objid, c.name, N'UsesAnsiTrim')),
				-- Non-DRI Default owner and name
			case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else user_name(d.uid) end,
			case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else d.name end,
				-- Non-DRI Rule owner and name
			case when (c.domain = 0) then null else user_name(r.uid) end,
			case when (c.domain = 0) then null else r.name end
		from syscolumns c
				-- NonDRI Default and Rule filters
			left outer join sysobjects d on d.id = c.cdefault
			left outer join sysobjects r on r.id = c.domain
				-- Fully derived data type name
			join systypes st on st.xusertype = c.xusertype
				-- Physical base data type name
			join systypes bt on bt.xusertype = c.xtype
				-- DRIDefault text, if it's only one row.
			left outer join syscomments t on t.id = c.cdefault and t.colid = 1
					and not exists (select * from syscomments where id = c.cdefault and colid = 2)
		where c.id = @objid
		order by c.colid

	/* Convert any timestamp column to binary(8) if they asked. */
	if (@flags & 0x80000 != 0)
		update #sphelpcols set col_typename = N'binary', col_len = 8, col_flags = col_flags | 0x0001 where col_typename = N'timestamp'

	/* Now see what our flags are, if anything. */
	if (@flags is not null and @flags != 0)
	begin
		if (@flags & 0x0400 != 0)
		begin
			/* Track from xusertype --> b.<base>xtype --> u.xusertype in systypes */
			/* First mask off the things we will set.  The convert() awkwardness is */
			/* necessitated by SQLServer's handling of 0x-prefixed values. */
			declare @typeflagmask int select @typeflagmask = (convert(int, 0x0001) + convert(int, 0x0002))
			update #sphelpcols set col_typename = b.name,
				-- ReInitialize flags to whether it's a length-specifiable type, or a numeric type, or 0.
				col_flags = col_flags & ~@typeflagmask
							+ case when b.name in (N'char',N'varchar',N'binary',N'varbinary',N'nchar',N'nvarchar') then 0x0001
								when b.name in (N'decimal',N'numeric') then 0x0002
								else 0 end
			from #sphelpcols c, systypes n, systypes b
				where n.name = col_typename				--// xtype (base type) of name
					and b.xusertype = n.xtype			--// Map it back to where it's xusertype, to get the name
		end
	end

	/* Determine if the column is in the primary key */
	if (@flags & 0x0200 = 0 and (OBJECTPROPERTY(@objid, N'TableHasPrimaryKey') <> 0)) begin
		declare @indid int
		select @indid = indid from sysindexes i where i.id = @objid and i.status & 0x0800 <> 0
		if (@indid is not null)
			update #sphelpcols set col_flags = col_flags | 0x0004
			from #sphelpcols c, sysindexkeys i
				where i.id = @objid and i.indid = @indid and i.colid = c.col_id
	end

	/* OK, now put out the data.  @flags2 added for DaVinci; currently only bit 1 (sp_help filtering of prec/scale) is relevant. */
	set nocount off
	if (@orderby is null or @orderby = N'id')
	begin
		select c.col_name, c.col_id, c.col_typename, c.col_len,
					-- Prec/scale only for numeric/decimal
				col_prec = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0))
						then c.col_prec else NULL end,
				col_scale = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0))
						then c.col_scale else NULL end,
				col_basetypename, c.col_defname, c.col_rulname, c.col_null, c.col_identity, c.col_flags,
			   c.col_seed,
            c.col_increment, c.col_dridefname, cn.text, c.col_iscomputed, cm.text, c.col_NotForRepl,
            c.col_fulltext, c.col_AnsiPad, c.col_DOwner, c.col_DName, c.col_ROwner, c.col_RName
		from (#sphelpcols c
      left outer join syscomments cm on cm.id = c.col_objectid and cm.number = c.col_id) left outer join syscomments cn on c.col_dridefid is not null and cn.id = c.col_dridefid
		order by c.col_id
	end else begin
		select c.col_name, c.col_id, c.col_typename, c.col_len,
					-- Prec/scale only for numeric/decimal
				col_prec = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0))
						then c.col_prec else NULL end,
				col_scale = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0))
						then c.col_scale else NULL end,
				col_basetypename, c.col_defname, c.col_rulname, c.col_null, c.col_identity, c.col_flags,
			   c.col_seed,
            c.col_increment, c.col_dridefname, cn.text, c.col_iscomputed, cm.text, c.col_NotForRepl,
            c.col_fulltext, c.col_AnsiPad, c.col_DOwner, c.col_DName, c.col_ROwner, c.col_RName
		from (#sphelpcols c
      left outer join syscomments cm on cm.id = c.col_objectid and cm.number = c.col_id) left outer join syscomments cn on c.col_dridefid is not null and cn.id = c.col_dridefid
		order by c.col_name
	end

go
/* End sp_MShelpcolumns */

/*******************************************************************************/
print N''
print N'Creating sp_MShelpindex'
print N''
go
create procedure sp_MShelpindex
@tablename nvarchar(517), @indexname nvarchar(258) = null, @flags int = null
as
   /*** @flags added for DaVinci uses.  If the bit isn't set, use 6.5 ***/
   /*** sp_MShelpindex '%s', null, 1                                  ***/

   /* @flags is for daVinci */
   if (@flags is null)
      select @flags = 0
   if (@flags <> 0)
   begin
   /* daVinci is calling */
      select i.name, i.status, i.indid, i.OrigFillFactor,
      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)
      , SegName = s.groupname
      , FullTextKey = IndexProperty(object_id(@tablename), i.name, N'IsFulltextKey')
      from (sysindexes i inner join
         sysfilegroups s on
         i.groupid = s.groupid )
      where id = object_id(@tablename) and i.indid > 0 and i.indid < 255 and
      (@indexname is null or i.name = @indexname) and
      (INDEXPROPERTY(object_id(@tablename), i.name, N'IsStatistics') <> 1) and
      (INDEXPROPERTY(object_id(@tablename), i.name, N'IsAutoStatistics') <> 1) and
      (INDEXPROPERTY(object_id(@tablename), i.name, N'IsHypothetical') <> 1)
      order by i.indid
   end else begin
      /* select (case when (i.status & 0x0040) != 0 then substring(i.name, 9, (datalength(i.name)/2)-17) else i.name end), i.status, i.indid, i.OrigFillFactor, */
      select i.name, i.status, i.indid, i.OrigFillFactor,
      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)
      , SegName = s.groupname
      , FullTextKey = IndexProperty(object_id(@tablename), i.name, N'IsFulltextKey')
      from (sysindexes i inner join
         sysfilegroups s on
         i.groupid = s.groupid )
      where id = object_id(@tablename) and i.indid > 0 and i.indid < 255
      and (@indexname is null or i.name = @indexname)
      order by i.indid
      /* order by i.name */
   end

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 N''
print N'Dumping transaction log...'
print N''
go
dump tran master with no_log
go
/************* END DUMP THE TRANSACTION LOG **********************************/

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

	/* Catch typos... */
	if (@flags is not null and @flags not in (N'sdt', N'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 @xusertype int
		select @xusertype = xusertype from systypes where name = @typename
		if (@xusertype is not null)
		begin
			if (@xusertype < 257)
			begin
				if (@flags is null)
					select @flags = N'sdt'
				if (@flags != N'sdt')
					select @xusertype = null
			end else begin
				if (@flags is null)
					select @flags = N'uddt'
				if (@flags != N'uddt')
					select @xusertype = null
			end
		end
		if (@xusertype is null)
		begin
			RAISERROR (15001, -1, -1, @typename)
			return 1
		end
	end

	/* Now go get the info, depending on the type they gave us. */
	if (@flags is null or @flags = N'sdt')
	begin
		/* Exclude the 'xxxxn' dblib-specific nullable types, and hardcode a check for variable length and numeric usertypes. */
      /* 7.0 ifvarlen_max returns length for all the datatypes */
		select 	SystemDatatypeName = t.name,
				ifvarlen_max = y.length,
					-- timestamp allows nulls even though the system tables say it doesn't.
				allownulls = case when t.name in (N'timestamp') then 1 else t.allownulls end,
				isnumeric = case when t.name in (N'decimal', N'numeric') then 1 else 0 end,
				allowidentity = case when t.name in (N'decimal', N'int', N'numeric', N'smallint', N'tinyint') then 1 else 0 end,
            variablelength = t.variable,
            max_len = t.length, prec_len = t.prec
         from systypes t, systypes y
         where t.xusertype < 257 and t.name not in (N'datetimn', N'decimaln', N'floatn', N'intn', N'moneyn', N'numericn') and (@typename is null or t.name = @typename)
         and y.xusertype =* t.xusertype and y.name in ( N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar' )
			order by t.name
	end

	/* Need a temp table so we can ownerqualify nonNULL rules/defaults. */
	create table #sphelptype (
		dt_xusertype		int				NULL,
		dt_basetype		nvarchar(128)	NULL,
		dt_rul 			int				NULL,
		dt_def 			int				NULL,

		dt_rulowner		nvarchar(128)		NULL,
		dt_rulname 		nvarchar(128)		NULL,
		dt_defowner		nvarchar(128)		NULL,
		dt_defname		nvarchar(128)		NULL,
		dt_flags		int				NULL
	)

	if (@flags is null or @flags = N'uddt')
	begin
		set nocount on
		insert #sphelptype (dt_xusertype, dt_basetype, dt_rul, dt_def, dt_flags)
			select t.xusertype,
			(select distinct b.name from systypes b where b.xtype = t.xtype and b.xusertype < 257 and b.name not in (N'sysname', N'timestamp')),
			t.domain, t.tdefault, 0
			from systypes t
			where t.xusertype > 256 and (@typename is null or t.name = @typename)

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

      update #sphelptype set dt_rulowner = user_name(r.uid)
            from #sphelptype c, sysobjects r where c.dt_rul is not null and r.id = c.dt_rul
      update #sphelptype set dt_rulname =  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 ( N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar')
		update #sphelptype set dt_flags = dt_flags | 0x0002 where dt_basetype in (N'numeric', N'decimal')

		set nocount off
		select distinct UserDatatypeName = t.name,
				owner = user_name(t.uid),
				basetypename = (select distinct b.name from systypes b where b.name = s.dt_basetype),
				defaultname = dt_defname,
				rulename = dt_rulname,
				tid = t.xusertype,
				length = case when s.dt_basetype in (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') then t.length else 0 end,
				nullable = t.allownulls,
				dt_prec = case when s.dt_basetype in (N'numeric', N'decimal') then t.prec else null end,
				dt_scale = case when s.dt_basetype in (N'numeric', N'decimal') then t.scale else null end,
				dt_flags,
				allowidentity = case when (s.dt_basetype in (N'decimal', N'int', N'numeric', N'smallint', N'tinyint') and t.scale = 0) then 1 else 0 end,
            variablelength = t.variable,
            /* char count for string datatype, byte count for others */
				maxlen = case when s.dt_basetype in (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') then t.prec else t.length end,
            defaultowner = dt_defowner,
            ruleowner = dt_rulowner
			from systypes t, #sphelptype s
			where t.xusertype > 256 and (@typename is null or t.name = @typename)
				and dt_xusertype = t.xusertype
			order by t.name
	end
go
/* End sp_MShelptype */

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

create procedure sp_MSdependencies
@objname nvarchar(517) = null, @objtype int = null, @flags int = 0x01fd, @objlist nvarchar(128) = null
as
	if (@objname = N'?')
	begin
		print N'sp_MSobject_dependencies name = NULL, type = NULL, flags = 0x01fd'
		print N'  name:  name or null (all objects of type)'
		print N'  type:  type number (see below) or null'
		print N'	  if both null, get all objects in database'
		print N'  flags is a bitmask of the following values:'
		print N'	  0x10000  = return multiple parent/child rows per object'
		print N'	  0x20000  = descending return order'
		print N'	  0x40000  = return children instead of parents'
		print N'	  0x80000  = Include input object in output result set'
		print N'	  0x100000 = return only firstlevel (immediate) parents/children'
		print N'	  0x200000 = return only DRI dependencies'
		print N'	  power(2, object type number(s))  to return in results set:'
		print N'		  0 (1  	- 0x0001)	 - datatype'
		print N'		  1 (2  	- 0x0002)	 - system tables or MS-internal objects'
		print N'		  2 (4  	- 0x0004)	 - view'
		print N'		  3 (8  	- 0x0008)	 - user table'
		print N'		  4 (16		- 0x0010)	 - procedure'
		print N'		  5 (32		- 0x0020)	 - log'
		print N'		  6 (64 	- 0x0040)	 - default'
		print N'		  7 (128	- 0x0080)	 - rule'
		print N'		  8 (256	- 0x0100)	 - trigger'
		print N'	  shortcuts:'
		print N'		  29	 (0x011c) - trig, view, user table, procedure'
		print N'		  448	(0x00c1) - rule, default, datatype'
		print N'		  509	(0x01fd) - all but systables/objects'
		print N'		  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.                           */
   /* I can do this only if the current login has the proper permission to dump tempdb               */
   /* In order to find out this information, I need to switch to tempdb                              */
	declare @origdb nvarchar(128)
   declare @tempdbName nvarchar(258)
	select @origdb = db_name()
   SELECT @tempdbName = REPLACE(@origdb, N']', N']]')
   exec (N'if (has_dbaccess(''tempdb'') = 1) begin use tempdb if (permissions() & 0x80 <> 0) dump tran tempdb with no_log use [' + @tempdbName + N'] end')

	/* If they want SQLDMODep_DRIOnly, remove all but usertable objects from @flags */
	if (@flags & 0x200000 <> 0)
		select @flags = (@flags & ~convert(int, 0x01ff)) | 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 N'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. */
	create table #t3 (
		tid				int				NOT NULL
	)
	create table #t4 (
		tid				int				NOT NULL
	)
	/* create clustered index #ci_t3 on #t3(tid) with allow_dup_row */
	/* create clustered index #ci_t4 on #t4(tid) with allow_dup_row */
	create clustered index #ci_t3 on #t3(tid)
	create clustered index #ci_t4 on #t4(tid)
	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 */
	create clustered index #ci_temptrig on #temptrig (deltrig)

	/*
	 * 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 (@objlist is not null)
	begin
		declare @mscategory nvarchar(12)
		select @mscategory = ltrim(str(convert(int, 0x0002)))

		exec(N'insert #t1 (pid, ptype, pcat) select l.objid, l.objtype, o.category &' +  @mscategory +
				N' from ' + @objlist + N' l, sysobjects o where o.id = l.objid ')
	end else begin
		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 (OBJECTPROPERTY(o.id, N'IsDefaultCnst') <> 1 and OBJECTPROPERTY(o.id, N'IsRule') <> 1 )
		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
					RAISERROR (15001, -1, -1, @objname)
					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
	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 OBJECTPROPERTY(d.id, N'IsTrigger') = 1

	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
			if (@flags & 0x200000 = 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

			/* 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.fkeyid, o.sysstat & 0x0f, o.category & 0x0002
				from #t1 t, sysreferences r, sysobjects o
				where t.bDone is null and r.rkeyid = t.pid and r.fkeyid = o.id
		end else begin
			if (@flags & 0x200000 = 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, 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

			/* 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.rkeyid, o.sysstat & 0x0f, o.category & 0x0002
				from #t1 t, sysreferences r, sysobjects o
				where t.bDone is null and r.fkeyid = t.pid and r.rkeyid = 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.
	 */
	truncate table #t3
	insert #t3 select tid from #t1 where tid is not null
		and tid <> pid				-- make sure self-refs with no other refs go in child list
	/* 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)
	delete #t1 where tid = pid		-- now remove self-refs

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

	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 xusertype from systypes where xusertype > 256
		if (@flags & (power(2, 7) | power(2, 6)) != 0)
			insert #t2 (tid, ttype, tcat)
				select id, sysstat & 0x0f, 0 from sysobjects
				where (OBJECTPROPERTY(id, N'IsRule') = 1 or OBJECTPROPERTY(id, N'IsDefault') = 1)
				and category & 0x0800 = 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 xusertype from syscolumns
				where xusertype > 256 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.xusertype = 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.xusertype = t.dtype
						and s.tdefault not in (select tid from #t1)
						and s.tdefault not in (select id from sysobjects where category & 0x0800 != 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 sysobjects where category & 0x0800 != 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 & 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) 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. */
	/*
	 * At this point, these are indirect (a->b->...->a), and can only be created by:
	 * 	create table a; create table b ref a; alter table a ref b
	 * There is thus no way to create the tables in a single pass.  Further, the ALTER
	 * TABLE B must be done AFTER data has been added (else the PK/FK will fail).
	 * Therefore, the two-step model of
	 *  - Create tables (and other objects)
	 *  - Transfer data
	 * does not work, so assume anyone doing this will do it in three passes (e.g. ScriptTransfer):
	 *  - Create tables (and other objects) but no references (also defer some indexing, for perfomance)
	 *  - Transfer data
	 *  - Create references (and deferred indexing)
	 * and just set bDone for everything remaining to @curid.
	 */
	if exists (select * from #t1 where bDone = 0) begin
		--select "Circular Dependencies", object_name(tid) from #t1 where bDone = 0
		--RAISERROR (14300, -1, -1)
		--return 1
		update #t1 set bDone = @curid where bDone = 0
	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 & 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.xusertype = 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 & OBJTYPE_BITS), RelName = p.name, RelOwner = user_name(p.uid), */
				RelType = case when (p.name is not null) then power(2, p.sysstat & 0x0f) else 0 end, 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 N''
print N'Dumping transaction log...'
print N''
go
dump tran master with no_log
go
/************* END DUMP THE TRANSACTION LOG **********************************/

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

create procedure sp_MStablespace
@name nvarchar(517), @id int = null
as
	declare @rows int, @datasizeused int, @indexsizeused int, @pagesize int
	declare @dbname nvarchar(128)
	select @dbname = db_name()

	if (@id is null)
		select @id = id from sysobjects where id = object_id(@name) and (OBJECTPROPERTY(id, N'IsTable') = 1)
	if (@id is null)
	begin
		RAISERROR (15009, -1, -1, @name, @dbname)
		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)

   /* Do not consider 2 < indid < 255 rows, those are nonclustered indices, and the space used by them are included by indid = 0(table) */
   /* or indid = 1(clustered index) already.  indid = 0(table) and = 1(clustered index) are mutual exclusive */
	/* 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=N'E'

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

/* End sp_MStablespace */

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

CREATE PROCEDURE sp_MSindexspace
	@tablename nvarchar(517), @index_name nvarchar(258) = NULL
AS
BEGIN
  DECLARE @table_id int
  DECLARE @index_id int
  DECLARE @msg nvarchar(2000)
  DECLARE @pagesize int
  select @pagesize = v.low / 1024 from master..spt_values v where v.number=1 and v.type=N'E'

  /* Make sure @tablename is local to the current database */
  /* Check if server is pre 7.0 */
  if (@@version NOT LIKE N'%7.%.%') begin
     IF (@tablename like N'%.%.%') AND (substring(@tablename, 1, charindex(N'.', @tablename) - 1) <> db_name())
     BEGIN
         RAISERROR (15078, -1, -1, N'')
         RETURN(1)
     END
  end

  /* Make sure that @tablename and @index_name exist, we are checking table instead of UserTable */
  SELECT @table_id = id
  FROM sysobjects
  WHERE (id = object_id(@tablename))
    AND (OBJECTPROPERTY(id, N'IsTable') = 1)
  IF (@table_id is NULL)
  BEGIN
    RAISERROR (15001, -1, -1, @tablename)
    RETURN(1)
  END
  IF (@index_name is not NULL)
  BEGIN
    SELECT @index_id = indid
    FROM sysindexes
    WHERE (name = @index_name)
      AND (id = object_id(@tablename))
    IF (@index_id is NULL)
    BEGIN
      SELECT @msg = @tablename + N'.' + @index_name
      RAISERROR (15001, -1, -1, @msg)
      RETURN(1)
    END
  END
  /* Ok, we're good to go */
  IF (user_id() = 1)
    CHECKPOINT
  IF (@index_name is NULL)
  BEGIN
    CREATE TABLE #IndexSizeTemp (
		IndexID			tinyint			NOT NULL,
		IndexName		nvarchar(128)		NOT NULL,
		IndexSize		int				NOT NULL,
		Comments		nvarchar(28)		NOT NULL
	)
    INSERT INTO #IndexSizeTemp
    SELECT indid, name, 0, N''
    FROM sysindexes
    WHERE (id = object_id(@tablename))
      AND ((indid > 0) AND (indid < 255))
    UPDATE #IndexSizeTemp
    SET IndexSize = used * @pagesize,
        Comments = N'(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)) * @pagesize,
        Comments = N'Size excludes actual data.'
    FROM sysindexes si, #IndexSizeTemp ist
    WHERE (id = object_id(@tablename))
      AND (indid = 1)
      AND (si.indid = ist.IndexID)
    SELECT N'Index ID' = IndexID, N'Index Name' = IndexName, N'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 N'Size (KB)' = used * @pagesize
      FROM sysindexes
      WHERE (id = object_id(@tablename))
        AND (name = @index_name)
      RETURN(0)
    END
    /* The clustered index case */
    IF (@indid = 1)
    BEGIN
      SELECT N'Size (KB)' =
             (used - dpages - isnull((SELECT sum(used)
                                      FROM sysindexes
                                      WHERE (indid > 1) AND (indid < 255)
                                        AND (id = object_id(@tablename))), 0)) * @pagesize
      FROM sysindexes
      WHERE (id = object_id(@tablename))
        AND (name = @index_name)
    END
  END
  RETURN(0)
END
go
/* End sp_MSindexspace */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MStablerefs'
print N''
go

create procedure sp_MStablerefs
	@tablename nvarchar(517),					
	@type nvarchar(20) = N'actualtables',		
	@direction nvarchar(20) = N'primary',		
	@reftable nvarchar(517) = null,			
   @flags int = 0
as
   /* tablename: table whose references are being evaluated */
   /* type     : '[actual | all][tables | keys | keycols]'; all candidates, or only those actually referenced */
   /* direction: look for references from @tablename to 'primary' table(s), or to @tablename from 'foreign' table(s) */
   /* reftable : limit scope to this table, if non-null */
   /*** @flags added for DaVinci uses.  If the bit isn't set, use 6.5 ***/
   /*** sp_MStablerefs '%s', null, 'both'                             ***/

	/* @flags is for daVinci */
	if (@flags is null)
		select @flags = 0

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

	if (lower(@direction) = N'both') begin
		select
         N'PK_Table' = PKT.name,
         N'FK_Table' = FKT.name,
         N'Constraint' = object_name(r.constid),
			c.status,
			cKeyCol1 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey1)),
			cKeyCol2 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey2)),
			cKeyCol3 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey3)),
			cKeyCol4 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey4)),
			cKeyCol5 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey5)),
			cKeyCol6 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey6)),
			cKeyCol7 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey7)),
			cKeyCol8 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey8)),
			cKeyCol9 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey9)),
			cKeyCol10 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey10)),
			cKeyCol11 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey11)),
			cKeyCol12 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey12)),
			cKeyCol13 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey13)),
			cKeyCol14 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey14)),
			cKeyCol15 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey15)),
			cKeyCol16 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey16)),
			cRefCol1 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey1)),
			cRefCol2 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey2)),	
			cRefCol3 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey3)),
			cRefCol4 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey4)),
			cRefCol5 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey5)),
			cRefCol6 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey6)),
			cRefCol7 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey7)),
			cRefCol8 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey8)),
			cRefCol9 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey9)),
			cRefCol10 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey10)),
			cRefCol11 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey11)),
			cRefCol12 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey12)),
			cRefCol13 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey13)),
			cRefCol14 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey14)),
			cRefCol15 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey15)),
			cRefCol16 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey16)),
			N'PK_Table_Owner' = user_name(PKT.uid),
			N'FK_Table_Owner' = user_name(FKT.uid)
      from sysreferences r, sysconstraints c, sysobjects PKT, sysobjects FKT
      where r.constid = c.constid and (@tablename is null or
         (r.rkeyid = object_id(@tablename) or r.fkeyid = object_id(@tablename)))
      and PKT.id = r.rkeyid and FKT.id = r.fkeyid
      return 0
	end /* @direction = 'both' */

	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 (15001, -1, -1, @tablename)
		return 1
	end
	if (@reftable is not null and @refid is null) begin
		RAISERROR (15001, -1, -1, @reftable)
		return 1
	end

	create table #sprefs (
		id					int				NOT NULL, 	/* id of reftable */
		constid				int				NULL, 		/* id of key */
		referenced			bit				NOT NULL	/* well, is it? */
	)

	declare @dotables bit, @doall bit, @doprimary bit, @docols bit
	select 	@dotables = case when (@type like N'allt%' or @type like N'actualt%') then 1 else 0 end,
			@doall = case when (@type like N'all%') then 1 else 0 end,
			@doprimary = case when (@direction like N'p%') then 1 else 0 end,
			@docols = case when (@type like N'%keycol%') then 1 else 0 end

	/* If a specific @tablename specified, see if it has the kind of keys we want. */
	/* If asking for references from @tablename to 'primary', we must have an FKEY; */
	/* if asking for references to @tablename from 'foreign', we must have an active REFerence. */
	if (@id is not null) begin
		declare @wantkeytype int
		select @wantkeytype = case @doprimary when 1 then 0x4 else 0x8 end
		if not exists (select * from sysobjects where id = @id and category & @wantkeytype <> 0)
			goto ReturnSet
	end

	if (@dotables = 1) begin
		if (@doprimary = 1) begin
			/* Get all candidate tables (those with Primary/Unique keys in sysconstraints). */
			insert #sprefs
				select distinct id, null, 0 from sysconstraints where status & 0x0f in (1, 2)

			/* Update the referenced bit if this table references it. */
			update #sprefs set referenced = 1
				where id in (select rkeyid from sysreferences where fkeyid = @id)
		end else begin
			/* All user tables are foreign-key candidate tables. */
			insert #sprefs
				select distinct id, null, 0 from sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1

			/* Update the referenced bit if it references this table. */
			update #sprefs set referenced = 1
				where id in (select fkeyid from sysreferences where rkeyid = @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, constid, 0 from sysconstraints where status & 0x0f in (1, 2)

			/* 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.fkeyid = @id
            and i.id = r.rkeyid and i.indid = r.rkeyindid and i.status & 0x1800 <> 0
            and s.constid = object_id(N'[' + REPLACE(i.name, N']', N']]') + N']')

		end else begin
			/* First add tables with FOREIGN keys defined. */
			insert #sprefs
				select distinct id, constid, 0 from sysconstraints where status & 0x0f in (3)

			/* 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 OBJECTPROPERTY(id, N'IsUserTable') = 1
					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 constid in (select constid from sysreferences where rkeyid = @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 OBJECTPROPERTY(id, N'IsUserTable') <> 1 or category & 0x0002 <> 0)
			or (@refid is not null and id != @refid)

	/* Output */
ReturnSet:
	if (@docols = 0) begin
		if (@tablename is not null) begin
            select candidate_table = N'[' + REPLACE(user_name(o.uid), N']', N']]') + N']' + N'.' + N'[' + REPLACE(object_name(o.id), N']', N']]') + N']',
               candidate_key = case @dotables when 1 then N'N/A' else object_name(s.constid) end, 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), object_name(s.constid)
      end else begin
            select candidate_table = N'[' + REPLACE(user_name(o.uid), N']', N']]') + N']' + N'.' + N'[' + REPLACE(object_name(o.id), N']', N']]') + N']',
               candidate_key = case @dotables when 1 then N'N/A' else object_name(s.constid) end
               from #sprefs s, sysobjects o where o.id = s.id
               order by object_name(o.id), user_name(o.uid), object_name(s.constid)
      end
	end else begin	/* @docols = 1 */
		/* This is currently just implemented for 'nonNULLtablename', 'actualkeycols', 'foreign'. */
         select candidate_table = N'[' + REPLACE(user_name(o.uid), N']', N']]') + N']' + N'.' + N'[' + REPLACE(object_name(o.id), N']', N']]') + N']',
               candidate_key = object_name(s.constid),
               cKeyCol1 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey1)),
               cKeyCol2 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey2)),
               cKeyCol3 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey3)),
               cKeyCol4 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey4)),
               cKeyCol5 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey5)),
               cKeyCol6 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey6)),
               cKeyCol7 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey7)),
               cKeyCol8 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey8)),
               cKeyCol9 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey9)),
               cKeyCol10 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey10)),
               cKeyCol11 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey11)),
               cKeyCol12 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey12)),
               cKeyCol13 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey13)),
               cKeyCol14 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey14)),
               cKeyCol15 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey15)),
               cKeyCol16 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey16))
            from #sprefs s, sysobjects o, sysreferences r
            where o.id = s.id and r.constid = s.constid and s.referenced = 1
            order by object_name(o.id), user_name(o.uid), object_name(s.constid)
	end
go
/* End sp_MStablerefs */


/*******************************************************************************/
print N''
print N'Creating sp_MStablekeys'
print N''
go

create procedure sp_MStablekeys
@tablename nvarchar(776) = null, @colname nvarchar(258) = null, @type int = null, @keyname nvarchar(517) = null, @flags int = 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). */
	if (@type is null)
		select @type = 0x000e
	else
		select @type = @type & 0x000e

	/* Flags usage:  For daVinci, to pass call thru to sp_MStablerefs. */
	if (@flags is null)
		select @flags = 0

	set nocount on
	create table #spkeys
	(
		cType				tinyint			NOT NULL,	/* key Type */
		cName				nvarchar(258)		NOT NULL,	/* 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			nvarchar(520)		NULL,		/* owner-qual Referenced table name for FKs */
		cRefKey				nvarchar(260)		NULL,		/* name of referenced key in referenced table */
			-- Note:  cConstID replaces the column list used in 6.0, for speed.
			-- The output set MUST replace this with either index_col(@tablename, cIndexID, 1-16) and NULL * 16
			-- (for PK/UQ) UNION col_name(r.fkeyid, r.fkey1-16) and col_name(r.rkeyid, r.rkey1-16), for SQLDMO,
			-- and these MUST BE nvarchar(132) for alignment in the SQLDMO cache structure!
		cConstID			int				NULL,		/* Reference constraint ID, if Foreign Key  */
		cIndexID			int				NULL,		/* ID of this key's index, if PK/UQ */
		cGroupName		sysname		NULL,		/* FileGroup name of this key, if PK/UQ */
		cDisabled		int				NULL,		/* 0 if enabled, 1 if disabled */
		cPrimaryFG		int				NULL,		/* 1 if primary FG, 0 otherwise */
	)

	declare @cType int, @cName nvarchar(258), @cFlags int, @cRefTable nvarchar(520), @fillfactor tinyint
	declare @objid int, @constid int, @indid int, @keycnt int, @q1 nvarchar(2000), @q2 nvarchar(2000), @objtype int, @groupname sysname
	declare @haskeytypes int, @wantkeytypes int
   declare @cDisabled int, @PrimaryFG int

	/* First see if @keyname was defined, and override @tablename and @type if so. */
	if (@keyname is not null)
	begin
         select @objid = id, @type = power(2, status & 0x0f) from sysconstraints where constid = object_id(@keyname)
         if (@objid is null)	begin
            RAISERROR (15001, -1, -1, @keyname)
            return 1
         end
         /* Now get the tablename for the index_col below */
         select @tablename = N'[' + REPLACE(user_name(uid), N']', N']]') + N']' + N'.' + N'[' + REPLACE(name, N']', N']]') + N']' from sysobjects where id = @objid
	end else begin
		/* Want all keys for this table (of @type type). */
		select @objid = id, @objtype = (case when OBJECTPROPERTY(id, N'IsTable') = 1 then 1 else 0 end), @haskeytypes = category & 0x0604
			from sysobjects where id = object_id(@tablename)
		if (@objid is null)	begin
			RAISERROR (15001, -1, -1, @tablename)
			return 1
		end
		if (@objtype <> 1)	begin
			RAISERROR (15218, -1, -1, @tablename)
			return 1
		end
		if @colname is not null and not exists (select * from syscolumns where id = @objid and name = @colname) begin
			RAISERROR (15253, -1, -1, @colname, @tablename)
			return 1
		end

		/* Skip cursor opening if we don't have any keys (of the type wanted); return a set anyway, for the cache. */
		if (@haskeytypes = 0)
			goto ReturnSet

		/* Map from the input bitmask to the category bitmask */
		select @wantkeytypes = 0
		if ((@type & power(2, 1)) <> 0)
			select @wantkeytypes = @wantkeytypes | 0x200
		if ((@type & power(2, 2)) <> 0)
			select @wantkeytypes = @wantkeytypes | 0x400
		if ((@type & power(2, 3)) <> 0)
			select @wantkeytypes = @wantkeytypes | 0x4
		if ((@haskeytypes & @wantkeytypes) = 0)
			goto ReturnSet
	end

	/* Preprocessor won't replace within quotes so have to use str(). */
	declare @sysgenname nvarchar(12), @pkstr nvarchar(12), @uqstr nvarchar(12), @fkstr nvarchar(12), @objtypebits nvarchar(12)
	select @sysgenname = ltrim(str(convert(int, 0x00020000)))
	select @pkstr = ltrim(str(convert(int, 1)))
	select @uqstr = ltrim(str(convert(int, 2)))
	select @fkstr = ltrim(str(convert(int, 3)))
	select @objtypebits = ltrim(str(convert(int, 0x0f)))

	/* Other ints we need strings for */
	declare @objidstr nvarchar(12), @typestr nvarchar(12)
	select @objidstr = ltrim(str(@objid))
	select @typestr = ltrim(str(@type))

	/* Qualifying key name. */
	declare @qualkeyname nvarchar(100)
	select @qualkeyname = null
	if (@keyname is not null) begin
      select @qualkeyname = N' and constid = object_id(''' + @keyname + N''')'
   end

	/*********************/
	/* Main cursor loop. */
	/*********************/
	exec(N'declare hC insensitive cursor for select constid, status & ' + @objtypebits + N', status & ' + @sysgenname +
			N' from sysconstraints where id = ' + @objidstr + N' and (' + @typestr + N' & power(2, status & 0x0f) != 0) ' + @qualkeyname)
	open hC
	fetch hC into @constid, @cType, @cFlags
	while (@@fetch_status >= 0) begin
		if (object_name(@constid) is null) begin
			raiserror 55555 N'Assert failed:  object_name(@constid) is null in sp_MStablekeys (pk/uq)'
			return 1
		end

		/* DRI_PRIMARYKEY, DRI_UNIQUE */
		if (@cType in (1, 2)) begin
			/* Get the index id enforcing this constraint. */
			select @indid = i.indid, @cName = o.name, @fillfactor = i.OrigFillFactor,
					@cFlags = @cFlags | (case indid when 1 then 0x00000001 else 0 end),		/* test for clustered index */
               /* clustered index keys are part of non-clustered index key list, which cause incorrect sysindexes.keycnt */
					@keycnt = case indid when 1 then keycnt else (select count(x.id) from sysindexkeys x where i.indid = x.indid and x.id = @objid) end,
               @groupname = f.groupname,
               @PrimaryFG = FILEGROUPPROPERTY( f.groupname, N'IsPrimaryFG' )
				from sysindexes i, sysobjects o, sysfilegroups f
				where o.id = @constid and i.name like o.name and i.status & 0x1800 <> 0 and i.groupid = f.groupid
			if (@indid is null) begin
				raiserror 77777 N'Assert failed:  @indid is null in sp_MStablekeys (pk/uq)'
				return 1
			end

			/* Load our temp table. */
			insert #spkeys values (@cType, @cName, @cFlags, @keycnt, @fillfactor, null, null, null, @indid, @groupname, 0, @PrimaryFG)
		end

		/* DRI_REFERENCE */
		else if (@cType in (3)) begin
			/* Get the key column information from sysreferences. */
         select @keycnt = r.keycnt, @cName = object_name(r.constid), @cRefTable = N'[' + user_name(o.uid) + N']' + N'.' + N'[' + o.name + N']',
               @cDisabled = OBJECTPROPERTY( r.constid, N'CnstIsDisabled' )
            from sysreferences r, sysobjects o where r.constid = @constid and o.id = r.rkeyid

			/* Follow r.rkeyindid back to sysindexes to get the ref key name. */
			declare @cRefKey nvarchar(132)
			select @cRefKey = i.name, @cFlags = c.status from sysreferences r, sysindexes i, sysconstraints c
				where c.constid = r.constid and r.constid = @constid
				and i.id = r.rkeyid and i.indid = r.rkeyindid and i.status & 0x1800 <> 0

			/* Load our temp table. */
			insert #spkeys values (@cType, @cName, @cFlags, @keycnt, null, @cRefTable, @cRefKey, @constid, null, null, @cDisabled, 0)
		end		/* Key type */

		/* Get the next row. */
		fetch hC into @constid, @cType, @cFlags
	end			/* PRIMARY/UNIQUE */
	deallocate hC

	/* Now output the data */
ReturnSet:
	set nocount off
	select cType, cName, cFlags, cColCount, cFillFactor, cRefTable, cRefKey,
			cKeyCol1 = convert(nvarchar(132), index_col(@tablename, cIndexID, 1)),
			cKeyCol2 = convert(nvarchar(132), index_col(@tablename, cIndexID, 2)),	
			cKeyCol3 = convert(nvarchar(132), index_col(@tablename, cIndexID, 3)),
			cKeyCol4 = convert(nvarchar(132), index_col(@tablename, cIndexID, 4)),
			cKeyCol5 = convert(nvarchar(132), index_col(@tablename, cIndexID, 5)),
			cKeyCol6 = convert(nvarchar(132), index_col(@tablename, cIndexID, 6)),	
			cKeyCol7 = convert(nvarchar(132), index_col(@tablename, cIndexID, 7)),
			cKeyCol8 = convert(nvarchar(132), index_col(@tablename, cIndexID, 8)),
			cKeyCol9 = convert(nvarchar(132), index_col(@tablename, cIndexID, 9)),
			cKeyCol10 = convert(nvarchar(132), index_col(@tablename, cIndexID, 10)),
			cKeyCol11 = convert(nvarchar(132), index_col(@tablename, cIndexID, 11)),
			cKeyCol12 = convert(nvarchar(132), index_col(@tablename, cIndexID, 12)),
			cKeyCol13 = convert(nvarchar(132), index_col(@tablename, cIndexID, 13)),
			cKeyCol14 = convert(nvarchar(132), index_col(@tablename, cIndexID, 14)),	
			cKeyCol15 = convert(nvarchar(132), index_col(@tablename, cIndexID, 15)),
			cKeyCol16 = convert(nvarchar(132), index_col(@tablename, cIndexID, 16)),
			cRefCol1 = convert(nvarchar(132), null),
			cRefCol2 = convert(nvarchar(132), null),
			cRefCol3 = convert(nvarchar(132), null),
			cRefCol4 = convert(nvarchar(132), null),
			cRefCol5 = convert(nvarchar(132), null),
			cRefCol6 = convert(nvarchar(132), null),
			cRefCol7 = convert(nvarchar(132), null),
			cRefCol8 = convert(nvarchar(132), null),
			cRefCol9 = convert(nvarchar(132), null),
			cRefCol10 = convert(nvarchar(132), null),
			cRefCol11 = convert(nvarchar(132), null),
			cRefCol12 = convert(nvarchar(132), null),
			cRefCol13 = convert(nvarchar(132), null),
			cRefCol14 = convert(nvarchar(132), null),
			cRefCol15 = convert(nvarchar(132), null),
			cRefCol16 = convert(nvarchar(132), null),
			cIndexID,
			cGroupName,
         cDisabled,
	      cPrimaryFG
		from #spkeys where cType in (1, 2)
			and (@colname is null or
				index_col(@tablename, cIndexID, 1) = @colname or
				index_col(@tablename, cIndexID, 2) = @colname or
				index_col(@tablename, cIndexID, 3) = @colname or
				index_col(@tablename, cIndexID, 4) = @colname or
				index_col(@tablename, cIndexID, 5) = @colname or
				index_col(@tablename, cIndexID, 6) = @colname or
				index_col(@tablename, cIndexID, 7) = @colname or
				index_col(@tablename, cIndexID, 8) = @colname or
				index_col(@tablename, cIndexID, 9) = @colname or
				index_col(@tablename, cIndexID, 10) = @colname or
				index_col(@tablename, cIndexID, 11) = @colname or
				index_col(@tablename, cIndexID, 12) = @colname or
				index_col(@tablename, cIndexID, 13) = @colname or
				index_col(@tablename, cIndexID, 14) = @colname or
				index_col(@tablename, cIndexID, 15) = @colname or
				index_col(@tablename, cIndexID, 16) = @colname
			)
		UNION
		select c.cType, c.cName, c.cFlags, c.cColCount, c.cFillFactor, c.cRefTable, c.cRefKey,
			cKeyCol1 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey1)),
			cKeyCol2 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey2)),
			cKeyCol3 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey3)),
			cKeyCol4 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey4)),
			cKeyCol5 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey5)),
			cKeyCol6 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey6)),
			cKeyCol7 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey7)),
			cKeyCol8 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey8)),
			cKeyCol9 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey9)),
			cKeyCol10 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey10)),
			cKeyCol11 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey11)),
			cKeyCol12 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey12)),
			cKeyCol13 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey13)),
			cKeyCol14 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey14)),
			cKeyCol15 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey15)),
			cKeyCol16 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey16)),
			cRefCol1 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey1)),
			cRefCol2 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey2)),	
			cRefCol3 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey3)),
			cRefCol4 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey4)),
			cRefCol5 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey5)),
			cRefCol6 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey6)),
			cRefCol7 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey7)),
			cRefCol8 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey8)),
			cRefCol9 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey9)),
			cRefCol10 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey10)),
			cRefCol11 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey11)),
			cRefCol12 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey12)),
			cRefCol13 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey13)),
			cRefCol14 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey14)),
			cRefCol15 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey15)),
			cRefCol16 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey16)),
			cIndexID,
			cGroupName,
         cDisabled,
	      cPrimaryFG
		from #spkeys c, sysreferences r where c.cType = 3 and r.constid = c.cConstID
			and (@colname is null or
				col_name(r.fkeyid, r.fkey1) = @colname or
				col_name(r.fkeyid, r.fkey2) = @colname or
				col_name(r.fkeyid, r.fkey3) = @colname or
				col_name(r.fkeyid, r.fkey4) = @colname or
				col_name(r.fkeyid, r.fkey5) = @colname or
				col_name(r.fkeyid, r.fkey6) = @colname or
				col_name(r.fkeyid, r.fkey7) = @colname or
				col_name(r.fkeyid, r.fkey8) = @colname or
				col_name(r.fkeyid, r.fkey9) = @colname or
				col_name(r.fkeyid, r.fkey10) = @colname or
				col_name(r.fkeyid, r.fkey11) = @colname or
				col_name(r.fkeyid, r.fkey12) = @colname or
				col_name(r.fkeyid, r.fkey13) = @colname or
				col_name(r.fkeyid, r.fkey14) = @colname or
				col_name(r.fkeyid, r.fkey15) = @colname or
				col_name(r.fkeyid, r.fkey16) = @colname
			)
		order by cType, cName

	if (@flags & 1 <> 0)
		exec sp_MStablerefs @tablename, N'actualkeycols', N'foreign'

go
/* End sp_MStablekeys */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MStablechecks'
print N''
go

create procedure sp_MStablechecks
	@tablename nvarchar(517), @flags int = null
as
   /*** @flags added for DaVinci uses.  If the bit isn't set, use 6.5 ***/
   /*** sp_MStablechecks '%s'                                        ***/

	declare @id int
	select @id = object_id(@tablename)
	if (@id is null) begin
		RAISERROR (15001, -1, -1, @tablename)
		return 1
	end

	/* @flags is for daVinci. */
	if (@flags is null)
		select @flags = 0

	/* 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, unless @flags wants it anyway. */
	select object_name(t.id),
		case when (@flags & 1 <> 0 or not exists (select * from syscomments where id = t.id and colid = 2))
				then t.text else null end,
		c.status & (convert(int, 0x00200000) | convert(int, 0x00020000) | convert(int, 0x00004000)),
      OBJECTPROPERTY( t.id, N'CnstIsDisabled' )
	from syscomments t, sysconstraints c
	where t.id = c.constid and c.id = @id and c.status & 0x0f = 4
		and (@flags & 1 <> 0 or t.colid = 1)
	order by object_name(t.id), t.colid
go
/* End sp_MStablechecks */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSsettopology'
print N''
go

/* Need this because it will set sysservers columns. */
sp_configure N'allow updates', 1
go
reconfigure with override
go

create procedure sp_MSsettopology
	@server nvarchar(258), @X int, @Y int
as
	update master..sysservers set topologyx = @X, topologyy = @Y
		where srvname = @server
	if (@@rowcount = 0) begin
		RAISERROR (15015, -1, -1, @server)
		return 1
	end
	return 0
go
/* End sp_MSsettopology */

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

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSmatchkey'
print N''
go

create proc sp_MSmatchkey
	@tablename nvarchar(517),
	@col1 nvarchar(258),
	@col2 nvarchar(258) = null,
	@col3 nvarchar(258) = null,
	@col4 nvarchar(258) = null,
	@col5 nvarchar(258) = null,
	@col6 nvarchar(258) = null,
	@col7 nvarchar(258) = null,
	@col8 nvarchar(258) = null,
	@col9 nvarchar(258) = null,
	@col10 nvarchar(258) = null,
	@col11 nvarchar(258) = null,
	@col12 nvarchar(258) = null,
	@col13 nvarchar(258) = null,
	@col14 nvarchar(258) = null,
	@col15 nvarchar(258) = null,
	@col16 nvarchar(258) = null
as
	declare @id int, @ii int, @colnotfound nvarchar(258), @keycnt int
	select @id = object_id(@tablename)
	if (@id is null) begin
		RAISERROR (15001, -1, -1, @tablename)
		return 1
	end
	select @ii = 1
	create table #t1 (		/* Join into this... */
		i					int				NOT NULL,
		name				nvarchar(258)		NULL
	)
	insert #t1 values (1, @col1)
	insert #t1 values (2, @col2)
	insert #t1 values (3, @col3)
	insert #t1 values (4, @col4)
	insert #t1 values (5, @col5)
	insert #t1 values (6, @col6)
	insert #t1 values (7, @col7)
	insert #t1 values (8, @col8)
	insert #t1 values (9, @col9)
	insert #t1 values (10, @col10)
	insert #t1 values (11, @col11)
	insert #t1 values (12, @col12)
	insert #t1 values (13, @col13)
	insert #t1 values (14, @col14)
	insert #t1 values (15, @col15)
	insert #t1 values (16, @col16)
	delete #t1 where name is null

	select @colnotfound = min(name) from #t1 where name not in (select name from syscolumns where id = @id)
	if (@colnotfound is not null) begin
		RAISERROR (15253, -1, -1, @colnotfound, @tablename)
		return 1
	end
	select @ii = 1, @keycnt = count(*) from #t1

	/* Load all indexes which have the matching number of columns into a temp table, then eliminate those which don't qualify. */
	/* Remember the RID in the nc index is counted as a key */
	create table #i1 (
		i					int				NOT NULL
	)
	insert #i1 select indid from sysindexes where status & 0x1800 <> 0
		and id = @id and keycnt - (case indid when 1 then 0 else 1 end) = @keycnt
	while (@ii <= @keycnt) begin
		delete #i1 from #i1 i, #t1 t where t.i = @ii and index_col(@tablename, i.i, t.i) <> t.name
		select @ii = @ii + 1
	end

	/* The qualifying key will be the lowest indid (or the ONLY indid, if we disallow duplicate indexes), if any remain. */
	select name from sysindexes where id = @id and indid = (select min(i) from #i1)
go
/* End sp_MSmatchkey */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSforeach_worker'
print N''
go

/*
 * This is the worker proc for all of the "for each" type procs.  Its function is to read the
 * next replacement name from the cursor (which returns only a single name), plug it into the
 * replacement locations for the commands, and execute them.  It assumes the cursor "hCForEach"
 * has already been opened by its caller.
 */
create proc sp_MSforeach_worker
	@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null
as
	set nocount on
	declare @name nvarchar(517), @namelen int, @q1 nvarchar(2000), @q2 nvarchar(2000)
   declare @q3 nvarchar(2000), @q4 nvarchar(2000), @q5 nvarchar(2000)
	declare @q6 nvarchar(2000), @q7 nvarchar(2000), @q8 nvarchar(2000), @q9 nvarchar(2000), @q10 nvarchar(2000)
	declare @cmd nvarchar(2000), @replacecharindex int, @useq tinyint, @usecmd tinyint, @nextcmd nvarchar(2000)
   declare @namesave nvarchar(517), @nametmp nvarchar(517), @nametmp2 nvarchar(258)

	create table #qtemp (	/* Temp command storage */
		qnum				int				NOT NULL,
		qchar				nvarchar(2000)	NULL
	)
	open hCForEach
	fetch hCForEach into @name

	/* Loop for each database */
	while (@@fetch_status >= 0) begin
		/* Initialize. */

      /* save the original dbname */
      select @namesave = @name
		select @useq = 1, @usecmd = 1, @cmd = @command1, @namelen = datalength(@name)
		while (@cmd is not null) begin		/* Generate @q* for exec() */
			/*
			 * Parse each @commandX into a single executable batch.
			 * Because the expanded form of a @commandX may be > OSQL_MAXCOLLEN_SET, we'll need to allow overflow.
			 * We also may append @commandX's (signified by '++' as first letters of next @command).
			 */
			select @replacecharindex = charindex(@replacechar, @cmd)
			while (@replacecharindex <> 0) begin

            /* 7.0, if name contains ' character, and the name has been single quoted in command, double all of them in dbname */
            /* if the name has not been single quoted in command, do not doulbe them */
            /* if name contains ] character, and the name has been [] quoted in command, double all of ] in dbname */
            select @name = @namesave
            select @namelen = datalength(@name)
            declare @tempindex int
            if (substring(@cmd, @replacecharindex - 1, 1) = N'''') begin
               /* if ? is inside of '', we need to double all the ' in name */
               select @name = REPLACE(@name, N'''', N'''''')
            end else if (substring(@cmd, @replacecharindex - 1, 1) = N'[') begin
               /* if ? is inside of [], we need to double all the ] in name */
               select @name = REPLACE(@name, N']', N']]')
            end else if ((@name LIKE N'%].%]') and (substring(@name, 1, 1) = N'[')) begin
               /* ? is NOT inside of [] nor '', and the name is in [owner].[name] format, handle it */
               /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */
               select @tempindex = charindex(N'].[', @name)
               select @nametmp  = substring(@name, 2, @tempindex-2 )
               select @nametmp2 = substring(@name, @tempindex+3, len(@name)-@tempindex-3 )
               select @nametmp  = REPLACE(@nametmp, N']', N']]')
               select @nametmp2 = REPLACE(@nametmp2, N']', N']]')
               select @name = N'[' + @nametmp + N'].[' + @nametmp2 + ']'
            end else if ((@name LIKE N'%]') and (substring(@name, 1, 1) = N'[')) begin
               /* ? is NOT inside of [] nor '', and the name is in [name] format, handle it */
               /* j.i.c., since we should not fall into this case */
               /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */
               select @nametmp = substring(@name, 2, len(@name)-2 )
               select @nametmp = REPLACE(@nametmp, N']', N']]')
               select @name = N'[' + @nametmp + N']'
            end
            /* Get the new length */
            select @namelen = datalength(@name)

            /* start normal process */
				if (datalength(@cmd) + @namelen - 1 > 2000) begin
					/* Overflow; put preceding stuff into the temp table */
					if (@useq > 9) begin
						raiserror 55555 N'sp_MSforeach_worker assert failed:  command too long'
						close hCForEach
						deallocate hCForEach
						return 1
					end
					if (@replacecharindex < @namelen) begin
						/* If this happened close to beginning, make sure expansion has enough room. */
						/* In this case no trailing space can occur as the row ends with @name. */
						select @nextcmd = substring(@cmd, 1, @replacecharindex)
						select @cmd = substring(@cmd, @replacecharindex + 1, 2000)
						select @nextcmd = stuff(@nextcmd, @replacecharindex, 1, @name)
						select @replacecharindex = charindex(@replacechar, @cmd)
						insert #qtemp values (@useq, @nextcmd)
						select @useq = @useq + 1
						continue
					end
					/* Move the string down and stuff() in-place. */
					/* Because varchar columns trim trailing spaces, we may need to prepend one to the following string. */
					/* In this case, the char to be replaced is moved over by one. */
					insert #qtemp values (@useq, substring(@cmd, 1, @replacecharindex - 1))
					if (substring(@cmd, @replacecharindex - 1, 1) = N' ') begin
						select @cmd = N' ' + substring(@cmd, @replacecharindex, 2000)
						select @replacecharindex = 2
					end else begin
						select @cmd = substring(@cmd, @replacecharindex, 2000)
						select @replacecharindex = 1
					end
					select @useq = @useq + 1
				end
				select @cmd = stuff(@cmd, @replacecharindex, 1, @name)
				select @replacecharindex = charindex(@replacechar, @cmd)
			end

			/* Done replacing for current @cmd.  Get the next one and see if it's to be appended. */
			select @usecmd = @usecmd + 1
			select @nextcmd = case (@usecmd) when 2 then @command2 when 3 then @command3 else null end
			if (@nextcmd is not null and substring(@nextcmd, 1, 2) = N'++') begin
				insert #qtemp values (@useq, @cmd)
				select @cmd = substring(@nextcmd, 3, 2000), @useq = @useq + 1
				continue
			end

			/* Now exec() the generated @q*, and see if we had more commands to exec().  Continue even if errors. */
			/* Null them first as the no-result-set case won't. */
			select @q1 = null, @q2 = null, @q3 = null, @q4 = null, @q5 = null, @q6 = null, @q7 = null, @q8 = null, @q9 = null, @q10 = null
			select @q1 = qchar from #qtemp where qnum = 1
			select @q2 = qchar from #qtemp where qnum = 2
			select @q3 = qchar from #qtemp where qnum = 3
			select @q4 = qchar from #qtemp where qnum = 4
			select @q5 = qchar from #qtemp where qnum = 5
			select @q6 = qchar from #qtemp where qnum = 6
			select @q7 = qchar from #qtemp where qnum = 7
			select @q8 = qchar from #qtemp where qnum = 8
			select @q9 = qchar from #qtemp where qnum = 9
			select @q10 = qchar from #qtemp where qnum = 10
			truncate table #qtemp
			exec (@q1 + @q2 + @q3 + @q4 + @q5 + @q6 + @q7 + @q8 + @q9 + @q10 + @cmd)
			select @cmd = @nextcmd, @useq = 1
		end /* while @cmd is not null, generating @q* for exec() */

		/* All commands done for this name.  Go to next one. */
		fetch hCForEach into @name
	end /* while FETCH_SUCCESS */
	close hCForEach
	deallocate hCForEach
	return 0
go

/* End sp_MSforeach_worker */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSforeachdb'
print N''
go

/*
 * The following table definition will be created by SQLDMO at start of each connection.
 * We don't create it here temporarily because we need it in Exec() or upgrade won't work.
 */

create proc sp_MSforeachdb
	@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
	@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
	/* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
	/* @precommand and @postcommand may be used to force a single result set via a temp table. */

	/* Preprocessor won't replace within quotes so have to use str(). */
	declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
	select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
	select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
	select @dbinaccessible = N'0x80000000'		/* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */

	if (@precommand is not null)
		exec(@precommand)

	declare @origdb nvarchar(128)
	select @origdb = db_name()

	/* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
   /* Create the select */
	exec(N'declare hCForEach cursor for select name from master..sysdatabases d ' +
			N' where (d.status & ' + @inaccessible + N' = 0)' +
			N' and ((DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1)) or ' +
			N' ( DATABASEPROPERTY(d.name, ''issingleuser'') = 1 and not exists ' +
			N' (select * from master..sysprocesses p where dbid = d.dbid and p.spid <> @@spid)))' )

	declare @retval int
	select @retval = @@error
	if (@retval = 0)
		exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

	if (@retval = 0 and @postcommand is not null)
		exec(@postcommand)

   declare @tempdb nvarchar(258)
   SELECT @tempdb = REPLACE(@origdb, N']', N']]')
   exec (N'use ' + N'[' + @tempdb + N']')

	return @retval
go
/* End sp_MSforeachdb */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSforeachtable'
print N''
go

create proc sp_MSforeachtable
	@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null,
   @command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null,
	@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
	/* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set */
	/* @precommand and @postcommand may be used to force a single result set via a temp table. */

	/* Preprocessor won't replace within quotes so have to use str(). */
	declare @mscat nvarchar(12)
	select @mscat = ltrim(str(convert(int, 0x0002)))

	if (@precommand is not null)
		exec(@precommand)

	/* Create the select */
   exec(N'declare hCForEach cursor for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' from sysobjects o '
         + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '
         + @whereand)
	declare @retval int
	select @retval = @@error
	if (@retval = 0)
		exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

	if (@retval = 0 and @postcommand is not null)
		exec(@postcommand)

	return @retval
go
/* End sp_MSforeachtable */

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

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



create proc sp_MSloginmappings
	@loginname nvarchar(258) = 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 @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 loginname = @loginname

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

	if (@numlogins = 0) begin
		RAISERROR (15007, -1, -1, @loginname)		/* Login not found */
		return 1
	end
	if (@checkmultilogin <> 0) begin
      /* We do not want to allow everybody to execute this SP */
		if (is_member(N'db_ddladmin') <> 1 and is_member(N'db_owner') <> 1 and is_member(N'db_accessadmin') <> 1 and is_member(N'db_securityadmin') <> 1 and (@numlogins > 1 or suser_sid() <> suser_sid(@loginname))) begin
			RAISERROR (14301, -1, -1, N'')				/* Only sa can see other than the current login */
			return 1
		end
	end
	if (@loginname is not null)
		select @whereloginname = N' and loginname = ''' + @loginname + N''''
   else
      select @whereloginname = N' '

	/*
	 * 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			nvarchar(128)		NULL,
		DBName				nvarchar(128)		NULL,
		UserName			nvarchar(128)		NULL,
		AliasName			nvarchar(128)		NULL
	)
	if (@flags & 0x01 <> 0) begin
		INSERT #loginmappings select l.loginname, db_name(), u.name, null from master..syslogins l, sysusers u where l.sid = u.sid and l.loginname is not NULL
		/*
		 * We only allow multi-db on a 6.x server because dynamic exec() didn't exist before then,
		 * hence there is no way to loop thru every database.  This is caught in SQLDMO so no
		 * need for error message here; we'll just return no result sets.
		 */
	end else begin
		exec @retval = sp_MSforeachdb
			N'use [?] INSERT #loginmappings select l.loginname, db_name(), u.name, null from master..syslogins l, sysusers u where l.sid = u.sid and l.loginname is not NULL'
		if (@retval <> 0)
			return 1
		insert #loginmappings select l.loginname, null, null, null from master..syslogins l where l.loginname not in (select LoginName from #loginmappings) and l.loginname is not NULL
	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).
	 */
	exec(N'declare hCForEachLogin cursor for select loginname from master..syslogins where loginname is not NULL ' + @whereloginname + N' order by loginname')
	if (@@error = 0)
		open hCForEachLogin
	if (@@error <> 0)
		return @@error
	fetch hCForEachLogin into @name
	while (@@fetch_status >= 0) begin
		if ((@flags & 0x01 = 0) or exists (select * from #loginmappings where LoginName like @name))
			select * from #loginmappings where LoginName like @name
		fetch hCForEachLogin into @name
	end /* FETCH_SUCCESS */
	close hCForEachLogin
	deallocate hCForEachLogin
	return @@error
go
/* End sp_MSloginmappings */

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

create procedure sp_MSuniquename
	@seed nvarchar(128), @start int = null
as
	/* Return a unique name for sysobjects, based on a passed-in seed. */
	set nocount on
	declare @i int, @append nvarchar(10), @seedlen int, @temp nvarchar(128), @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 OSQL_DBLSYSNAME_SET for @append. */
		/* We'll work our way back along the string if more room needed (pathological user). */
		select @append = ltrim(str(@i)) + N'__' + ltrim(str(@@spid))
		if (@recalcseedlen = @i or @seedcharlen = 0)
		begin
			while @recalcseedlen <= @i
				select @recalcseedlen = @recalcseedlen * 10
			select @seedcharlen = @seedlen
			if ((@seedlen + datalength(@append)) > 128) begin
				select @seedlen = 128 - datalength(@append)

				/* Get the charlen of this datalength for the substring() call. */
				select @seedcharlen = @seedlen
			   /* exec sp_GetMBCSCharLen @seed, @seedlen, @seedcharlen out */
			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 N''
print N'Creating sp_MSkilldb'
print N''
go

sp_configure updat, 1
go
reconfigure with override
go

create proc sp_MSkilldb
	@dbname nvarchar(258)
as
	if (@@trancount > 0) begin
		RAISERROR (15002, -1, -1, N'sp_MSkilldb')
		return 1
	end

	if (is_member(N'db_owner') <> 1 and is_member(N'db_ddladmin') <> 1) begin
		RAISERROR (15003, -1, -1, N'')
		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
		declare @len int
		select @len = datalength(@dbname)
		RAISERROR (2702, -1, -1, @len, @dbname)
		return 1
	end
	dbcc dbrepair(@dbname, dropdb)
	return 0
go

sp_configure updat, 0
go
reconfigure with override
go

/* End sp_MSkilldb */

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

create proc sp_MSobjectprivs
	@objname nvarchar(776) = null,
	@mode nvarchar(10) = N'object',	
	@objid int = null,				
	@srvpriv int = null,			
	@prottype int = null,			
	@grantee nvarchar(258) = null,		
   @flags int = 0
as
   /* mode    : '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   : ID of the object we're querying */
   /* srvpriv : privilege that we're querying for (e.g. select) */
   /* prottype: Protect type, e.g. GRANT/REVOKE */
   /* grantee : Grantee name. */

   /*** @flags added for DaVinci uses.  If the bit isn't set, use 6.5 ***/
   /*** sp_MSobjectprivs '%s'                                         ***/

	/* @flags is for daVinci */
	if (@flags is null)
		select @flags = 0

	/* 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)) &
					-- 6.5 changed so that the bit 0 position is an "invert the bits" indicator:
					--		when 0, behaviour is the same as in prior versions, and other bits
					--			indicate columns with the specified privilege
					--		when 1, the other bits are indicate columns lacking the specified privilege
					a.high <> (case when (substring(isnull(p.columns, 0x00), 1, 1) & 1 = 0) then 0 else a.high end)
					and col_name(p.id, a.number) is not null
					and a.type = N'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 N'us%') begin
	   select @uid = user_id(@objname)
	end else begin
      select @id = object_id(@objname)
   end
	if (@id is null and @uid is null) begin
		RAISERROR (15001, -1, -1, @objname)
		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
	)

   /* This is the original code */
   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,
		grantor			int
	)

	create table #tmp(
		action   int   NOT NULL,
		uid      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, p.grantor
			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 = N'P'

      declare @count int, @whataction int, @whatid int, @dup int

      /* First pass to correct duplicates */
      select @count = count(*) from #output where id = @id and colid = 0 and protecttype = 205
      if ( @count > 0 ) begin
         /* We might have duplicate rows for permission on single coulmn(s) at this point */
         /* Use a fake cursor to remove the duplicates first. */
         insert #tmp select action, uid from #output where id = @id and colid = 0 and protecttype = 205
         select @whataction = min(action) from #tmp
         select @whatid = uid from #tmp where action = @whataction
         while (@whataction is not null) begin
            delete #output where (@whatid = uid) and (colid <> 0) and (protecttype = 205)
            delete #tmp where @whatid = uid
            select @whataction = min(action) from #tmp
            select @whatid = uid from #tmp where action = @whataction
         end
         delete #tmp
      end

      /* Second pass to correct protect type */
      select @count = count(*) from #output where id = @id and colid = 0
      if ( @count > 0 ) begin
         /* use another fake cursor to correct the protecttype */
         /* if there are multiple rows in #output for the same id and action, and if colid = 0 exist, then other rows should have 206, not 205 */
         insert #tmp select action, uid from #output where id = @id and colid = 0
         select @whataction = min(action) from #tmp
         select @whatid = uid from #tmp where action = @whataction
         while (@whataction is not null) begin
               update #output set protecttype = 206 where id = @id and colid <> 0 and @whataction = action and @whatid = uid
               delete #tmp where action = @whataction and @whatid = uid
               select @whataction = min(action) from #tmp
               select @whatid = uid from #tmp where action = @whataction
         end
         delete #tmp
      end

		/* 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), N'column' = col_name(p.id, p.colid), p.uid, N'username' = user_name(p.uid),
			p.protecttype, o.name, N'owner' = user_name(o.uid), p.id, N'grantor' = user_name(p.grantor)
		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 N''
print N'Creating sp_MSSQLDMO70_version'
print N''
go

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

/*
 * The following two scripts must retain the SQLOLE nomenclature as we provide them to be an informative
 * notification to downlevel connections.
 */
create procedure sp_MSSQLOLE65_version
as
	RAISERROR 55555 N'You must upgrade your SQL Enterprise Manager and SQL-DMO (SQLOLE) to version 7.0 (SQLDMO) to connect to this server.'
   return 1
go

create procedure sp_MSSQLOLE_version
as
	RAISERROR 55555 N'You must upgrade your SQL Enterprise Manager and SQL-DMO (SQLOLE) to version 7.0 (SQLDMO) to connect to this server.'
   return 1
go

/*******************************************************************************/
print N''
print N'Creating sp_MSscriptdatabase'
print N''
go
create procedure sp_MSscriptdatabase
@dbname nvarchar(258)
as
   /* verify */
   declare @id int
   select @id = dbid from master..sysdatabases where name = @dbname
   if (@id is null)
   begin
      RAISERROR (15001, -1, -1, @dbname)
      return 1
   end

   /* Ready to get to work */
   declare @dbTempname nvarchar(258)
   SELECT @dbTempname = REPLACE(@dbname, N']', N']]')
   exec (N'[' + @dbTempname + N']' + N'..sp_MSscriptdb_worker ')
go
/* End sp_MSscriptdatabase */

/*******************************************************************************/
print N''
print N'Creating sp_MSscriptdb_worker'
print N''
go
create procedure sp_MSscriptdb_worker
as
	set nocount on

   declare @PageSize int;
   select @PageSize = low from master..spt_values where number = 1 and type = N'E'

	create table #tempFG
	(
     cDefault     int,                                  /* 1 for default FG, 0 for user defined */
     cDBFile      int,                                  /* 1 for DB file, 0 for Log file */
	  cSize        int,                                  /* in 8K page */
	  cMaxSize     int,
	  cGrowth      int,
     cGrowthType  int,                                  /* 1 for GrowthInMB, 0 for GrowthInPercent */
     cFGName      nvarchar(132) NOT NULL,      /* FG name */
	  cName        nchar(132) NOT NULL,         /* Logical */
	  cFileName    nchar(264) NOT NULL,        /* Physical */
	)

   /* Default FileGroup first, which should cover all the log files */
   /* This one to pick up all the db files in Primary file group, while group id = 1 */
   insert #tempFG select 1, 1, ((o.size * @PageSize)/1024)/1024, (case when (o.maxsize < 1) then o.maxsize else ((o.maxsize * @PageSize)/1024)/1024 end), o.growth, (case when (o.status & 0x100000 = 0) then 1 else 0 end), g.groupname, o.name, o.filename from sysfiles o, sysfilegroups g where g.groupid = 1 and g.groupid = o.groupid and (o.status & 0x40) = 0
   /* This one to pick up all the log files in Primary file group, while group id = 0, note that group id 0 does not exist in sysfilegroups */
   insert #tempFG select 1, 0, ((o.size * @PageSize)/1024)/1024, (case when (o.maxsize < 1) then o.maxsize else ((o.maxsize * @PageSize)/1024)/1024 end), o.growth, (case when (o.status & 0x100000 = 0) then 1 else 0 end), N'PRIMARY', o.name, o.filename from sysfiles o where o.groupid = 0 and (o.status & 0x40) <> 0
   /* Other FileGroups, we should have DBFiles, no log files */

	create table #tempID
   (
		cGroupID int
	)
   insert #tempID select groupid from sysfilegroups where groupid <> 1

   declare @FGid int
	exec(N'declare hC cursor for select cGroupID from #tempID')
	open hC
	fetch hC into @FGid
	while (@@fetch_status >= 0) begin
      insert #tempFG select 0, 1, ((o.size * @PageSize)/1024)/1024, (case when (o.maxsize < 1) then o.maxsize else ((o.maxsize * @PageSize)/1024)/1024 end), o.growth, (case when (o.status & 0x100000 = 0) then 1 else 0 end), g.groupname, o.name, o.filename from sysfiles o, sysfilegroups g where g.groupid = @FGid and g.groupid = o.groupid and (o.status & 0x40) = 0
      fetch hC into @FGid
   end
	deallocate hC

   select * from #tempFG
   DROP TABLE #tempFG

go
/* End sp_MSscriptdb_worker */

/*******************************************************************************/
/*******************************************************************************/
/* exec sp_MSdbuseraccess 'perm', 'dbname' -- selecting priv bit from specified db                       */
/* exec sp_MSdbuseraccess 'db', 'dbname'   -- select databases, need to change db if dbname is specified */
/* exec sp_MSdbuseraccess 'init', 'dbname' -- noop                                                       */
/*******************************************************************************/
print N''
print N'Creating sp_MSdbuseraccess'
print N''
go

create proc sp_MSdbuseraccess
	@mode nvarchar(10) = N'perm', @qual nvarchar(128) = N'%'
as

   set nocount on

   declare @accessbit int
	if (lower(@mode) like N'perm%') begin
      /* verify */
      declare @id int, @stat int, @inval int
      select @id = dbid, @stat = status from master..sysdatabases where name = @qual
      if (@id is null) begin
         RAISERROR (15001, -1, -1, @qual)
         return 1
      end

      /* Can we access this db? */
      declare @single int
      select @single = DATABASEPROPERTY( @qual, N'issingleuser' )
/*      if ((@single <> 0) or ((@stat & SQLDMODBStat_Inaccessible) <> 0)) begin  */
      if ((@single <> 0) or
         (DATABASEPROPERTY(@qual, N'isdetached') <> 0) or
         (DATABASEPROPERTY(@qual, N'isshutdown') <> 0) or
         (DATABASEPROPERTY(@qual, N'issuspect') <> 0) or
         (DATABASEPROPERTY(@qual, N'isoffline') <> 0) or
         (DATABASEPROPERTY(@qual, N'isinload') <> 0) or
         (DATABASEPROPERTY(@qual, N'isinrecovery') <> 0) or
         (DATABASEPROPERTY(@qual, N'isnotrecovered') <> 0)) begin
         select @inval = 0x80000000
         select @inval
         return 0
      end
      select @accessbit = has_dbaccess(@qual)
      if ( @accessbit <> 1) begin
         select @inval = 0x40000000
         select @inval
         return 0
      end

      /** OK, we can access this db, need to go to the specified database to get priv bit **/
      declare @dbTempname nvarchar(258)
      declare @tempindex int
      SELECT @dbTempname = REPLACE(@qual, N']', N']]')
      exec (N'[' + @dbTempname + N']' + N'..sp_MSdbuserpriv ')
      return 0
   end

   /* If 'db', we want to know if what kind of access we have to the specified databases */
   /* If we are not in master, then we are selecting single database, we want to correct role bit to save round trip */
   if (lower(@mode) like N'db%') begin
      /* Make sure we're either in master or only doing it to current db. */
      declare @dbrole int
      select @dbrole = 0x0000

      if (db_id() <> 1)
         select @qual = db_name()

      /* If dbname contains ', double it for the cursor, since cursor statement is inside of '' */
      declare @qual2 nvarchar(128)
      SELECT @qual2 = REPLACE(@qual, N'''', N'''''')

      /* Preprocessor won't replace within quotes so have to use str(). */
      declare @invalidlogin nvarchar(12)
      select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
      declare @inaccessible nvarchar(12)
      select @inaccessible = ltrim(str(convert(int, 0x80000000), 11))

      /* We can't 'use' a database with a version below the minimum. */
      /* SQL6.0 minimum is 406; SQL65 requires 408.  SQL70 database version is 408 now, it might change later */
      declare @mindbver smallint
      if (@@microsoftversion >= 0x07000000)
         select @mindbver = 408
      else
         select @mindbver = 406

      create table #TmpDbUserProfile (
         dbid        int NOT NULL PRIMARY KEY,
         accessperms int NOT NULL
         )

      /* Select all matching databases -- we want an entry even for inaccessible ones. */
      declare @dbid smallint, @dbidstr nvarchar(12), @dbstat int, @dbname nvarchar(258), @dbver smallint
      declare @dbbits int, @dbbitstr nvarchar(12)

      /* !!! work around here, if name contains '[', LIKE operator can't find it, since LIKE operator it treating '[' as a wild char */
      /* !!! but @qual2 might be '%', then = operator does not work */
      declare @temp int
      select @tempindex = charindex(N'[', @qual2)
      if (@tempindex <> 0)
         exec(N'declare hCdbs cursor for select name, dbid, status, version from master..sysdatabases where name = N''' + @qual2 + N'''')
      else
         exec(N'declare hCdbs cursor for select name, dbid, status, version from master..sysdatabases where name like N''' + @qual2 + N'''')

      open hCdbs

      /* Loop for each database, and if it's accessible, recursively call ourselves to add it. */
      fetch hCdbs into @dbname, @dbid, @dbstat, @dbver
      while (@@fetch_status >= 0) begin
         /* Preprocessor won't replace within quotes so have to use str(). */
         select @dbidstr = ltrim(str(convert(int, @dbid)))

         /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
         declare @single_lockedout int
         select @single_lockedout = DATABASEPROPERTY( @dbname, N'issingleuser' )
         if (@single_lockedout <> 0)
            select @single_lockedout = 0 where not exists
               (select * from master..sysprocesses p where dbid = @dbid and p.spid <> @@spid)

         /* First see if the db is accessible (not in load, recovery, offline, single-use with another user besides us, etc.) */
/*         if ((@single_lockedout <> 0) or ((@dbstat & SQLDMODBStat_Inaccessible) <> 0) or (@dbver < @mindbver)) begin   */
         if ((@single_lockedout <> 0) or
            (@dbver < @mindbver) or
            (DATABASEPROPERTY(@dbname, N'isdetached') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isshutdown') <> 0) or
            (DATABASEPROPERTY(@dbname, N'issuspect') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isoffline') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isinload') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isinrecovery') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isnotrecovered') <> 0) ) begin
            /* Inaccessible, but we can set dbo if we're sa or suser_id() is db owner sid. */
            exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @inaccessible + N')')
            end
         else begin
            /* Find out whether the current user has access to the database */
            select @accessbit = has_dbaccess(@dbname)
            if ( @accessbit <> 1) begin
               exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @invalidlogin + N')')
               end
            else begin
               /* Yes, current user does have access to this database, we are not trying to get priv at this point */
               select @dbbits = 0x01ff
               select @dbbitstr = ltrim(convert(nvarchar(12), @dbbits))
               exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @dbbitstr + N')')
               end
            end

         fetch hCdbs into @dbname, @dbid, @dbstat, @dbver
      end /* while FETCH_SUCCESS */
      close hCdbs
      deallocate hCdbs

      /* 1. If on all databases, then dbrole is dummy, need to get it later */
      /* 2. Do not double the ' character(s) in database name */
      /* 3. To speed up connection, accessperms column only indicate whether the login user can access the db, it does not contain */
      /*    permission info, we will retrieve the permission info through sp_MSdbuserpriv when necessary */
      /* !!! work around here, if name contains '[', LIKE operator can't find it, since LIKE operator it treating '[' as a wild char */
      /* !!! but @qual2 might be '%', then = operator does not work */
      if (@tempindex <> 0)
         select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, lSize = 0, NonDbo = 0, Status = o.status, spaceavail = 0,
            LogOnSepDev = 1, o.category, t.accessperms, @dbrole, DatabaseProperty(o.name, 'isfulltextenabled'), o.status2 from master..sysdatabases o left outer join #TmpDbUserProfile t on t.dbid = o.dbid where o.name = @qual order by o.name
      else
         select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, lSize = 0, NonDbo = 0, Status = o.status, spaceavail = 0,
            LogOnSepDev = 1, o.category, t.accessperms, @dbrole, DatabaseProperty(o.name, 'isfulltextenabled'), o.status2 from master..sysdatabases o left outer join #TmpDbUserProfile t on t.dbid = o.dbid where o.name like @qual order by o.name
      DROP TABLE #TmpDbUserProfile
      return 0
   end
go
/* End sp_MSdbuseraccess */

/*******************************************************************************/
/*******************************************************************************/
/* exec sp_MSdbuserpriv 'serv'  -- selecting the server (master db) user profile, just create db priv, if sa, 7 */
/* exec sp_MSdbuserpriv 'role'  -- selecting role membership for current db                                     */
/* exec sp_MSdbuserpriv 'ver'   -- selectversion70                                                              */
/* exec sp_MSdbuserpriv 'perm'  -- selecting user priv bit for the current db                                   */
/*******************************************************************************/
print N''
print N'Creating sp_MSdbuserpriv'
print N''
go

create proc sp_MSdbuserpriv
	@mode nvarchar(10) = N'perm'
as

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









   set nocount on
   declare @bits int, @status int, @prot int
   declare @dbrole int, @dbrolestr nvarchar(12)

   /* If 'srv', we're selecting the server (master db) user profile - currently, just create db priv. */
   if (lower(@mode) like N'serv%')
      begin
      select @bits = 0x0000
      if (user_id() = 1 or is_srvrolemember(N'sysadmin') = 1 or is_member(N'db_owner') = 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 status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 203 and uid = user_id(N'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(N'sp_addextendedproc')
         if (@prot is null) select @prot = protecttype from sysprotects where action = 224 and uid = (select status from sysusers where uid = user_id()) and id = object_id(N'sp_addextendedproc')
         if (@prot is null) select @prot = protecttype from sysprotects where action = 224 and uid = user_id(N'public') and id = object_id(N'sp_addextendedproc')
         if (@prot = 205) select @bits = @bits | 0x0004
         end
      select @bits
      return 0
      end

   /* If 'perm', we're selecting the current database priv and role membership for the login user. */
	if (lower(@mode) like N'role%' or lower(@mode) like N'ver%' or lower(@mode) like N'perm%')
      begin
      if (user_id() = 1 or is_srvrolemember(N'sysadmin') = 1 or is_member(N'db_owner') = 1)
         begin
         /* sa/Dbo has everything. */
         select @bits = 0x01ff
         end
      else begin
         /* Not dbo so get individual privileges */
         select @bits = 0x0000, @status = status 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 status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 198 and uid = user_id(N'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 status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 207 and uid = user_id(N'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 status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 222 and uid = user_id(N'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 status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 228 and uid = user_id(N'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 status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 233 and uid = user_id(N'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 status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 235 and uid = user_id(N'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 status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 236 and uid = user_id(N'public')
         if (@prot = 205) select @bits = @bits | 0x0080

         select @prot = null select @prot = protecttype from sysprotects where action = 57 and uid = user_id()
         if (@prot is null) select @prot = protecttype from sysprotects where action = 57 and uid = (select status from sysusers where uid = user_id())
         if (@prot is null) select @prot = protecttype from sysprotects where action = 57 and uid = user_id(N'public')
         if (@prot = 205) select @bits = @bits | 0x0100
         end

      /* Get both Server and Database Role information */
      select @dbrole = 0x0000
      /* Server Roles */
      select @dbrole = (case when (is_srvrolemember(N'dbcreator') = 1) then @dbrole | 0x0001 else @dbrole end),
             @dbrole = (case when (is_srvrolemember(N'diskadmin') = 1) then @dbrole | 0x0002 else @dbrole end),
             @dbrole = (case when (is_srvrolemember(N'processadmin') = 1) then @dbrole | 0x0004 else @dbrole end),
             @dbrole = (case when (is_srvrolemember(N'securityadmin') = 1) then @dbrole | 0x0008 else @dbrole end),
             @dbrole = (case when (is_srvrolemember(N'serveradmin') = 1) then @dbrole | 0x0010 else @dbrole end),
             @dbrole = (case when (is_srvrolemember(N'setupadmin') = 1) then @dbrole | 0x0020 else @dbrole end),
             @dbrole = (case when (is_srvrolemember(N'sysadmin') = 1) then @dbrole | 0x0040 else @dbrole end),
      /* Database Roles */
             @dbrole = (case when (is_member(N'db_accessadmin') = 1) then @dbrole | 0x0080 else @dbrole end),
             @dbrole = (case when (is_member(N'db_datareader') = 1) then @dbrole | 0x0100 else @dbrole end),
             @dbrole = (case when (is_member(N'db_ddladmin') = 1) then @dbrole | 0x0200 else @dbrole end),
             @dbrole = (case when (is_member(N'db_denydatareader') = 1) then @dbrole | 0x0400 else @dbrole end),
             @dbrole = (case when (is_member(N'db_denydatawriter') = 1) then @dbrole | 0x0800 else @dbrole end),
             @dbrole = (case when (is_member(N'db_backupoperator') = 1) then @dbrole | 0x1000 else @dbrole end),
             @dbrole = (case when (is_member(N'db_owner') = 1) then @dbrole | 0x2000 else @dbrole end),
             @dbrole = (case when (is_member(N'db_securityadmin') = 1) then @dbrole | 0x4000 else @dbrole end),
             @dbrole = (case when (is_member(N'db_datawriter') = 1) then @dbrole | 0x8000 else @dbrole end)

      if (lower(@mode) like N'ver%')
         begin
         select @@version, N'login_id' = convert(int, suser_sid()), N'pagesize' = v.low, N'highbit' = v2.low, N'highbyte' = v3.low,
            N'casesens' = (case when (N'A' != N'a') then 1 else 0 end), @@spid, @@servername, is_srvrolemember(N'sysadmin'), @dbrole
            from master..spt_values v,master..spt_values v2,master..spt_values v3 where v.number=1 and v.type=N'E' and v2.number=2
            and v2.type=N'E' and v3.number=3 and v3.type=N'E'
         end
      else if (lower(@mode) like N'role%')
         begin
         select @dbrole
         end
      else if (lower(@mode) like N'perm%')
         begin
         select @bits
         end
      return 0
      end
go
/* End sp_MSdbuserpriv */


/*******************************************************************************/
print N''
print N'Creating sp_MShelpfulltextindex'
print N''
go

create proc sp_MShelpfulltextindex
   @tablename nvarchar(517)
as
   set nocount on

	create table #sphelpft
      (
  	   ind_name   nvarchar(128) NOT NULL,
      col1       nvarchar(128),
      col2       nvarchar(128),
      col3       nvarchar(128),
      col4       nvarchar(128),
      col5       nvarchar(128),
      col6       nvarchar(128),
      col7       nvarchar(128),
      col8       nvarchar(128),
      col9       nvarchar(128),
      col10      nvarchar(128),
      col11      nvarchar(128),
      col12      nvarchar(128),
      col13      nvarchar(128),
      col14      nvarchar(128),
      col15      nvarchar(128),
      col16      nvarchar(128)
      )

   /* all the possible full text unique indexes */
   declare @objid int
   select @objid = object_id(@tablename, N'local')
  	insert #sphelpft
         select i.name,
         columnproperty( @objid, index_col(@tablename, i.indid, 1), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 2), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 3), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 4), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 5), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 6), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 7), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 8), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 9), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 10), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 11), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 12), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 13), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 14), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 15), N'AllowsNull'),
         columnproperty( @objid, index_col(@tablename, i.indid, 16), N'AllowsNull')
         from sysindexes i where
         @objid = i.id and
         IndexProperty(@objid, i.name, N'IsUnique') = 1 and
         IndexProperty(@objid, i.name, N'UserKeyCount') = 1 and
         /* 450 byte MAX */
         exists (select * from syscolumns where id = @objid and name = Index_col(@tablename, IndexProperty(@objid, i.name, N'IndexId'), 1)
                 and length <= 450)

   /* Now we need to filter out the indexes which the associated key(s) are nullable */
   /* Each index can have up to 16 associated keys, all of them need to be non-nullalbe for the index to be qualified as a full text index */
   delete #sphelpft where col1 = 1 or col2 = 1 or col3 = 1 or col4 = 1 or col5 = 1 or col6 = 1 or col7 = 1 or col8 = 1 or
                          col9 = 1 or col10 = 1 or col11 = 1 or col12 = 1 or col13 = 1 or col14 = 1 or col15 = 1 or col16 = 1

   select ind_name from #sphelpft
   DROP TABLE #sphelpft

go
/* End sp_MShelpfulltextindex */

/*******************************************************************************/
print N''
print N'Creating sp_MShelpfulltextscript'
print N''
go

create proc sp_MShelpfulltextscript
   @tablename nvarchar(517)
as
   set nocount on

	declare @objid int
	select @objid = object_id(@tablename)
	if (@objid is null)
	begin
		RAISERROR (15001, -1, -1, @tablename)
		return 1
	end

   /* prepare the information for fulltext index scripting */
   declare @activate    int
   select @activate = OBJECTPROPERTY(@objid, N'TableFulltextCatalogId')
	if (@activate <> 0)
      begin
      declare @uniqueindex nvarchar(128)
      declare @catname     nvarchar(128)

      /* get unique index name */
      select @uniqueindex = i.name from sysindexes i where @objid = i.id and IndexProperty(@objid, i.name, N'IsFulltextKey') = 1
      /* get catalog name */
      select @catname = f.name from sysfulltextcatalogs f, sysobjects o where f.ftcatid = o.ftcatid and o.id = @objid
      if (@uniqueindex is not null and @catname is not null)
         begin
         /* is this table fulltext index activated? */
         select @activate = OBJECTPROPERTY(@objid, N'TableHasActiveFulltextIndex')
         select @uniqueindex, @catname, @activate
         end
      end

go
/* End sp_MShelpfulltextscript */


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

/**  Mark all the SPs as system objects **/
exec sp_MS_marksystemobject sp_MShelpcolumns
go
exec sp_MS_marksystemobject sp_MShelpindex
go
exec sp_MS_marksystemobject sp_MShelptype
go
exec sp_MS_marksystemobject sp_MSdependencies
go
exec sp_MS_marksystemobject sp_MStablespace
go
exec sp_MS_marksystemobject sp_MSindexspace
go
exec sp_MS_marksystemobject sp_MStablerefs
go
exec sp_MS_marksystemobject sp_MStablekeys
go
exec sp_MS_marksystemobject sp_MStablechecks
go
exec sp_MS_marksystemobject sp_MSsettopology
go
exec sp_MS_marksystemobject sp_MSmatchkey
go
exec sp_MS_marksystemobject sp_MSforeach_worker
go
exec sp_MS_marksystemobject sp_MSforeachdb
go
exec sp_MS_marksystemobject sp_MSforeachtable
go
exec sp_MS_marksystemobject sp_MSloginmappings
go
exec sp_MS_marksystemobject sp_MSuniquename
go
exec sp_MS_marksystemobject sp_MSkilldb
go
exec sp_MS_marksystemobject sp_MSobjectprivs
go
exec sp_MS_marksystemobject sp_MSSQLDMO70_version
go
exec sp_MS_marksystemobject sp_MSSQLOLE65_version
go
exec sp_MS_marksystemobject sp_MSSQLOLE_version
go
exec sp_MS_marksystemobject sp_MSscriptdatabase
go
exec sp_MS_marksystemobject sp_MSscriptdb_worker
go
exec sp_MS_marksystemobject sp_MSdbuseraccess
go
exec sp_MS_marksystemobject sp_MSdbuserpriv
go
exec sp_MS_marksystemobject sp_MShelpfulltextindex
go
exec sp_MS_marksystemobject sp_MShelpfulltextscript
go

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_MSobjectprivs to public
grant execute on sp_MSloginmappings to public
grant execute on sp_MStablekeys to public
grant execute on sp_MStablechecks to public
grant execute on sp_MStablerefs to public
grant execute on sp_MSsettopology to public
grant execute on sp_MSmatchkey to public
grant execute on sp_MSforeachdb to public
grant execute on sp_MSforeachtable to public
grant execute on sp_MSforeach_worker to public
grant execute on sp_MSSQLOLE_version to public
grant execute on sp_MSSQLOLE65_version to public
grant execute on sp_MSSQLDMO70_version to public
grant execute on sp_MSscriptdatabase to public
grant execute on sp_MSscriptdb_worker to public
grant execute on sp_MSdbuseraccess to public
grant execute on sp_MSdbuserpriv to public
grant execute on sp_MShelpfulltextindex to public
grant execute on sp_MShelpfulltextscript to public
go

















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

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

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

print ''
print 'Creating sp_MSfilterclause'
print ''
go

create procedure sp_MSfilterclause
	@publication nvarchar(258), @article nvarchar(258)
as
	/* Return a text column as multiple readtexts of maxcol length */
	declare @pubid int, @artid int
	select @pubid = pubid from syspublications where name = @publication
	if (@pubid is null) begin
		RAISERROR (15001, 11, -1, @publication)
		return 1
	end
	select @artid = artid from sysarticles where name = @article and pubid = @pubid
	if (@artid is null) begin
		RAISERROR (15001, 11, -1, @article)
		return 1
	end

	declare @val varbinary(16), @len int, @ii int, @chunk int
	-- filter clause is in unicode, the length is a half of the number of bytes.
	select @val = textptr(filter_clause), @len = datalength(filter_clause)/2 from sysarticles where artid = @artid and pubid = @pubid
	select @ii = 0, @chunk = 255

	/* Get all the rows of an maxcol size */
	while @len > @chunk begin
		readtext sysarticles.filter_clause @val @ii @chunk
		select @ii = @ii + @chunk, @len = @len - @chunk
	end

	/* Get the last chunk */
	if (@len > 0)
		readtext sysarticles.filter_clause @val @ii @len
	return 0
go
/* End sp_MSfilterclause */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print ''
print 'Creating sp_MSsubscriptions'
print ''
go

create procedure sp_MSsubscriptions
	@subscriber nvarchar(258) = '%', @dbname nvarchar(258) = '%', @mode nvarchar(10) = null
as
	if (@subscriber is null)
		select @subscriber = '%'
	if (@dbname is null)
		select @dbname = '%'

	/* Similar to sp_helpsubscription but intended for SQLDMO's EnumSubscriberSubscriptions, to report on the full */
	/* server so it needs to return the publication databasename as well, and all subscriptions, not just those active. */
	IF (@subscriber <> '%' and not exists
			(SELECT * FROM sysservers WHERE srvname = @subscriber AND (srvstatus & 0x0004) <> 0)) begin
		RAISERROR (14010, -1, -1)
		return 1
	end
	IF (@dbname <> '%' and db_id(@dbname) is null) begin
		declare @len int
		select @len = datalength(@dbname)
		RAISERROR (2702, -1, -1, @len, @dbname)
		return 1
	end
	if (@mode = 'drop' and is_member('db_owner') <> 1 and is_member('db_ddladmin') <> 1) begin
		RAISERROR (15003, -1, -1)
		return 1
	end

	create table #t1 (
		i					int					NOT NULL IDENTITY PRIMARY KEY,
		cSubscriber			nvarchar(128)			NOT NULL,
		cPubDBName			nvarchar(128)			NOT NULL,
		cPublication		nvarchar(60)			NOT NULL,
		cArticle			nvarchar(60)			NOT NULL
	)

	/* Preprocessor won't replace within quotes so have to use str(). */
	declare @pubbit nvarchar(12)
	select @pubbit = ltrim(str(convert(int, 0x0001)))
	exec ('declare hC_mssub cursor for select name from master..sysdatabases where category & ' + @pubbit + ' <> 0 and name like ''' + @dbname + '''')
	open hC_mssub
	fetch hC_mssub into @dbname
	while (@@fetch_status >= 0) begin
		exec ('insert #t1 select ss.srvname, ''' + @dbname + ''', pub.name, art.name from ' +
				@dbname + '..syssubscriptions sub, ' +
				@dbname + '..syspublications pub, ' +
				@dbname + '..sysarticles art, ' +
				'master..sysservers ss where ss.srvname like ''' + @subscriber + ''' and sub.srvid = ss.srvid and art.pubid = pub.pubid and sub.artid = art.artid')
		fetch hC_mssub into @dbname
	end
	close hC_mssub
	deallocate hC_mssub

	if (@mode = 'drop') begin
		declare @pubname nvarchar(60), @artname nvarchar(60)
		exec ('declare hC_mssub cursor for select cSubscriber, cPubDBName, cPublication, cArticle from #t1')
		open hC_mssub
		fetch hC_mssub into @subscriber, @dbname, @pubname, @artname

		/* Would be nice to do this in a transaction but schema modifications in transaction aren't supported. */
		while (@@fetch_status >= 0) begin
			exec (@dbname + '..sp_dropsubscription ''' + @pubname + ''', ''' + @artname + ''', ''' + @subscriber + '''')
			fetch hC_mssub into @subscriber, @dbname, @pubname, @artname
		end
		close hC_mssub
		deallocate hC_mssub
	end else begin
		select cSubscriber, cPubDBName, cPublication, cArticle from #t1 order by cSubscriber, cPubDBName, cPublication, cArticle
	end
	drop table #t1
go
/* End sp_MSsubscriptions */

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

exec sp_MS_marksystemobject sp_MSfilterclause
go
exec sp_MS_marksystemobject sp_MSsubscriptions
go

grant execute on sp_MSfilterclause to public
grant execute on sp_MSsubscriptions to public














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

if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = 'sp_MSgetalertinfo')
	drop procedure sp_MSgetalertinfo
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = 'sp_MSsetalertinfo')
	drop procedure sp_MSsetalertinfo
go

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

print N''
print N'Creating sp_MSgetalertinfo'
print N''
go
create procedure sp_MSgetalertinfo
	@includeaddresses bit = 0
as
	/* Return all alert info at one go, for performance reasons. */
	declare @FailSafeOperator nvarchar(255)
	declare @NotificationMethod int
	declare @ForwardingServer nvarchar(255)
	declare @ForwardingSeverity int
	declare @ForwardAlways int
	declare @PagerToTemplate nvarchar(255)
	declare @PagerCCTemplate nvarchar(255)
	declare @PagerSubjectTemplate nvarchar(255)
	declare @PagerSendSubjectOnly int
	declare @FailSafeEmailAddress nvarchar(255)
	declare @FailSafePagerAddress nvarchar(255)
	declare @FailSafeNetSendAddress nvarchar(255)

	exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeOperator', @param = @FailSafeOperator OUT, @no_output = N'no_output'
	exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertNotificationMethod', @param = @NotificationMethod OUT, @no_output = N'no_output'
	exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardingServer', @param = @ForwardingServer OUT, @no_output = N'no_output'
	exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardingSeverity', @param = @ForwardingSeverity OUT, @no_output = N'no_output'
	exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardAlways', @param = @ForwardAlways OUT, @no_output = N'no_output'
	exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerToTemplate', @param = @PagerToTemplate OUT, @no_output = N'no_output'
	exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerCCTemplate', @param = @PagerCCTemplate OUT, @no_output = N'no_output'
	exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerSubjectTemplate', @param = @PagerSubjectTemplate OUT, @no_output = N'no_output'
	exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerSendSubjectOnly', @param = @PagerSendSubjectOnly OUT, @no_output = N'no_output'

	if (@includeaddresses <> 0) begin
		exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeEmailAddress', @param = @FailSafeEmailAddress OUT, @no_output = N'no_output'
		exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafePagerAddress', @param = @FailSafePagerAddress OUT, @no_output = N'no_output'
		exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeNetSendAddress', @param = @FailSafeNetSendAddress OUT, @no_output = N'no_output'
	end

	select
		AlertFailSafeOperator = @FailSafeOperator,
		AlertNotificationMethod = @NotificationMethod,
		AlertForwardingServer = @ForwardingServer,
		AlertForwardingSeverity = @ForwardingSeverity,
		AlertPagerToTemplate = @PagerToTemplate,
		AlertPagerCCTemplate = @PagerCCTemplate,
		AlertPagerSubjectTemplate = @PagerSubjectTemplate,
		AlertPagerSendSubjectOnly = @PagerSendSubjectOnly,
		AlertForwardAlways = ISNULL(@ForwardAlways, 0)

	if (@includeaddresses <> 0)
		select
			AlertFailSafeEmailAddress = @FailSafeEmailAddress,
			AlertFailSafePagerAddress = @FailSafePagerAddress,
			AlertFailSafeNetSendAddress = @FailSafeNetSendAddress
go
/* End sp_MSgetalertinfo */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSsetalertinfo'
print N''
go
create procedure sp_MSsetalertinfo
	@failsafeoperator nvarchar(255) = null,
	@notificationmethod int = null,
	@forwardingserver nvarchar(255) = null,
	@forwardingseverity int = null,
	@pagertotemplate nvarchar(255) = null,
	@pagercctemplate nvarchar(255) = null,
	@pagersubjecttemplate nvarchar(255) = null,
	@pagersendsubjectonly int = null,
	@failsafeemailaddress nvarchar(255) = null,
	@failsafepageraddress nvarchar(255) = null,
	@failsafenetsendaddress nvarchar(255) = null,
	@forwardalways int = null -- 0 = forward only unhandled events, 1 = always forward events (both subject to @forwardingseverity)
as
	/* Set all alert info at one go, for performance reasons.  Translate values if needed. */
	if (@pagersendsubjectonly is not null and @pagersendsubjectonly <> 0)
		select @pagersendsubjectonly = 1

	if (@failsafeoperator is not null)
		exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeOperator', N'REG_SZ', @failsafeoperator
	if (@notificationmethod is not null)
		exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertNotificationMethod', N'REG_DWORD', @notificationmethod
	if (@forwardingserver is not null)
		exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardingServer', N'REG_SZ', @forwardingserver
	if (@forwardingseverity is not null)
		exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardingSeverity', N'REG_DWORD', @forwardingseverity
	if (@pagertotemplate is not null)
		exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerToTemplate', N'REG_SZ', @pagertotemplate
	if (@pagercctemplate is not null)
		exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerCCTemplate', N'REG_SZ', @pagercctemplate
	if (@pagersubjecttemplate is not null)
		exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerSubjectTemplate', N'REG_SZ', @pagersubjecttemplate
	if (@pagersendsubjectonly is not null)
		exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerSendSubjectOnly', N'REG_DWORD', @pagersendsubjectonly
	if (@failsafeemailaddress is not null)
		exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeEmailAddress', N'REG_SZ', @failsafeemailaddress
	if (@failsafepageraddress is not null)
		exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafePagerAddress', N'REG_SZ', @failsafepageraddress
	if (@failsafenetsendaddress is not null)
		exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeNetSendAddress', N'REG_SZ', @failsafenetsendaddress
	if (@forwardalways is not null)
		exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardAlways', N'REG_DWORD', @forwardalways
go
/* End sp_MSgetalertinfo */

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

exec sp_MS_marksystemobject sp_MSgetalertinfo
go
exec sp_MS_marksystemobject sp_MSsetalertinfo
go

grant execute on sp_MSgetalertinfo to public
grant execute on sp_MSsetalertinfo to public


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





print ''
print 'Updating category for objects created by SQLDMO70.sql.'
print ''
go

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

/* if (@@microsoftversion >= SQL70_MINVERSION) begin                                      */
/* 	exec sp_MS_upd_sysobj_category 2                                                    */
/* end else begin                                                                         */
/* 	RAISERROR 55555 'You need a released version of SQL 7.0 to run this SQLDMO script'  */
/* end                                                                                    */
if (@@microsoftversion < 0x07000000) begin
	RAISERROR 55555 'You need a released version of SQL 7.0 to run this SQLDMO script'
end
go

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

if (object_id('sp_MSSQLDMO70_version') is not null) begin
	print ''
	print ''
	print ' Successful installation.'
	exec sp_MSSQLDMO70_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 **********************************/

