Microsoft Internet Business Development Kit/PRODUCT_CD.iso/sqlsvr/alpha/sqlole42.sql

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

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

































































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

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

    set nocount on

    create table #sphelpcols
    (
        col_name        varchar (30),
        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, NULL, NULL, cdefault, domain,
            convert(bit, status & 0x0008), convert(bit, status & 0x0080), NULL, NULL, type, 0, null, null, null, null
    from syscolumns
    where id = object_id(@tablename)

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












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

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

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

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

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












    end


































    /* For scripting, set the col_flags */
    update #sphelpcols set col_flags = col_flags | 0x0001 where col_type in (1,2,3,4)
    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, 0,

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

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

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

    declare @42errmsg varchar(100)

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

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




                isnumeric = 0, allowidentity = 0

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

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

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

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

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

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








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

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

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

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

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





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

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

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






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

    declare @42errmsg varchar(100)

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

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

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

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

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

        if (@flags & 0x40000 != 0)
        begin

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

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








        end else begin

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

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








        end

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

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

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







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


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








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


    /*
     * If we're to get all objects, get all UDDTs (which aren't in sysobjects)
     * and Rules/Defaults, assuming we're returning those types.
     */
    if (@allobjs <> 0)
    begin
        if (@flags & power(2, 0) != 0)
            insert #tempudt
                select usertype from systypes where usertype > 99
        if (@flags & (power(2, 7) | power(2, 6)) != 0)
            insert #t2 (tid, ttype, tcat)
                select id, sysstat & 0x0f, 0 from sysobjects
                where sysstat in (7, 6)

    end else begin
        /*
         * Not getting all objects.  Get any datatypes that
         * are referenced by objects in #t1.  We don't care about specific
         * datatype dependencies, we just want to know which ones are needed.
         */
        if (@flags & power(2, 0) != 0)
            insert #tempudt select distinct usertype from syscolumns
                where usertype > 99 and id in (select tid from #t1)

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

            insert #t2 (tid, ttype, tcat)
                select distinct s.cdefault, 6, 0 from syscolumns s, #t1 t
                    where s.cdefault != 0 and s.id = t.tid
                        and s.cdefault not in (select tid from #t1)

        end
    end        /* Not getting all objects */

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

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

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


















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


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

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







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

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

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

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

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

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

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

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

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

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

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

/* End sp_MStablespace */

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

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

  /* Make sure @tablename is local to the current database */
  IF (@tablename like '%.%.%') AND
     (substring(@tablename, 1, charindex('.', @tablename) - 1) <> db_name())
  BEGIN
    select @42errmsg = 'Table or view name must be in the ''current'' database.'
    RAISERROR 4215078 @42errmsg
    RETURN(1)
  END
  /* Make sure that @tablename and @index_name exist */
  SELECT @table_id = id
  FROM sysobjects
  WHERE (id = object_id(@tablename))
    AND (sysstat & 0xf = 3)
  IF (@table_id = NULL)
  BEGIN
    select @42errmsg = @tablename + ' does not exist.'
    RAISERROR 4215001 @42errmsg
    RETURN(1)
  END
  IF (@index_name <> NULL)
  BEGIN
    SELECT @index_id = indid
    FROM sysindexes
    WHERE (name = @index_name)
      AND (id = object_id(@tablename))
    IF (@index_id = NULL)
    BEGIN
      SELECT @msg = @tablename + '.' + @index_name
      select @42errmsg = @msg + ' does not exist.'
      RAISERROR 4215001 @42errmsg
      RETURN(1)
    END
  END
  /* Ok, we're good to go */
  IF (user_id() = 1)
    CHECKPOINT
  IF (@index_name = NULL)
  BEGIN
    CREATE TABLE #IndexSizeTemp
    (IndexID tinyint, 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_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
    declare @42errmsg varchar(100)
    if (@@trancount > 0) begin
        select @42errmsg = 'The procedure ''sp_MSkilldb'' cannot be executed from within a transaction.'
        RAISERROR 4215002 @42errmsg
        return 1
    end

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

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

sp_configure updat, 0
go
reconfigure with override
go

/* End sp_MSkilldb */

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

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

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







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

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


















































































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


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

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

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

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

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

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

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









    if (lower(@mode) like '%sel%') begin
        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
        declare @42errmsg varchar(100)
        select @42errmsg = @objname + ' does not exist.'
        RAISERROR 4215001 @42errmsg
        return 1
    end

    /* Get a temp list of objects we're interested in.  Do not include repl_* users. */
    create table #objs(
        id int
    )
    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 xp_msver to public

grant execute on sp_MSSQLOLE_version to public
go

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

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





















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

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

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

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

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

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

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

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

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

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

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

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

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



if (object_id('sp_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 **********************************/