Enter 2001 April/EnterCD4.iso/Update/SQL Server SP3/sql70sp3i.exe/INSTALL/sp1.sql

Text File | 1999-04-15 | 34.5 KB | 862 lines

-- sp1.sql
-- Upgrade system tables from SQL Server 7.0 to SQL Server 7.0 SP1
--    Copyright 1999, Microsoft Corp.    All rights reserved.
--
-- Contains "deltas" extracted from the following scripts:
--        UPGRADE.SQL
--        INSTCAT.SQL
--        MESSAGES.SQL
--        SQLDMO.SQL
--        INSTMSDB.SQL

--***********************************************************************
-- PROLOGUE
--***********************************************************************

use master
go

execute sp_configure 'allow updates',1
go

reconfigure with override
go

exec sp_MS_upd_sysobj_category 1 /*Capture datetime for use below.*/
go

--***********************************************************************
-- START MASTER
--***********************************************************************

dump tran master with no_log
go

--***********************************************************************
-- UPGRADE.SQL
--***********************************************************************

delete syscharsets where id between 73 and 75
go

-- Feb10 1999 New case sensitive Scandinavian sort orders
-- Sort order 73: LCID = 0x414, NORM_IGNOREKANATYPE | NORM_IGNOREWIDTH
insert syscharsets (type, id, csid, status, name, description, binarydefinition)
    values (2001, 73, 1, 0, N'dnk_nor_dictionary', N'Danish/Norwegian case sensitive sort order for code page 1252',
    0x800000001404000000000300)
go

-- Sort order 74: LCID = 0x41d, NORM_IGNOREKANATYPE | NORM_IGNOREWIDTH
insert syscharsets (type, id, csid, status, name, description, binarydefinition)
    values (2001, 74, 1, 0, N'fin_swe_dictionary', N'Finnish/Swedish case sensitive sort order for code page 1252',
    0x800000001d04000000000300)
go

-- Sort order 75: LCID = 0x40f, NORM_IGNOREKANATYPE | NORM_IGNOREWIDTH
insert syscharsets (type, id, csid, status, name, description, binarydefinition)
    values (2001, 75, 1, 0, N' isl_dictionary', N'Icelandic case sensitive sort order for code page 1252',
    0x800000000f04000000000300)
go

--***********************************************************************
-- INSTCAT.SQL
--***********************************************************************

set quoted_identifier on
go

update spt_server_info set attribute_value = @@version where attribute_id = 2
go

update spt_server_info set attribute_value = '7.00.624' where attribute_id = 500
go

print 'creating sp_datatype_info'
go

if object_id(N'sp_datatype_info') is not null
    drop proc sp_datatype_info
go

/*    Procedure for 7.0 server */
create proc sp_datatype_info
    (@data_type int = 0, @ODBCVer tinyint = 2)
as
    declare @mintype int
    declare @maxtype int

    if @ODBCVer <> 3
        select @ODBCVer = 2
    if @data_type = 0
    begin
        select @mintype = -32768
        select @maxtype = 32767
    end
    else
    begin
        select @mintype = @data_type
        select @maxtype = @data_type
    end

    select
        convert(sysname,case
            when t.xusertype > 255 then t.name
            else d.TYPE_NAME
        end) TYPE_NAME,
        d.DATA_TYPE,
        convert(int,case
            when d.DATA_TYPE in (6,7) then d.data_precision         /* FLOAT/REAL */
            when type_name(d.ss_dtype) IN ('numeric','decimal') and
                t.xusertype <= 255 then @@max_precision /* DECIMAL/NUMERIC */
            else OdbcPrec(t.xtype, t.length, t.xprec)
        end) "PRECISION",
        d.LITERAL_PREFIX,
        d.LITERAL_SUFFIX,
        e.CREATE_PARAMS,
        convert(smallint,case
            when d.AUTO_INCREMENT = 1 then 0 /* IDENTITY*/
            else TypeProperty (t.name, 'AllowsNull')
        end) NULLABLE,
        d.CASE_SENSITIVE,
        d.SEARCHABLE,
        d.UNSIGNED_ATTRIBUTE,
        d.MONEY,
        d.AUTO_INCREMENT,
        convert(sysname,case
            when t.xusertype > 255 then t.name
            else d.LOCAL_TYPE_NAME
        end) LOCAL_TYPE_NAME,
        convert(smallint,case
            when type_name(d.ss_dtype) IN ('numeric','decimal') and t.xusertype > 255 then TypeProperty (t.name, 'Scale')
            else d.numeric_scale
        end) MINIMUM_SCALE,
        convert(smallint,case
            when type_name(d.ss_dtype) IN ('numeric','decimal') and d.AUTO_INCREMENT = 0 and t.xusertype <= 255 then @@max_precision /* DECIMAL/NUMERIC */
            when type_name(d.ss_dtype) IN ('numeric','decimal') and d.AUTO_INCREMENT = 1 then 0 /* DECIMAL/NUMERIC IDENTITY*/
            else TypeProperty (t.name, 'Scale')
        end) MAXIMUM_SCALE,
        d.SQL_DATA_TYPE,
        d.SQL_DATETIME_SUB,
        NUM_PREC_RADIX = convert(int,d.RADIX),
        INTERVAL_PRECISION = convert(smallint,NULL),
        USERTYPE = t.usertype
    from master.dbo.spt_datatype_info d
        INNER JOIN systypes t on d.ss_dtype = t.xtype
        LEFT OUTER JOIN master.dbo.spt_datatype_info_ext e on
            t.xusertype = e.user_type
            and isnull(d.AUTO_INCREMENT,0) = e.AUTO_INCREMENT
    where
        d.DATA_TYPE between @mintype and @maxtype
        and (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
        and (t.xusertype <= 255 or
            isnull(d.AUTO_INCREMENT,0) = 0)
    order by 2, 12, 11,
    case
        when t.usertype=18 then 255
        else t.usertype
    end

go

grant execute on sp_datatype_info to public
go

dump tran master with no_log
go

set quoted_identifier off
go

--***********************************************************************
-- MESSAGES.SQL
--***********************************************************************

DELETE sysmessages WHERE error BETWEEN 21142 AND 21144
    or error = 21161
go

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (21142,16,0,'The SQL Server ''%s'' could not get Windows NT group membership information for login ''%s''. Verify that the Windows NT account that the SQL Server is running in has access to the domain of the login.' ,1033)
go

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
        (21143,16,0,'The custom stored procedure schema option is invalid for a Snapshot publication article.' ,1033)
go

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
        (21144,10,0,'Duplicate rows found in %s. Not creating unique index.' ,1033)
GO

insert into master..sysmessages (error, severity, dlevel, description, msglangid)
    values
        (21161,16,0,'Could not change the publisher because the subscription has been dropped. Use sp_subscription_cleanup to cleanup the triggers.',1033)
go

--***********************************************************************
-- SQLDMO.SQL
--***********************************************************************
exec sp_MS_upd_sysobj_category 2   -- Turn "system" bit off for SQLDMO
go

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

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

create procedure sp_MStablekeys
@tablename nvarchar(776) = null, @colname nvarchar(258) = null, @type int = null, @keyname nvarchar(517) = null, @flags int = null
as
    /* This proc returns the table's DRI keys.  @type is the type(s) of key(s) to return. */
    /* Make sure @type is only the key types (DRI_PRIMARYKEY, DRI_UNIQUE, DRI_REFERENCE). */
    if (@type is null)
        select @type = 0x000e
    else
        select @type = @type & 0x000e

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

go
/* End sp_MStablekeys */

exec sp_MS_marksystemobject sp_MStablekeys
go
grant execute on sp_MStablekeys to public
go

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

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

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



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

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

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

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

    /*
     * This proc returns a result set with one or more rows for each database for which a login is a user or aliased to one.
     * If loginname is specified, the results are limited to that login.  First load a temp table with all logins that are
     * in a db, then add those which aren't mapped to any db.
     */
    create table #loginmappings(
        LoginName            nvarchar(128)        NULL,
        DBName                nvarchar(128)        NULL,
        UserName            nvarchar(128)        NULL,
        AliasName            nvarchar(128)        NULL
    )
    if (@flags & 0x01 <> 0) begin
        INSERT #loginmappings select l.loginname, db_name(), u.name, null from master..syslogins l, sysusers u where l.sid = u.sid and l.loginname is not NULL
        /*
         * We only allow multi-db on a 6.x server because dynamic exec() didn't exist before then,
         * hence there is no way to loop thru every database.  This is caught in SQLDMO so no
         * need for error message here; we'll just return no result sets.
         */
    end else begin
        exec @retval = sp_MSforeachdb
            N'use [?] INSERT #loginmappings select l.loginname, db_name(), u.name, null from master..syslogins l, sysusers u where l.sid = u.sid and l.loginname is not NULL'
        if (@retval <> 0)
            return 1
        insert #loginmappings select l.loginname, null, null, null from master..syslogins l where l.loginname not in (select LoginName from #loginmappings) and l.loginname is not NULL
    end

    /*
     * Now bring them out by loginname, each in its own result set.
     * If this is for all logins, we'll return all logins; if for curdb,
     * only those in #loginmappings (i.e. only those mapped in curdb).
     */
    exec(N'declare hCForEachLogin cursor for select loginname from master..syslogins where loginname is not NULL ' + @whereloginname + N' order by loginname')
    if (@@error = 0)
        open hCForEachLogin
    if (@@error <> 0)
        return @@error
    fetch hCForEachLogin into @name
    while (@@fetch_status >= 0) begin
      /* Use '=' instead of 'LIKE' in comparision, so we can handle wide card character correctly */
        if ((@flags & 0x01 = 0) or exists (select * from #loginmappings where LoginName = @name))
            select * from #loginmappings where LoginName = @name
        fetch hCForEachLogin into @name
    end /* FETCH_SUCCESS */
    close hCForEachLogin
    deallocate hCForEachLogin
    return @@error
go
/* End sp_MSloginmappings */

exec sp_MS_marksystemobject sp_MSloginmappings
go

grant execute on sp_MSloginmappings to public
go

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

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

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

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

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

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

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

   select * from #tempFG
   DROP TABLE #tempFG

go
/* End sp_MSscriptdb_worker */

exec sp_MS_marksystemobject sp_MSscriptdb_worker
go
grant execute on sp_MSscriptdb_worker to public
go

exec sp_MS_upd_sysobj_category 1   -- Turn "system" bit back on
go

--***********************************************************************
-- XPSTAR.SQL (drop the SPs that were added in SP1 Beta)
--***********************************************************************
if exists (select * from master.dbo.sysobjects where name = 'sp_enum_oledb_providers')
  drop procedure sp_enum_oledb_providers
go
if exists (select * from master.dbo.sysobjects where name = 'sp_prop_oledb_provider')
  drop procedure sp_prop_oledb_provider
go

--***********************************************************************
-- END MASTER
--***********************************************************************
dump tran master with no_log
go
checkpoint
go

--***********************************************************************
-- START MSDB
--***********************************************************************
--***********************************************************************
-- INSTMSDB.SQL
--***********************************************************************

-- Explicitly set the options that the server stores with the object in sysobjects.status
-- so that it doesn't matter if the script is run using a DBLib or ODBC based client.
SET QUOTED_IDENTIFIER OFF -- We don't use quoted identifiers
SET ANSI_NULLS ON         -- We don't want (NULL = NULL) == TRUE
go
SET ANSI_PADDING ON       -- Set so that trailing zeros aren't trimmed off sysjobs.owner_login_sid
go

EXECUTE sp_dboption msdb, N'trunc. log on chkpt.', TRUE
go

USE msdb
go

-- Check that we're in msdb
IF (DB_NAME() <> N'msdb')
  RAISERROR('A problem was encountered accessing msdb. SP1.SQL terminating.', 20, 127) WITH LOG
go

DUMP TRANSACTION msdb WITH NO_LOG
go
CHECKPOINT
go

PRINT ''
PRINT 'Creating procedure sp_enum_sqlagent_subsystems...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_enum_sqlagent_subsystems')
              AND (type = 'P')))
  DROP PROCEDURE sp_enum_sqlagent_subsystems
go
CREATE PROCEDURE sp_enum_sqlagent_subsystems
AS
BEGIN
  DECLARE @part                  NVARCHAR(300)
  DECLARE @fmt                   NVARCHAR(300)
  DECLARE @subsystem             NVARCHAR(40)
  DECLARE @replication_installed INT

  SET NOCOUNT ON

  CREATE TABLE #xp_results (subsystem   NVARCHAR(40)  NOT NULL,
                            description NVARCHAR(300) NOT NULL)
  CREATE TABLE #sp_enum_ss_temp (subsystem          NVARCHAR(40) NOT NULL,
                                 description        NVARCHAR(80) NOT NULL,
                                 subsystem_dll      NVARCHAR(80) NULL,
                                 agent_exe          NVARCHAR(80) NULL,
                                 start_entry_point  NVARCHAR(30) NULL,
                                 event_entry_point  NVARCHAR(30) NULL,
                                 stop_entry_point   NVARCHAR(30) NULL,
                                 max_worker_threads INT          NULL)

  -- Check if replication is installed
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\Replication',
                                N'IsInstalled',
                                 @replication_installed OUTPUT,
                                N'no_output'
  SELECT @replication_installed = ISNULL(@replication_installed, 0)

  INSERT INTO #xp_results
  EXECUTE master.dbo.xp_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\SubSystems'

  IF (@replication_installed = 0)
  BEGIN
    DELETE FROM #xp_results
    WHERE (subsystem IN (N'Distribution', N'LogReader', N'Merge', N'Snapshot'))
  END

  DECLARE all_subsystems CURSOR LOCAL
  FOR
  SELECT subsystem, description
  FROM #xp_results

  OPEN all_subsystems
  FETCH NEXT FROM all_subsystems INTO @subsystem, @part
  WHILE (@@fetch_status = 0)
  BEGIN
    IF (@subsystem = N'TSQL')
      INSERT INTO #sp_enum_ss_temp VALUES (N'TSQL', FORMATMESSAGE(14556), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), CONVERT(INT, @part))
    ELSE
    BEGIN
      SELECT @fmt = N''
      WHILE (CHARINDEX(N',', @part) > 0)
      BEGIN
        SELECT @fmt = @fmt + 'N''' + SUBSTRING(@part, 1, CHARINDEX(N',', @part) - 1) + ''', '
        SELECT @part = RIGHT(@part, (DATALENGTH(@part) / 2) - CHARINDEX(N',', @part))
      END
      SELECT @fmt = @fmt + @part
      IF (DATALENGTH(@fmt) > 0)
        INSERT INTO #sp_enum_ss_temp
        EXECUTE(N'SELECT ''' + @subsystem + N''', N'''', ' + @fmt)
    END
    FETCH NEXT FROM all_subsystems INTO @subsystem, @part
  END
  DEALLOCATE all_subsystems

  UPDATE #sp_enum_ss_temp SET description = FORMATMESSAGE(14550)
  WHERE (subsystem = N'CmdExec')
  UPDATE #sp_enum_ss_temp SET description = FORMATMESSAGE(14551)
  WHERE (subsystem = N'Snapshot')
  UPDATE #sp_enum_ss_temp SET description = FORMATMESSAGE(14552)
  WHERE (subsystem = N'LogReader')
  UPDATE #sp_enum_ss_temp SET description = FORMATMESSAGE(14553)
  WHERE (subsystem = N'Distribution')
  UPDATE #sp_enum_ss_temp SET description = FORMATMESSAGE(14554)
  WHERE (subsystem = N'Merge')
  UPDATE #sp_enum_ss_temp SET description = FORMATMESSAGE(14555)
  WHERE (subsystem = N'ActiveScripting')

  -- 'TSQL' is always available (since it's a built-in subsystem), so we explicity add it
  -- to the result set
  IF (NOT EXISTS (SELECT *
                  FROM #sp_enum_ss_temp
                  WHERE (subsystem = N'TSQL')))
    INSERT INTO #sp_enum_ss_temp VALUES (N'TSQL', FORMATMESSAGE(14556), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), CASE (PLATFORM() & 0x2) WHEN 0x2 THEN 10 ELSE 20 END) -- Worker thread rule should match DEF_REG_MAX_TSQL_WORKER_THREADS

  SELECT subsystem,
         description,
         subsystem_dll,
         agent_exe,
         start_entry_point,
         event_entry_point,
         stop_entry_point,
         max_worker_threads
  FROM #sp_enum_ss_temp
  ORDER BY subsystem
END
go

--***********************************************************************
-- END MSDB
--***********************************************************************

DUMP TRANSACTION msdb WITH NO_LOG
go
CHECKPOINT
go

--***********************************************************************
-- EPILOGUE
--***********************************************************************

exec sp_configure 'allow updates',0
go

reconfigure with override
go

exec sp_MS_upd_sysobj_category 2 /* set category | 2 based on crdate. */
go

print ''
print 'sp1.sql completed successfully.'
go