Microsoft Internet Business Development Kit/PRODUCT_CD.iso/sqlsvr/ppc/sqlole60.sql

Text File | 1995-12-13 | 100.8 KB | 2,864 lines

/* 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 columns - don't conflict with bit_sysgenname for DRIDefaults. */






/* sysdatabases.category bits */






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






































/* From perm.h */















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

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












use master
go

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

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





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


if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSgetalertinfo')
    drop procedure sp_MSgetalertinfo
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSsetalertinfo')
    drop procedure sp_MSsetalertinfo
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSgetexecinfo')
    drop procedure sp_MSgetexecinfo
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSsetexecinfo')
    drop procedure sp_MSsetexecinfo
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MStablekeys')
    drop procedure sp_MStablekeys
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MStablechecks')
    drop procedure sp_MStablechecks
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MStablerefs')
    drop procedure sp_MStablerefs
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSsettopology')
    drop procedure sp_MSsettopology
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_MSmatchkey')
    drop procedure sp_MSmatchkey
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSsubscriptions')
    drop procedure sp_MSsubscriptions
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSforeachdb')
    drop procedure sp_MSforeachdb
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSforeachtable')
    drop procedure sp_MSforeachtable
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSloginmappings')
    drop procedure sp_MSloginmappings
go
if exists (select * from master..sysobjects where sysstat & 0x0f = 4 and name = 'sp_MSforeach_worker')
    drop procedure sp_MSforeach_worker
go

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

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

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


    if (object_id(@tablename) is null)
    begin

        RAISERROR (15001, -1, -1, @tablename)
        return 1
    end

    set nocount on

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

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

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


    /* If they have a column dridefault, then this will show up in syscolumns.cdefault.  Filter that; */
    /* we'll put in the real dridefault below if they don't have NoDRI set. */
    update #sphelpcols set col_def = null where exists (select * from sysconstraints s, #sphelpcols c
        where s.colid = c.col_id and s.id = object_id(@tablename) and s.status & 0x0f = 5)

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


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

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

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

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

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

        /* Modifided to allow Identity if NoDRI.  This is like a new datatype; OK unless 42Syntax is specified. */
        if (@flags & 0x0800 != 0)
        begin
            update #sphelpcols                /* No IDENTITY */
                set col_identity = 0
            update #sphelpcols                /* Convert numeric and decimal with prec < 8 to real */
                set col_type = 23 where col_type in (10, 24) and col_prec < 8
            update #sphelpcols                /* Convert numeric and decimal with prec >= 8 to float */
                set col_type = 8 where col_type in (10, 24)    and col_prec >= 8
        end

    end


    /* Modifided to allow Identity if NoDRI.  This is like a new datatype; OK unless 42Syntax is specified. */
    update #sphelpcols set col_seed = ident_seed(@tablename), col_increment = ident_incr(@tablename) where col_identity != 0

    /* Get seed and increment for the IDENTITY column, if any. */
    if (@flags is null or @flags & 0x0200 = 0) begin
        update #sphelpcols set col_dridefname = object_name(s.constid) from sysconstraints s, #sphelpcols c
            where s.colid = c.col_id and s.id = object_id(@tablename) and s.status & 0x0f = 5

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

        /* We'll put out the default text if it's all in one row (most likely); otherwise leave it */
        /* blank for refetching in its entirety via sp_helptext. */
        update #sphelpcols set col_drideftext = t.text from syscomments t, sysconstraints s, #sphelpcols c
            where s.colid = c.col_id and s.id = object_id(@tablename) and s.status & 0x0f = 5
                and t.id = s.constid and 1 = (select count(*) from syscomments where id = s.constid)
    end

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



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

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

/*******************************************************************************/
print ''
print 'Creating sp_MShelpindex'
print ''
go
create procedure sp_MShelpindex
@tablename varchar(92), @indexname varchar(30) = null
as

    select i.name, i.status, i.indid, i.OrigFillFactor,



        index_col(@tablename, i.indid, 1), index_col(@tablename, i.indid, 2),
        index_col(@tablename, i.indid, 3), index_col(@tablename, i.indid, 4),
        index_col(@tablename, i.indid, 5), index_col(@tablename, i.indid, 6),
        index_col(@tablename, i.indid, 7), index_col(@tablename, i.indid, 8),
        index_col(@tablename, i.indid, 9), index_col(@tablename, i.indid, 10),
        index_col(@tablename, i.indid, 11), index_col(@tablename, i.indid, 12),
        index_col(@tablename, i.indid, 13), index_col(@tablename, i.indid, 14),
        index_col(@tablename, i.indid, 15), index_col(@tablename, i.indid, 16)
    from sysindexes i where id = object_id(@tablename) and i.indid > 0 and i.indid < 255
    and (@indexname is null or i.name = @indexname)
    order by i.name
go
/* End sp_MShelpindex */

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

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



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

            RAISERROR (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 = 'sdt')
    begin
        /* Exclude the 'xxxxn' dblib-specific nullable types, and hardcode a check for variable length and numeric usertypes. */
        select     SystemDatatypeName = t.name,
                ifvarlen_max = y.length,
                allownulls = t.allownulls,

                isnumeric = (select count(*) from systypes y where y.usertype = t.usertype and y.usertype in (24,10)),
                allowidentity = case when t.usertype in (24,7,10,6,5) then 1 else 0 end



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

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

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

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

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

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

                length = case when s.dt_basetype in (1,2,3,4) then t.length else 0 end,
                nullable = t.allownulls,
                dt_prec = case when s.dt_basetype in (10, 24) then t.prec else null end,
                dt_scale = case when s.dt_basetype in (10, 24) then t.scale else null end,
                dt_flags,
                allowidentity = case when (s.dt_basetype in (24,7,10,6,5) and scale = 0) then 1 else 0 end



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

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

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

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

    if (@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 'Rules, defaults, and datatypes do not have dependencies.'
        return (0)
    end

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

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

    create table #t3 (tid int)
    create clustered index #ci_t3 on #t3(tid) with allow_dup_row
    create table #t4 (tid int)
    create clustered index #ci_t4 on #t4(tid) with allow_dup_row

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



    /*
     * If both name and type are null, this means get every object in the
     * database matching the specification they passed in.  Otherwise,
     * find the passed object or all objects of the passed type.  Start off
     * loading parent info (pid, tid); these will be put into child as needed.
     */
    if (@objname is null and @objtype is null)
    begin
        set nocount on
        select @allobjs = 1
        insert #t1 (pid, ptype, pcat) select o.id, o.sysstat & 0x0f, o.category & 0x0002 from sysobjects o
            where ((power(2, o.sysstat & 0x0f) & 0x01ff) <> 0) and (o.sysstat & 0x0f not in (6, 7))
    end else begin
        if (@objname is not null)
        begin
            select @curid = id, @objtype = o.sysstat & 0x0f, @curcat = o.category & 0x0002 from sysobjects o where id = object_id(@objname)
            if (@curid is null)
            begin

                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

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

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

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

        if (@flags & 0x40000 != 0)
        begin
            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
    update statistics #t3 #ci_t3
    insert #t1 (tid, ttype, tcat, bDone) select distinct pid, ptype, pcat, 0 from #t1 t
        where t.pid is not null and not exists (select * from #t3 where tid = t.pid)





    /*
     * Because triggers can go in both directions, we'll need to check for
     * circular dependencies on parent evaluation.  Since any tables referenced
     * by the trigger must exist before the trigger can be created, remove rows
     * where the trigger is the parent.
     */
    if (@flags & 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 usertype from systypes where usertype > 99
        if (@flags & (power(2, 7) | power(2, 6)) != 0)
            insert #t2 (tid, ttype, tcat)
                select id, sysstat & 0x0f, 0 from sysobjects
                where sysstat in (7, 6)
                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 usertype from syscolumns
                where usertype > 99 and id in (select tid from #t1)

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

    if exists (select * from #t1 where bDone = 0) begin
        RAISERROR (14300, -1, -1)
        return 1
    end


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

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

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

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

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


    if (@id is null)
        select @id = id from sysobjects where id = object_id(@name) and sysstat & 0x0f in (1, 3)
    if (@id is null)
    begin

        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)

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

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

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

/* End sp_MStablespace */

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

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


  /* Make sure @tablename is local to the current database */
  IF (@tablename like '%.%.%') AND
     (substring(@tablename, 1, charindex('.', @tablename) - 1) <> db_name())
  BEGIN

    RAISERROR (15078, -1, -1, '')
    RETURN(1)
  END
  /* Make sure that @tablename and @index_name exist */
  SELECT @table_id = id
  FROM sysobjects
  WHERE (id = object_id(@tablename))
    AND (sysstat & 0xf = 3)
  IF (@table_id = NULL)
  BEGIN

    RAISERROR (15001, -1, -1, @tablename)
    RETURN(1)
  END
  IF (@index_name <> NULL)
  BEGIN
    SELECT @index_id = indid
    FROM sysindexes
    WHERE (name = @index_name)
      AND (id = object_id(@tablename))
    IF (@index_id = NULL)
    BEGIN
      SELECT @msg = @tablename + '.' + @index_name

      RAISERROR (15001, -1, -1, @msg)
      RETURN(1)
    END
  END
  /* Ok, we're good to go */
  IF (user_id() = 1)
    CHECKPOINT
  IF (@index_name = NULL)
  BEGIN
    CREATE TABLE #IndexSizeTemp
    (IndexID tinyint, IndexName varchar(30), IndexSize int, Comments varchar(28))
    INSERT INTO #IndexSizeTemp
    SELECT indid, name, 0, ''
    FROM sysindexes
    WHERE (id = object_id(@tablename))
      AND ((indid > 0) AND (indid < 255))
    UPDATE #IndexSizeTemp
    SET IndexSize = used * 2,
        Comments = '(None)'
    FROM sysindexes si, #IndexSizeTemp ist
    WHERE (id = object_id(@tablename))
      AND (indid > 1) AND (indid < 255)
      AND (si.indid = ist.IndexID)
    UPDATE #IndexSizeTemp
    SET IndexSize = (used - dpages - isnull((SELECT sum(used)
                                             FROM sysindexes
                                             WHERE (indid > 1) AND (indid < 255)
                                               AND (id = object_id(@tablename))), 0)) * 2,
        Comments = 'Size excludes actual data.'
    FROM sysindexes si, #IndexSizeTemp ist
    WHERE (id = object_id(@tablename))
      AND (indid = 1)
      AND (si.indid = ist.IndexID)
    SELECT 'Index ID' = IndexID, 'Index Name' = IndexName, 'Size (KB)' = IndexSize, Comments
    FROM #IndexSizeTemp
    ORDER BY IndexID
    DROP TABLE #IndexSizeTemp
  END
  ELSE
  BEGIN
    DECLARE @indid int
    SELECT @indid = indid
    FROM sysindexes
    WHERE (id = object_id(@tablename))
      AND (name = @index_name)
    /* The non-clustered index case */
    IF ((@indid > 1) AND (@indid < 255))
    BEGIN
      SELECT 'Size (KB)' = used * 2
      FROM sysindexes
      WHERE (id = object_id(@tablename))
        AND (name = @index_name)
      RETURN(0)
    END
    /* The clustered index case */
    IF (@indid = 1)
    BEGIN
      SELECT 'Size (KB)' =
             (used - dpages - isnull((SELECT sum(used)
                                      FROM sysindexes
                                      WHERE (indid > 1) AND (indid < 255)
                                        AND (id = object_id(@tablename))), 0)) * 2
      FROM sysindexes
      WHERE (id = object_id(@tablename))
        AND (name = @index_name)
    END
  END
  RETURN(0)
END
go
/* End sp_MSindexspace */

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

print ''
print 'Creating sp_MStablekeys'
print ''
go

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

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

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

    /* First see if @keyname was defined, and override @tablename and @type if so. */
    if (@keyname is not null)
    begin
        select @objid = id, @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 = user_name(uid) + '.' + name from sysobjects where id = @objid
    end else begin
        /* Want all keys for this table (of @type type). */
        select @objid = object_id(@tablename)
        if (@objid is null)    begin
            RAISERROR (15001, -1, -1, @tablename)
            return 1
        end
        if exists (select id from sysobjects where id = @objid and sysstat & 0x0f not in (1, 3))    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
    end

    /* Preprocessor won't replace within quotes so have to use str(). */
    declare @sysgenname varchar(12), @pkstr varchar(12), @uqstr varchar(12), @fkstr varchar(12), @objtypebits varchar(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 varchar(12), @typestr varchar(12)
    select @objidstr = ltrim(str(@objid))
    select @typestr = ltrim(str(@type))

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

    /*********************/
    /* Main cursor loop. */
    /*********************/
    exec('declare hC insensitive cursor for select constid, status & ' + @objtypebits + ', status & ' + @sysgenname +
            ' from sysconstraints where id = ' + @objidstr + ' and (' + @typestr + ' & 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 '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 */
                    @keycnt = case indid when 1 then keycnt else keycnt - 1 end                    /* keycnt includes RID if nc index */
                from sysindexes i, sysobjects o
                where o.id = @constid and i.name = o.name and i.status & 0x1800 <> 0
            if (@indid is null) begin
                raiserror 77777 "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,
                index_col(@tablename, @indid, 1), index_col(@tablename, @indid, 2),    index_col(@tablename, @indid, 3), index_col(@tablename, @indid, 4),
                index_col(@tablename, @indid, 5), index_col(@tablename, @indid, 6),    index_col(@tablename, @indid, 7), index_col(@tablename, @indid, 8),
                index_col(@tablename, @indid, 9), index_col(@tablename, @indid, 10),index_col(@tablename, @indid, 11), index_col(@tablename, @indid, 12),
                index_col(@tablename, @indid, 13), index_col(@tablename, @indid, 14),    index_col(@tablename, @indid, 15), index_col(@tablename, @indid, 16),
                null, null, null, null,    null, null, null, null,    null, null, null, null,    null, null, null, null, @indid)
        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 = user_name(o.uid) + '.' + o.name
                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 varchar(32)
            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 select @cType, @cName, @cFlags, @keycnt, null, @cRefTable, @cRefKey,
                col_name(r.fkeyid, r.fkey1), col_name(r.fkeyid, r.fkey2), col_name(r.fkeyid, r.fkey3), col_name(r.fkeyid, r.fkey4),
                col_name(r.fkeyid, r.fkey5), col_name(r.fkeyid, r.fkey6), col_name(r.fkeyid, r.fkey7), col_name(r.fkeyid, r.fkey8),
                col_name(r.fkeyid, r.fkey9), col_name(r.fkeyid, r.fkey10),col_name(r.fkeyid, r.fkey11), col_name(r.fkeyid, r.fkey12),
                col_name(r.fkeyid, r.fkey13), col_name(r.fkeyid, r.fkey14),col_name(r.fkeyid, r.fkey15), col_name(r.fkeyid, r.fkey16),
                col_name(r.rkeyid, r.rkey1), col_name(r.rkeyid, r.rkey2),    col_name(r.rkeyid, r.rkey3), col_name(r.rkeyid, r.rkey4),
                col_name(r.rkeyid, r.rkey5), col_name(r.rkeyid, r.rkey6), col_name(r.rkeyid, r.rkey7), col_name(r.rkeyid, r.rkey8),
                col_name(r.rkeyid, r.rkey9), col_name(r.rkeyid, r.rkey10), col_name(r.rkeyid, r.rkey11), col_name(r.rkeyid, r.rkey12),
                col_name(r.rkeyid, r.rkey13), col_name(r.rkeyid, r.rkey14), col_name(r.rkeyid, r.rkey15), col_name(r.rkeyid, r.rkey16),
                null
            from sysreferences r where r.constid = @constid
        end        /* Key type */

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

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

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

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

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

    select
        AlertFailSafeOperator = @FailSafeOperator,
        AlertNotificationMethod = @NotificationMethod,
        AlertForwardingServer = @ForwardingServer,
        AlertForwardingSeverity = @ForwardingSeverity,
        AlertPagerToTemplate = @PagerToTemplate,
        AlertPagerCCTemplate = @PagerCCTemplate,
        AlertPagerSubjectTemplate = @PagerSubjectTemplate,
        AlertPagerSendSubjectOnly = @PagerSendSubjectOnly

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

/*-- Another SQL60-only proc ---------------------------------------------------*/
print ''
print 'Creating sp_MSsetalertinfo'
print ''
go
create procedure sp_MSsetalertinfo
    @failsafeoperator varchar(255) = null,
    @notificationmethod int = null,
    @forwardingserver varchar(255) = null,
    @forwardingseverity int = null,
    @pagertotemplate varchar(255) = null,
    @pagercctemplate varchar(255) = null,
    @pagersubjecttemplate varchar(255) = null,
    @pagersendsubjectonly int = null,
    @failsafeemailaddress varchar(255) = null,
    @failsafepageraddress varchar(255) = null
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 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'AlertFailSafeOperator', 'REG_SZ', @failsafeoperator
    if (@notificationmethod is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'AlertNotificationMethod', 'REG_DWORD', @notificationmethod
    if (@forwardingserver is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'AlertForwardingServer', 'REG_SZ', @forwardingserver
    if (@forwardingseverity is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'AlertForwardingSeverity', 'REG_DWORD', @forwardingseverity
    if (@pagertotemplate is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'AlertPagerToTemplate', 'REG_SZ', @pagertotemplate
    if (@pagercctemplate is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'AlertPagerCCTemplate', 'REG_SZ', @pagercctemplate
    if (@pagersubjecttemplate is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'AlertPagerSubjectTemplate', 'REG_SZ', @pagersubjecttemplate
    if (@pagersendsubjectonly is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'AlertPagerSendSubjectOnly', 'REG_DWORD', @pagersendsubjectonly
    if (@failsafeemailaddress is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'AlertFailSafeEmailAddress', 'REG_SZ', @failsafeemailaddress
    if (@failsafepageraddress is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'AlertFailSafePagerAddress', 'REG_SZ', @failsafepageraddress
go
/* End sp_MSgetalertinfo */

/*-- Another SQL60-only proc ---------------------------------------------------*/
print ''
print 'Creating sp_MSgetexecinfo'
print ''
go
create procedure sp_MSgetexecinfo
    @includeaddresses bit = 0
as
    /* Return all SQLExecutive info at one go, for performance reasons. */
    declare @AutoStart int
    declare @RestartSQLServer int
    declare @RestartSQLServerInterval int
    declare @LimitHistoryRows int
    declare @LimitHistoryRowsMax int

    exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Services\SQLExecutive', 'Start', @param = @AutoStart OUT
    exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'RestartSQLServer', @param = @RestartSQLServer OUT
    exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'RestartSQLServerInterval', @param = @RestartSQLServerInterval OUT
    exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'SyshistoryLimitRows', @param = @LimitHistoryRows OUT
    exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'SyshistoryMaxRows', @param = @LimitHistoryRowsMax OUT

    /* Remember that for the Services, 2 == AutoStart, 3 == don't (don't ask me why). */
    select
        AutoStart = case when (2 = @AutoStart) then 1 else 0 end,
        RestartSQLServer = @RestartSQLServer,
        RestartSQLServerInterval = @RestartSQLServerInterval,
        LimitHistoryRows = @LimitHistoryRows,
        LimitHistoryRowsMax = @LimitHistoryRowsMax
go
/* End sp_MSgetexecinfo */

/*-- Another SQL60-only proc ---------------------------------------------------*/
print ''
print 'Creating sp_MSsetexecinfo'
print ''
go
create procedure sp_MSsetexecinfo
    @autostart int = null,
    @restartsqlserver int = null,
    @restartsqlserverinterval int = null,
    @limithistoryrows int = null,
    @limithistoryrowsmax int = null
as
    /* Set all SQLExecutive info at one go, for performance reasons.  Translate values if needed. */
    /* Remember that for the Services, 2 == AutoStart, 3 == don't (don't ask me why). */
    if (@autostart is not null)
        select @autostart = case when (0 = @autostart) then 3 else 2 end
    if (@restartsqlserver is not null and @restartsqlserver <> 0)
        select @restartsqlserver = 1
    if (@limithistoryrows is not null and @limithistoryrows <> 0)
        select @limithistoryrows = 1

    if (@autostart is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Services\SQLExecutive', 'Start', 'REG_DWORD', @autostart
    if (@restartsqlserver is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'RestartSQLServer', 'REG_DWORD', @restartsqlserver
    if (@restartsqlserverinterval is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'RestartSQLServerInterval', 'REG_DWORD', @restartsqlserverinterval
    if (@limithistoryrows is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'SyshistoryLimitRows', 'REG_DWORD', @limithistoryrows
    if (@limithistoryrowsmax is not null)
        exec master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive', 'SyshistoryMaxRows', 'REG_DWORD', @limithistoryrowsmax
go
/* End sp_MSgetexecinfo */

/*-- Another SQL60-only proc ---------------------------------------------------*/
print ''
print 'Creating sp_MStablechecks'
print ''
go

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

    /* We'll put out the check text if it's all in one row (most likely); otherwise leave it */
    /* blank for refetching in its entirety via sp_helptext. */
    select distinct object_name(t.id), (select y.text from syscomments y where y.id = t.id and
                                        1 = (select count(*) from syscomments where id = y.id)),
                c.status & (convert(int, 0x00200000) | convert(int, 0x00020000))
    from syscomments t, sysconstraints c
    where t.id in (select constid from sysconstraints where id = @id and status & 0x0f = 4)
        and t.id = c.constid
go
/* End sp_MStablechecks */

/*-- Another SQL60-only proc ---------------------------------------------------*/
print ''
print 'Creating sp_MStablerefs'
print ''
go

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

    declare @id int, @refid int
    select @id = object_id(@tablename), @refid = object_id(@reftable)
    if (@tablename is not null and @id is null) begin
        RAISERROR (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,                 /* id of reftable */
        constid int null,         /* id of key */
        referenced bit            /* well, is it? */
    )

    declare @dotables bit, @doall bit, @doprimary bit
    select     @dotables = case when (@type like 'allt%' or @type like 'actualt%') then 1 else 0 end,
            @doall = case when (@type like 'all%') then 1 else 0 end,
            @doprimary = case when (@direction like 'p%') then 1 else 0 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 sysstat & 0x0f = 3

            /* 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(i.name)

        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 sysstat & 0x0f = 3
                    and @doall = 1 and id not in (select id from #sprefs)

            /* Update the referenced bit if it references this table. */
            update #sprefs set referenced = 1
                where 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 sysstat & 0x0f <> 3 or category & 0x0002 <> 0)
            or (@refid is not null and id != @refid)

    /* Output */
    if (@tablename is not null)
        select candidate_table = user_name(o.uid) + '.' + object_name(o.id),
            candidate_key = case @dotables when 1 then '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)
    else
        select candidate_table = user_name(o.uid) + '.' + object_name(o.id),
            candidate_key = case @dotables when 1 then '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)
go
/* End sp_MStablerefs */

/*-- Another SQL60-only proc ---------------------------------------------------*/
print ''
print 'Creating sp_MSsettopology'
print ''
go

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

create procedure sp_MSsettopology
    @server varchar(30), @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 'allow updates', 0
go
reconfigure with override
go

/*-- Another SQL60-only proc ---------------------------------------------------*/
print ''
print 'Creating sp_MSfilterclause'
print ''
go

create procedure sp_MSfilterclause
    @publication varchar(30), @article varchar(30)
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
    select @val = textptr(filter_clause), @len = datalength(filter_clause) 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 */

/*-- Another SQL60-only proc ---------------------------------------------------*/
print ''
print 'Creating sp_MSmatchkey'
print ''
go

create proc sp_MSmatchkey
    @tablename varchar(92),
    @col1 varchar(30),
    @col2 varchar(30) = null,
    @col3 varchar(30) = null,
    @col4 varchar(30) = null,
    @col5 varchar(30) = null,
    @col6 varchar(30) = null,
    @col7 varchar(30) = null,
    @col8 varchar(30) = null,
    @col9 varchar(30) = null,
    @col10 varchar(30) = null,
    @col11 varchar(30) = null,
    @col12 varchar(30) = null,
    @col13 varchar(30) = null,
    @col14 varchar(30) = null,
    @col15 varchar(30) = null,
    @col16 varchar(30) = null
as
    declare @id int, @ii int, @colnotfound varchar(30), @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 (i int, name varchar (30) null)        /* Join into this... */
    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)
    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 */

/*-- Another SQL60-only proc ---------------------------------------------------*/
print ''
print 'Creating sp_MSsubscriptions'
print ''
go

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

    /* Similar to sp_helpsubscription but intended for SQLOLE'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
        RAISERROR (14211, -1, -1, @dbname)
        return 1
    end
    if (@mode = 'drop' and suser_id() <> 1) begin
        RAISERROR (15003, -1, -1)
        return 1
    end

    create table #t1 (i int identity primary key, cSubscriber varchar(30), cPubDBName varchar(30), cPublication varchar(60), cArticle varchar(60))

    /* Preprocessor won't replace within quotes so have to use str(). */
    declare @pubbit varchar(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 varchar(60), @artname varchar(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 */

/*-- Another SQL60-only proc ---------------------------------------------------*/
print ''
print 'Creating sp_MSforeach_worker'
print ''
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 varchar(255), @replacechar char(1) = '?', @command2 varchar(255) = null, @command3 varchar(255) = null
as
    set nocount on
    declare @name varchar(100), @namelen int, @q1 varchar(255), @q2 varchar(255), @q3 varchar(255), @q4 varchar(255), @q5 varchar(255)
    declare @q6 varchar(255), @q7 varchar(255), @q8 varchar(255), @q9 varchar(255), @q10 varchar(255)
    declare @cmd varchar(255), @replacecharindex int, @useq tinyint, @usecmd tinyint, @nextcmd varchar(255)
    create table #qtemp(qnum int, qchar varchar(255) null)    /* Temp command storage */
    open hCForEach
    fetch hCForEach into @name

    /* Loop for each database */
    while (@@fetch_status >= 0) begin
        /* Initialize. */
        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 > 255, 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
                if (datalength(@cmd) + @namelen - 1 > 255) begin
                    /* Overflow; put preceding stuff into the temp table */
                    if (@useq > 9) begin
                        raiserror 55555 '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, 255)
                        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) = ' ') begin
                        select @cmd = ' ' + substring(@cmd, @replacecharindex, 255)
                        select @replacecharindex = 2
                    end else begin
                        select @cmd = substring(@cmd, @replacecharindex, 255)
                        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) = '++') begin
                insert #qtemp values (@useq, @cmd)
                select @cmd = substring(@nextcmd, 3, 255), @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 */

/*-- Another SQL60-only proc ---------------------------------------------------*/
print ''
print 'Creating sp_MSforeachdb'
print ''
go

/*
 * The following table definition will be created by SQLOLE at start of each connection.
 * We don't create it here temporarily because we need it in Exec() or upgrade won't work.
 *
 *         create table #SQLOLEDbUserProfile (dbid int primary key, profilebits int)
 */

create proc sp_MSforeachdb
    @command1 varchar(255), @replacechar char(1) = '?', @command2 varchar(255) = null, @command3 varchar(255) = null,
    @precommand varchar(255) = null, @postcommand varchar(255) = 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 varchar(12), @invalidlogin varchar(12)
    select @inaccessible = ltrim(str(convert(int, 0x03e0)))
    select @invalidlogin = ltrim(str(convert(int, 0x40000000)))

    /* 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 @notsingle varchar(255), @dbstat_singlestr varchar(12)
    select @dbstat_singlestr = ltrim(str(convert(int, 0x1000)))

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

    declare @origdb varchar(30)
    select @origdb = db_name()

    /* Create the select */
    exec('declare hCForEach cursor for select name from master..sysdatabases d, #SQLOLEDbUserProfile p ' +
            ' where (d.status & ' + @inaccessible + ' = 0) and (d.dbid = p.dbid) and (p.profilebits & ' + @invalidlogin + ' = 0)' +
            ' and (d.status & ' + @dbstat_singlestr + ' = 0 or not exists ' +
            ' (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)

    exec ('use ' + @origdb)
    return @retval
go
/* End sp_MSforeachdb */

/*-- Another SQL60-only proc ---------------------------------------------------*/
print ''
print 'Creating sp_MSforeachtable'
print ''
go

create proc sp_MSforeachtable
    @command1 varchar(255), @replacechar char(1) = '?', @command2 varchar(255) = null, @command3 varchar(255) = null, @whereand varchar(255) = null,
    @precommand varchar(255) = null, @postcommand varchar(255) = 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 varchar(12), @objtypebits varchar(12), @usertablestat varchar(12)
    select @mscat = ltrim(str(convert(int, 0x0002)))
    select @objtypebits = ltrim(str(convert(int, 0x0f)))
    select @usertablestat = ltrim(str(convert(int, 3)))

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

    /* Create the select */
    exec('declare hCForEach cursor for select user_name(uid) + ''.'' + object_name(id) from sysobjects o '
            + ' where o.sysstat & ' + @objtypebits + ' = ' + @usertablestat + ' and o.category & ' + @mscat + ' = 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 */

/*-- Another SQL60-only proc ---------------------------------------------------*/
print ''
print 'Creating sp_MSloginmappings'
print ''
go

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

    if (@numlogins = 0) begin
        RAISERROR (14220, -1, -1, @loginname)        /* Login not found */
        return 1
    end
    if (suser_id() <> 1 and (@numlogins > 1 or suser_id() <> suser_id(@loginname))) begin
        RAISERROR (14301, -1, -1, '')                /* Only sa can see other than the current login */
        return 1
    end
    if (@loginname is not null)
        select @whereloginname = ' where name like ''' + @loginname + ''''

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

    /* Now bring them out by loginname, each in its own result set. */
    exec('declare hCForEachLogin cursor for select name from syslogins ' + @whereloginname + ' order by name')
    if (@@error = 0)
        open hCForEachLogin
    if (@@error <> 0)
        return @@error
    fetch hCForEachLogin into @name
    while (@@fetch_status >= 0) begin
        select * from #loginmappings where LoginName = @name
        fetch hCForEachLogin into @name
    end /* FETCH_SUCCESS */

    close hCForEachLogin
    deallocate hCForEachLogin
    return @@error
go
/* End sp_MSloginmappings */



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

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

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

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

sp_configure updat, 1
go
reconfigure with override
go

create proc sp_MSkilldb
    @dbname varchar(30)
as

    if (@@trancount > 0) begin

        RAISERROR (15002, -1, -1, 'sp_MSkilldb')
        return 1
    end

    if (suser_id() <> 1) begin

        RAISERROR (15003, -1, -1, '')
        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

        RAISERROR (14211, -1, -1, @dbname)
        return 1
    end
    dbcc dbrepair(@dbname, dropdb)
    return 0
go

sp_configure updat, 0
go
reconfigure with override
go

/* End sp_MSkilldb */

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

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

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







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

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


    /* Just list the current user profile */
    if (lower(@mode) = 'list') begin
        exec ('select db_name(dbid), convert(binary(4), profilebits) from #SQLOLEDbUserProfile')
        return 0
    end

    /* If 'init', just load the table. */
    if (lower(@mode) like 'ini%') begin
        /* Make sure we're either in master or only doing it to current db. */
        if (db_id() <> 1)
            select @qual = db_name()

        /* First clear out the table.  Do it in an exec() in case we're just selecting from cmdline debugging. */
        exec ('delete #SQLOLEDbUserProfile from #SQLOLEDbUserProfile p, master..sysdatabases d where d.name like ''' + @qual + ''' and p.dbid = d.dbid')

        /* See if we're to select after adding. */
        declare @origmode varchar(10)
        select @origmode = @mode
        if (@mode like '%sel%')
            select @mode = '''addsel'''
        else
            select @mode = '''add'''

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

        /* Select all matching databases -- we want an entry even for inaccessible ones. */
        declare @dbid smallint, @dbidstr varchar(12), @dbstat smallint, @dbname varchar(30)
        exec('declare hCdbs cursor for select name, dbid, status from master..sysdatabases where name like ''' + @qual + '''')
        open hCdbs

        /* Loop for each database, and if it's accessible, recursively call ourselves to add it. */
        fetch hCdbs into @dbname, @dbid, @dbstat
        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 = @dbstat & 0x1000
            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 & 0x03e0) <> 0)) begin
                /* Inaccessible, but we can set dbo if we're sa or suser_id() is db owner suid. */
                declare @dbbits int, @dbbitstr varchar(12)
                select @dbbits = 0x80000000
                if (suser_id() = 1 or suser_id() = (select suid from sysdatabases where dbid = @dbid))
                    select @dbbits = @dbbits | 0x0001
                select @dbbitstr = ltrim(convert(varchar(12), @dbbits))
                exec ('insert #SQLOLEDbUserProfile values (' + @dbidstr + ', ' + @dbbitstr + ')')
                if (lower(@mode) like '%sel%')
                    select 'profile bitmask' = @dbbits
            end else begin
                exec (@dbname + '..sp_MSdbuserprofile ' + @mode)
            end

            /* The exec() may have failed if we are not sa login and have not been added as a user to @dbname. */
            if (@@error <> 0)
                exec ('insert #SQLOLEDbUserProfile values (' + @dbidstr + ', ' + @invalidlogin + ')')
            fetch hCdbs into @dbname, @dbid, @dbstat
        end /* while FETCH_SUCCESS */
        close hCdbs
        deallocate hCdbs

        /* Return the list if desired. */
        if (lower(@origmode) = 'initlist')
            exec sp_MSdbuserprofile 'list'
        return 0
    end

    /* If 'drop', just do it. */
    if (@mode = 'drop') begin
        exec ('delete #SQLOLEDbUserProfile where dbid = db_id()')
        return 0
    end





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

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

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

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

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

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

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


    /* We have the profile bitmask, now see what to do with it. */
    if (lower(@mode) like 'add%') begin
        declare @bitstr varchar(12)
        select @bitstr = ltrim(str(convert(int, @bits)))
        exec ('insert #SQLOLEDbUserProfile values (db_id(), ' + @bitstr + ')')
    end

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

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

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

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


        RAISERROR (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
    )
    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,
        colid int null,
        uid int,
        protecttype int,
        id int
    )

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

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

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

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

/*******************************************************************************/
/* Need to create the version proc here so we can set its category bit */
go
create procedure sp_MSSQLOLE_version
as
    /* Values for this are same as @@microsoft_version:  0xrraaiibb (reserved, major, minor, build). */
    declare @i int
    select @i = 0x00063201    /* Must be in hex! */
    /* Select the numeric value, and a conversion to make it readable */
    select 'Microsoft SQLOLE Scripts' = @i, 'Version' = convert(binary(4), @i)
go

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

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

grant execute on sp_MSgetalertinfo to public
grant execute on sp_MSsetalertinfo to public
grant execute on sp_MSgetexecinfo to public
grant execute on sp_MSsetexecinfo 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_MSfilterclause to public
grant execute on sp_MSmatchkey to public
grant execute on sp_MSsubscriptions to public
grant execute on sp_MSforeachdb to public
grant execute on sp_MSforeachtable to public
grant execute on sp_MSloginmappings to public
grant execute on sp_MSforeach_worker to public



grant execute on sp_MSSQLOLE_version to public
go

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

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

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

print ''
print 'Checking objects created by sqlole60.sql.'
go

/* This kills the conn with a sev-20 raiserror on failure. */
exec sp_check_objects 'sqlole'
go

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











































































































































































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