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

Text (UTF-16) | 2000-03-28 | 244.2 KB | 3,744 lines

/*------------------------------------------------------------------------------

SP2_SERV.SQL

THIS SCRIPT TAKES THE SERVER-SIDE SYSTEM-PROCS FROM 7.0 SP1 to SP2.

Changes in this file are organized as follows (please maintain):
    System Tables (UPGRADE.SQL)
    System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL)
    Engine System Procs (U_TABLES.SQL / PROCSYST.SQL / ODSOLE.SQL)
    Schema Procs (OLEDBSCH.SQL / ANSIVIEW.SQL)
    ODBC/OLEDB Catalog Procs (INSTCAT.SQL)
    SEM SQLDMO System Procs (SQLDMO.SQL)

Changes to these scripts should NOT be placed in this file:
    Starfighter Procs (XPSTAR.SQL / INSTMSDB.SQL / SQLTRACE.SQL / WEB.SQL)
    Doc's Samples (INSTPUBS.SQL / INSTNWND.SQL)
    Replication Procs (REPLSYS.SQL / REPLCOM.SQL / REPLTRAN.SQL / REPLMERG.SQL)
These components will maintain separate upgrade scripts.

Notes:
+ Catalog-updates and sp_MS_upd_sysobj_category are enabled for the entire
    file.  Do not disable or re-enable them.  Please do not change set options.

------------------------------------------------------------------------------*/


--------------------------------------------------------------------------------
-- VERIFY Server is started in single-user-mode (catalog-updates enables), and
--    start marking of system-objects.
--------------------------------------------------------------------------------
execute sp_configure 'allow updates',1
go

reconfigure with override
go

exec sp_MS_upd_sysobj_category 1
go

--------------------------------------------------------------------------------
--    System Tables (UPGRADE.SQL)
--------------------------------------------------------------------------------

-- Bug 55162
-- Sort order 98: LCID = 0x415, NORM_IGNOREKANATYPE | NORM_IGNOREWIDTH | NORM_IGNORECASE
delete from syscharsets where id=98
go
insert syscharsets (type, id, csid, status, name, description, binarydefinition)
    values (2001, 98, 4, 0, N'polish_ci', N'Windows Polish case insensitive sort order for code page 1250',
    0x800000001504000001000300)
go


--------------------------------------------------------------------------------
--    System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL) English
--------------------------------------------------------------------------------
DELETE sysmessages
    where error in
    (948, 1931, 2731, 3106, 3132, 3168, 3169, 16927, 21287,18773)
go

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (948,14,0,'Database ''%.*ls'' cannot be upgraded. Database is version %d. This server supports version  %d.' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (1931,16,0,'Create %S_MSG is not allowed when the database has ''pending upgrade'' enabled.' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (2731, 16, 1, 'The width of column ''%.*ls'' is %d. This width is not valid.' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (3132,16,0,'The media set for database ''%ls'' has %d family members, but only %d are provided. All members must be provided.' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (3168,16,0,'The backup of the system database on device %ls cannot be restored because it was created by a version of the server (%u) different from this version (%u).' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (3169,16,0,'The backed-up database has on-disk structure version %d. The server supports version %d and cannot restore or upgrade this database.' ,1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (16927, 16, 0,'The fetch operation cannot be used with text, ntext, or image data types.', 1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (21287, 16, 0,'Cannot clean up the meta data for publication ''%s'' because other publications are using one or more articles in this publication.', 1033)

insert into master..sysmessages (error ,severity ,dlevel ,description ,msglangid)
    values
    (18773, 16, 0,'Could not locate text information records for column %d during command construction.', 1033)

go


--------------------------------------------------------------------------------
--    System Messages (MESSAGES.SQL / MSGWORK.SQL / SERVMSGS.SQL) Localized
--------------------------------------------------------------------------------



--------------------------------------------------------------------------------
--    Engine System Procs (U_TABLES.SQL / PROCSYST.SQL / ODSOLE.SQL)
--------------------------------------------------------------------------------
-- Allow for log shipping to next release server.
delete spt_values where type ='D' and name='pending upgrade'
go
insert spt_values (name, number, type)
    values ('pending upgrade', 16384, 'D')
go
update spt_values set number = 4218397
where type = 'D' and name = 'ALL SETTABLE OPTIONS'
go

-- BUG 56868
if (exists (select * from sysobjects
        where name = 'sp_change_users_login' and type = 'P '))
    drop proc sp_change_users_login
go
CREATE PROCEDURE sp_change_users_login
    @Action               varchar(10)       -- REPORT / UPDATE_ONE / AUTO_FIX
   ,@UserNamePattern      sysname  = Null
   ,@LoginName            sysname  = Null
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @exec_stmt nvarchar(430)

    declare @ret            int,
            @FixMode        char(5),
            @cfixesupdate   int,        -- count of fixes by update
            @cfixesaddlogin int,        -- count of fixes by sp_addlogin
            @dbname         sysname,
            @loginsid       varbinary(85),
            @110name        sysname

    -- SET INITIAL VALUES --
    select  @dbname         = db_name(),
            @cfixesupdate   = 0,
            @cfixesaddlogin = 0

    -- ERROR IF IN USER TRANSACTION --
    if @@trancount > 0
    begin
        raiserror(15289,-1,-1)
        return (1)
    end

    -- INVALIDATE USE OF SPECIAL LOGIN/USER NAMES --
    if @LoginName = 'sa'
    begin
        raiserror(15287,-1,-1,@LoginName)
        return (1)
    end
    if lower(@UserNamePattern) in ('dbo','guest','INFORMATION_SCHEMA')
    begin
        raiserror(15287,-1,-1,@UserNamePattern)
        return (1)
    end

    -- HANDLE REPORT --
    if upper(@Action) = 'REPORT'
    begin

        -- VALIDATE PARAMS --
        if @UserNamePattern IS NOT Null or @LoginName IS NOT Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- GENERATE REPORT --
        select UserName = name, UserSID = sid from sysusers
            where issqluser = 1 and (sid is not null and sid <> 0x0)
                    and suser_sname(sid) is null
            order by name
        return (0)
    end

    -- HANDLE UPDATE_ONE --
    if upper(@Action) = 'UPDATE_ONE'
    begin

        -- CHECK PERMISSIONS --
        if not is_member('db_owner') = 1
        begin
            raiserror(15288,-1,-1,'SA or DBO',@Action)
            return (1)
        end

        -- ERROR IF PARAMS NULL --
        if @UserNamePattern IS Null or @LoginName IS Null
        begin
            raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
            return (1)
        end

        -- VALIDATE PARAMS --
        -- Can ONLY remap SQL Users to SQL Logins!  Should be no need
        --  for re-mapping NT logins, and if you try, you'll mess up
        --  the user status bits! (see samsmith)
        if not exists (select name from sysusers where
                name = @UserNamePattern             -- match user name
            and issqluser = 1)                      -- must be sql user
        begin
            raiserror(15291,-1,-1,'User',@UserNamePattern)
            return (1)
        end
        select @loginsid = sid from master.dbo.syslogins where
                loginname = @LoginName              -- match login name
            and isntname = 0                        -- cannot use nt logins
        if @loginsid is null
        begin
            raiserror(15291,-1,-1,'Login',@LoginName)
            return (1)
        end

        -- ERROR IF SID ALREADY IN USE IN DATABASE --
        if exists (select sid from sysusers where sid = @loginsid
                    and name <> @UserNamePattern)
        begin
            raiserror(15063,-1,-1)
            return (1)
        end

        -- CHANGE THE USERS LOGIN (SID) --
        update sysusers set sid = @loginsid, updatedate = getdate()
                where name = @UserNamePattern and issqluser = 1
                and sid <> @loginsid

        -- FINALIZATION: REPORT AND EXIT --
        if @@error <> 0 or @@rowcount <> 1
            raiserror(15295,-1,-1, 0)
        else
            raiserror(15295,-1,-1, 1)
        return (0)
    end

    -- ERROR IF NOT AUTO_FIX --
    if upper(@Action) <> 'AUTO_FIX'
    begin
        raiserror(15286,-1,-1,@Action)
        return (1)
    end

    -- HANDLE AUTO_FIX --
    -- CHECK PERMISSIONS --
    if not is_srvrolemember('sysadmin') = 1
    begin
        raiserror(15288,-1,-1,'SA or DBO',@Action)
        return (1)
    end

    -- VALIDATE PARAMS --
    if @UserNamePattern IS Null or @LoginName IS NOT Null
    begin
        raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName)
        return (1)
    end

    -- LOOP THRU ORPHANED USERS --
    -- declare the cursor explicity global so that the cursor
    -- exists at the end of the exec stmt regardless of the database
    -- option value for 'default to local cursor'
    select @exec_stmt = 'DECLARE Cursor110_Users cursor global for
            select name from sysusers
            where name = N' + quotename( @UserNamePattern , '''')+ '
                and issqluser = 1 and suser_sname(sid) is null'
    EXECUTE (@exec_stmt)
    OPEN Cursor110_Users

    WHILE (110=110)
    begin
        FETCH next from Cursor110_Users into @110name
        if (@@fetch_status <> 0)
        begin
            DEALLOCATE Cursor110_Users
            BREAK
        end

        -- IS NAME ALREADY IN USE? --
        -- if suser_sid(@110name) is null
        if not exists(select * from master.dbo.syslogins where loginname = @110name)
        begin

            -- ADD LOGIN --
            execute @ret = sp_addlogin @110name, Null, @dbname
            if @ret <> 0 or suser_sid(@110name) is null
            begin
                raiserror(15497,16,1,@110name)
                deallocate Cursor110_Users
                return (1)
            end
            select @FixMode = '1AddL'
            raiserror(15293,-1,-1,@110name)
        end
        ELSE
        begin
            Select @FixMode = '2UpdU'
            Raiserror(15292,-1,-1,@110name)
        end

        -- REPORT ERROR & CONTINUE IF DUPLICATE SID IN DB --
        select @loginsid = suser_sid(@110name)
        if user_sid(@loginsid) is not null
        begin
            raiserror(15331,-1,-1,@110name,@loginsid)
            CONTINUE
        end

        -- UPDATE SYSUSERS ROW --
        update sysusers set sid = @loginsid, updatedate = getdate() where name = @110name
        if @@error <> 0
        begin
            raiserror(15498,17,127)
            deallocate Cursor110_Users
            return (1)
        end


        if @FixMode = '1AddL'
            Select @cfixesaddlogin = @cfixesaddlogin + 1
        else
            Select @cfixesupdate = @cfixesupdate + 1
    end -- loop 110

    -- REPORT AND RETURN SUCCESS --
    raiserror(15295,-1,-1,@cfixesupdate)
    raiserror(15294,-1,-1,@cfixesaddlogin)
    return (0) -- sp_change_users_login
go

grant execute on sp_change_users_login to public
go


--BUGS 54274, 55554, 55297, and 55152
if object_id('sp_bindefault','P') IS NOT NULL
    drop procedure sp_bindefault
if object_id('sp_bindrule','P') IS NOT NULL
    drop procedure sp_bindrule
if object_id('sp_unbindefault','P') IS NOT NULL
    drop procedure sp_unbindefault
if object_id('sp_unbindrule','P') IS NOT NULL
    drop procedure sp_unbindrule
go

raiserror(15339,-1,-1,'sp_bindefault')
go
create procedure sp_bindefault --- 1996/08/30 20:04
@defname nvarchar(776),            /* name of the default */
@objname nvarchar(517),            /* table or usertype name */
@futureonly varchar(15) = NULL        /* flag to indicate extent of binding */
as

declare @defid int            /* id of the default to bind */
declare @futurevalue varchar(15)    /* the value of @futureonly that causes
                    ** the binding to be limited */
declare
    @vc1            nvarchar(517)
    ,@tab_id        integer
    ,@parent_obj    integer
    ,@cur_tab_id    integer
    ,@colid            smallint
    ,@xtype            tinyint
    ,@xusertype        smallint
    ,@col_status    tinyint
    ,@col_default int
    ,@identity binary(1)

declare
    @UnqualDef            sysname
    ,@QualDef1            sysname
    ,@QualDef2            sysname
    ,@QualDef3            sysname

    ,@UnqualObj            sysname
    ,@QualObj1            sysname
    ,@QualObj2            sysname
    ,@QualObj3            sysname

set cursor_close_on_commit    off
set nocount            on

select @futurevalue = 'futureonly'    /* initialize @futurevalue */
select @identity = 0X80 /* identity columns*/

/*
**  When a default or rule is bound to a user-defined datatype, it is also
**  bound, by default, to any columns of the user datatype that are currently
**  using the existing default or rule as their default or rule.  This default
**  action may be overridden by setting @futureonly = @futurevalue when the
**  procedure is invoked.  In this case existing columns with the user
**  datatype won't have their existing default or rule changed.
*/

-- get name parts --
select @UnqualDef = parsename(@defname, 1),
        @QualDef1 = parsename(@defname, 2),
        @QualDef2 = parsename(@defname, 3),
        @QualDef3 = parsename(@defname, 4)

select @UnqualObj = parsename(@objname, 1),
        @QualObj1 = parsename(@objname, 2),
        @QualObj2 = parsename(@objname, 3),
        @QualObj3 = parsename(@objname, 4)

IF (@UnqualDef is NULL OR @QualDef3 is not null)
   begin
   raiserror(15253,-1,-1,@defname)
   return (1)
   end

IF (@UnqualObj is NULL OR @QualObj3 is not null)
   begin
   raiserror(15253,-1,-1,@objname)
   return (1)
   end


------------------  Verify database.

if ((@QualObj2 is not null and @QualObj1 is null)
    or (@QualDef2 is not null and @QualDef2 <> db_name()))
    begin
        raiserror(15076,-1,-1)
        return (1)
    end

/*
**  Check that the @futureonly argument, if supplied, is correct.
*/
if (@futureonly IS NOT NULL)
    if (@futureonly <> @futurevalue)
        begin
            raiserror(15100,-1,-1)
            return (1)
        end

/*
**  Check to see that the default exists and get its id.
*/
select @defid = id, @parent_obj = parent_obj from sysobjects
            where id = object_id(@defname)
                and xtype='D '    -- default object 6

if @defid is NULL
    begin
        raiserror(15016,-1,-1,@UnqualDef)
        return (1)
    end


if @parent_obj > 0
    begin
        raiserror(15050,-1,-1,@defname)
        return(1)
    end

/*
**  If @objname is of the form tab.col then we are binding to a column.
**  Otherwise its a datatype.  In the column case, we need to extract
**  and verify the table and column names and make sure the user owns
**  the table that is getting the default bound. We also need to ensure
**  that we don't overwrite any DRI style defaults.
*/
if @QualObj1 is not null
begin
    if (@QualObj2 is not null)
        select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1)
    else
        select @vc1 = QuoteName(@QualObj1)

    select    @tab_id = o.id,        @colid = c.colid,
            @xtype = c.xtype,    @col_status = c.status,
            @col_default = c.cdefault
    from sysobjects o, syscolumns c
    where c.id = object_id(@vc1,'local')
            and c.name = @UnqualObj
            and o.id = c.id
            and o.xtype='U '

    /*Check that table and column exist*/
     if @tab_id is null
    begin
        raiserror(15104,-1,-1,@QualObj1,@UnqualObj)
        return (1)
    end

    /*
    **  If the column type is timestamp, disallow the bind.
    **  Defaults can't be bound to timestamp columns.
    */
    if type_name(@xtype) = 'timestamp'
    begin
        raiserror(15101,-1,-1)
        return (1)
    end

    /*
    **  If the column category is identity, disallow the bind.
    **  Defaults can't be bound to identity columns.
    */
    if @col_status & @identity = @identity
    begin
        raiserror(15102,-1,-1)
        return (1)
    end

   /*
   **  Check to see if the column was created with or altered
   **  to have a DRI style default value.
   */
    if @col_default > 0
        if exists
         (select    *
            from    sysobjects o
            where    @col_default       = o.id
            and        @tab_id             = o.parent_obj)
        begin
            raiserror(15103,-1,-1)
            return (1)
        end

    BEGIN TRANSACTION txn_bindefault_1

        /*
        **  Since binding a default is a schema change, update schema count
        **  for the object in the sysobjects table.
        */

        dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

        update syscolumns set cdefault = @defid where id = @tab_id and colid = @colid

    COMMIT TRANSACTION txn_bindefault_1

    raiserror(15511,-1,-1)

end
else
begin
    /*
    **  We're binding to a user type.  In this case, the @objname
    **  is really the name of the user datatype.
    **  When we bind to a user type, any existing columns get changed
    **  to the new binding unless their current binding is not equal
    **  to the current binding for the usertype or if they set the
    **  @futureonly parameter to @futurevalue.
    */
    declare @olddefault int    /* current default for type */

    /*
    **  Get the current default for the datatype.
    */

    select @xusertype = xusertype, @olddefault = tdefault
        from systypes where name = @UnqualObj and xusertype > 256
        AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(uid))=1)

    if @xusertype is null
        begin
            raiserror(15105,-1,-1)
            return (1)
        end

    update systypes
        set tdefault = @defid
            from systypes
        where xusertype = @xusertype


    raiserror(15512,-1,-1)

    /*
    **  need the new binding.
    */
    if isnull(@futureonly, ' ') <> @futurevalue
    begin

        declare csr3_t1 insensitive cursor for
          select
            distinct
                 c.id
                 ,c.colid
            from     syscolumns    c JOIN sysobjects o ON c.id = o.id AND o.xtype = 'U '
            where     c.xusertype    = @xusertype
            and    (c.cdefault    = @olddefault    OR
                 c.cdefault    = 0
                )
            order by c.id
                  for read only

        open csr3_t1

        BEGIN TRANSACTION txn_bindefault_3

        fetch next from csr3_t1 into
             @tab_id,
             @colid

        WHILE @@fetch_status = 0
        begin

            select @vc1 = quotename(user_name(OBJECTPROPERTY(@tab_id,'OwnerId'))) + '.'
                        + quotename(object_name(@tab_id))

            dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

            select @cur_tab_id = @tab_id

            while @cur_tab_id = @tab_id and @@fetch_status = 0
            begin

                update syscolumns
                set cdefault = @defid
                from syscolumns c
                where c.id = @tab_id
                and c.colid = @colid

                fetch next from csr3_t1 into
                     @tab_id,
                     @colid
            end

        end --loop 3

        COMMIT TRANSACTION txn_bindefault_3

        deallocate csr3_t1

        raiserror(15513,-1,-1)
    end
end

return (0) -- sp_bindefault
go

raiserror(15339,-1,-1,'sp_bindrule')
go
create procedure sp_bindrule --- 1996/08/14 15:02
@rulename nvarchar(776),            /* name of the rule */
@objname nvarchar(517),            /* table or usertype name */
@futureonly varchar(15) = NULL        /* column name */
as

declare @ruleid int            /* id of the rule to bind */
declare @futurevalue varchar(15)    /* the value of @futureonly that causes
                    ** the binding to be limited */

declare
    @vc1            nvarchar(517)
    ,@tab_id        integer
    ,@cur_tab_id    integer
    ,@colid            smallint

    ,@xtype            smallint
    ,@xusertype        smallint

declare
    @UnqualRule        sysname
    ,@QualRule1        sysname
    ,@QualRule2        sysname
    ,@QualRule3        sysname

    ,@UnqualObj        sysname
    ,@QualObj1        sysname
    ,@QualObj2        sysname
    ,@QualObj3        sysname

set cursor_close_on_commit    off
set nocount on

select @futurevalue = 'futureonly'    /* initialize @futurevalue */

/*
**  When a default or rule is bound to a user-defined datatype, it is also
**  bound, by default, to any columns of the user datatype that are currently
**  using the existing default or rule as their default or rule.  This default
**  action may be overridden by setting @futureonly = @futurevalue when the
**  procedure is invoked.  In this case existing columns with the user
**  datatype won't have their existing default or rule changed.
*/

-- get name parts --
select @UnqualRule = parsename(@rulename, 1),
        @QualRule1 = parsename(@rulename, 2),
        @QualRule2 = parsename(@rulename, 3),
        @QualRule3 = parsename(@rulename, 4)

select @UnqualObj = parsename(@objname, 1),
        @QualObj1 = parsename(@objname, 2),
        @QualObj2 = parsename(@objname, 3),
        @QualObj3 = parsename(@objname, 4)

IF (@UnqualRule is NULL OR @QualRule3 is not null)
   begin
   raiserror(15253,-1,-1,@rulename)
   return (1)
   end

IF (@UnqualObj is NULL OR @QualObj3 is not null)
   begin
   raiserror(15253,-1,-1,@objname)
   return (1)
   end


------------------  Verify database.

if ((@QualObj2 is not null and @QualObj1 is null)
    or (@QualRule2 is not null and @QualRule2 <> db_name()))
    begin
        raiserror(15077,-1,-1)
        return (1)
    end

/*
**  Check that the @futureonly argument, if supplied, is correct.
*/
if (@futureonly IS NOT NULL)
    begin
        if (@futureonly <> @futurevalue)
            begin
                raiserror(15106,-1,-1)
                return (1)
            end
    end

/*
**  Check to see that the rule exists and get its id.
*/
select @ruleid = id from sysobjects
            where id = object_id(@rulename)
                and xtype='R ' --rule object 7

if @ruleid is NULL
    begin
        raiserror(15017,-1,-1,@rulename)
        return (1)
    end

/*
**  If @objname is of the form tab.col then we are binding to a column.
**  Otherwise its a datatype.  In the column case, we need to extract
**  and verify the table and column names and make sure the user owns
**  the table that is getting the rule bound.
*/
if @QualObj1 is not null
begin
    if (@QualObj2 is not null)
        select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1)
    else
        select @vc1 = QuoteName(@QualObj1)

    select @tab_id = o.id, @colid = c.colid, @xtype = c.xtype
    from sysobjects o, syscolumns c
    where c.id = object_id(@vc1,'local')
            and c.name = @UnqualObj
            and o.id = c.id
            and o.xtype='U '

    /*Check that table and column exist*/
     if @tab_id is null
    begin
        raiserror(15104,-1,-1,@QualObj1,@UnqualObj)
        return (1)
    end

    /*
    **  If the column type is image, text, or timestamp, disallow the bind.
    **  Rules can't be bound to image, text, or timestamp columns.
    **  The types are checked in case
    **  there is a user-defined datatype that is an image or text.
    **  User-defined datatypes mapping to timestamp are not allowed
    **  by sp_addtype.
    */
    if type_name(@xtype) in ('text', 'ntext', 'image', 'timestamp')
        begin
            raiserror(15107,-1,-1)
            return (1)
        end

    BEGIN TRANSACTION txn_bindrule_1

        dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

        update syscolumns set domain = @ruleid
            where id = @tab_id and colid = @colid

    COMMIT TRANSACTION txn_bindrule_1

    raiserror(15514,-1,-1)

end
else
begin
    /*
    **  We're binding to a user type.  In this case, the @objname
    **  is really the name of the user datatype.
    **  When we bind to a user type, any existing columns get changed
    **  to the new binding unless their current binding is not equal
    **  to the current binding for the usertype or if they set the
    **  @futureonly parameter to @futurevalue.
    */
    declare @oldrule int            /* current rule for type */

    /*
    **  Get the current rule for the datatype.
    */
    select @oldrule = domain, @xtype = xtype, @xusertype = xusertype
        from systypes where name = @UnqualObj and xusertype > 256
        AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(uid))=1)

    if @oldrule is null
        begin
            raiserror(15105,-1,-1)
            return (1)
        end

    /*
    **  If the column type is image, text, or timestamp, disallow the bind.
    **  Rules can't be bound to image or text columns.
    */
    if type_name(@xtype) in ('text', 'ntext', 'image', 'timestamp')
        begin
            raiserror(15107,-1,-1)
            return (1)
        end

    update systypes set domain = @ruleid
            from systypes
        where xusertype = @xusertype


    raiserror(15515,-1,-1)

    /*
    **  Now see if there are any columns with the usertype that
    **  need the new binding.
    */
    if isnull(@futureonly, ' ') <> @futurevalue
    begin
        declare csr_bindrule_1 insensitive cursor for
          select
            distinct
                 c.id
                ,c.colid
            from     syscolumns c JOIN sysobjects o ON c.id = o.id AND o.xtype = 'U '
            where     c.xusertype    = @xusertype
            and    (c.domain    = @oldrule    OR
                 c.domain    = 0
                )
            order by c.id
                  for read only

        open csr_bindrule_1

        BEGIN TRANSACTION txn_bindrule_2

        fetch next from csr_bindrule_1 into
            @tab_id
            ,@colid

        WHILE @@fetch_status = 0
        begin

            select @vc1 = quotename(user_name(OBJECTPROPERTY(@tab_id,'OwnerId'))) + '.'
                        + quotename(object_name(@tab_id))

            dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

            select @cur_tab_id = @tab_id

            while @cur_tab_id = @tab_id and @@fetch_status = 0
            begin
                /*
                **  Update syscolumns with new binding.
                */
                update syscolumns
                    set domain = @ruleid
                    where id = @tab_id and colid = @colid

                fetch next from csr_bindrule_1 into
                     @tab_id
                    ,@colid
            end

        end --loop 3

        deallocate csr_bindrule_1

        COMMIT TRANSACTION txn_bindrule_2

        raiserror(15516,-1,-1)
    end
end
return (0) -- sp_bindrule
go

raiserror(15339,-1,-1,'sp_unbindefault')
go
create procedure sp_unbindefault --- 1996/08/13 13:34
@objname nvarchar(776),         /* table/column or datatype name */
@futureonly varchar(15) = NULL   /* flag to indicate extent of binding */
as

declare @futurevalue varchar(15) /* the value of @futureonly that causes
               ** the binding to be limited */

declare
    @vc1            nvarchar(517)
declare
    @tab_id            integer
    ,@cur_tab_id    integer
    ,@colid            integer
    ,@cdefault        integer
    ,@olddefault    integer
    ,@xusertype        smallint

    ,@UnqualObj        sysname
    ,@QualObj1        sysname
    ,@QualObj2        sysname
    ,@QualObj3        sysname

set cursor_close_on_commit    off
select @futurevalue = 'futureonly'  /* initialize @futurevalue */

/*
**  When a default or rule is bound to a user-defined datatype, it is also
**  bound, by default, to any columns of the user datatype that are currently
**  using the existing default or rule as their default or rule.  This default
**  action may be overridden by setting @futureonly = @futurevalue when the
**  procedure is invoked.  In this case existing columns with the user
**  datatype won't have their existing default or rule changed.
*/

-- get name parts --
select @UnqualObj = parsename(@objname, 1),
        @QualObj1 = parsename(@objname, 2),
        @QualObj2 = parsename(@objname, 3),
        @QualObj3 = parsename(@objname, 4)

IF (@UnqualObj is NULL OR @QualObj3 is not null)
   begin
   raiserror(15253,-1,-1,@objname)
   return (1)
   end


------------------  Verify database.

if (@QualObj2 is not null and @QualObj1 is null)
    begin
        raiserror(15084,-1,-1)
        return (1)
    end

/*
**  If @objname is of the form tab.col then we are unbinding a column.
**  Otherwise its a datatype.  In the column case, we need to extract
**  and verify the table and column names and make sure the user owns
**  the table that is getting the default unbound.
*/
if @QualObj1 is not null
begin
    if (@QualObj2 is not null)
        select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1)
    else
        select @vc1 = QuoteName(@QualObj1)

   /*
   **  Find it and unbind it.
   */
   select @tab_id = c.id, @colid = c.colid, @cdefault = c.cdefault
   from syscolumns c, sysobjects o
      where c.id = o.id
         and c.name = @UnqualObj
         and o.id = object_id(@vc1,'local')
         and o.xtype = 'U '

    if @tab_id is null
    begin
         raiserror(15104,-1,-1,@QualObj1,@UnqualObj)
         return(1)
    end

    if @cdefault = 0
    begin
        raiserror(15236,-1,-1,@objname)
        return(1)
    end

    if exists
    (select    *
        from    sysobjects o
        where    @cdefault    = o.id
            and    @tab_id        = o.parent_obj)
        begin
            raiserror(15049,-1,-1, @objname)
            return (1)
        end

    BEGIN TRANSACTION txn_unbindefault_1

         /*
         **  Since binding a default is a schema change,
         **  update schema count
         **  for the object in the sysobjects table.
         */
        dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

        update syscolumns set cdefault = 0
            from syscolumns where id = @tab_id and colid = @colid

    COMMIT TRANSACTION txn_bindefault_1

    raiserror(15519,-1,-1)
    return (0)

end

else

begin
   /*
   **  We're unbinding to a user type.  In this case, the @objname
   **  is really the name of the user datatype.
   **  When we unbind to a user type, any existing columns get changed
   **  to the new binding unless their current binding is not equal
   **  to the current binding for the usertype or if they set the
   **  @futureonly parameter to @futurevalue.
   */

   /*
   **  Get the current default for the datatype.
   */
   select @olddefault = tdefault, @xusertype = xusertype
        from systypes where name = @UnqualObj and xusertype > 256
        AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(uid))=1)

   if @olddefault is null
      begin
         raiserror(15036,-1,-1,@UnqualObj)
         return (1)
      end

   if @olddefault = 0
      begin
         raiserror(15237,-1,-1,@UnqualObj)
         return (1)
      end

   update systypes set tdefault = 0
      from systypes
      where xusertype = @xusertype

   raiserror(15520,-1,-1)

   /*
   **  Now see if there are any columns with the usertype that
   **  need the new binding.
   */
   if isnull(@futureonly, ' ') <> @futurevalue
   begin
        declare csr_unbindefault_1 insensitive cursor for
            select
            distinct
                 c.id
                ,c.colid
            from     syscolumns c JOIN sysobjects o ON c.id = o.id AND o.xtype = 'U '
                 where  c.xusertype = @xusertype
                    and c.cdefault = @olddefault
            order by c.id
                    for read only

        open csr_unbindefault_1

        fetch next from csr_unbindefault_1 into
            @tab_id
            ,@colid

        BEGIN TRANSACTION txn_unbindefault_2

            while @@fetch_status = 0
            begin

                select @vc1 = quotename(user_name(OBJECTPROPERTY(@tab_id,'OwnerId'))) + '.'
                + quotename(object_name(@tab_id))

                dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

                select @cur_tab_id = @tab_id

                while @cur_tab_id = @tab_id and @@fetch_status = 0
                begin
                      update syscolumns set cdefault = 0
                    from syscolumns
                    where id = @tab_id and colid = @colid

                    fetch next from csr_unbindefault_1 into
                        @tab_id
                        ,@colid
                end
            end --loop 3      /*

        COMMIT TRANSACTION txn_unbindefault_2

        deallocate csr_unbindefault_1

        raiserror(15521,-1,-1)
    end
end

return (0) -- sp_unbindefault
go

raiserror(15339,-1,-1,'sp_unbindrule')
go
create procedure sp_unbindrule --- 1996/08/13 13:33
@objname nvarchar(776),         /* table/column or datatype name */
@futureonly varchar(15) = NULL      /* flag to indicate extent of binding */
as

declare @oldrule int /* current rule for type */
declare @tabname sysname     /* name of table */
declare @colname sysname     /* name of column */
declare @futurevalue varchar(15) /* the value of @futureonly that causes
                           ** the binding to be limited */

declare
    @vc1            nvarchar(517)
declare
     @obj_id        integer
    ,@cur_tab_id    integer
    ,@colid            integer
    ,@domain        integer
    ,@xusertype        smallint

    ,@owner_name    sysname
    ,@obj_name        sysname

    ,@UnqualObj            sysname
    ,@QualObj1            sysname
    ,@QualObj2            sysname
    ,@QualObj3            sysname

set cursor_close_on_commit    off

select @futurevalue = 'futureonly'  /* initialize @futurevalue */

/*
**  When a default or rule is bound to a user-defined datatype, it is also
**  bound, by default, to any columns of the user datatype that are currently
**  using the existing default or rule as their default or rule.  This default
**  action may be overridden by setting @futureonly = @futurevalue when the
**  procedure is invoked.  In this case existing columns with the user
**  datatype won't have their existing default or rule changed.
*/

-- get name parts --
select @UnqualObj = parsename(@objname, 1),
        @QualObj1 = parsename(@objname, 2),
        @QualObj2 = parsename(@objname, 3),
        @QualObj3 = parsename(@objname, 4)

IF (@UnqualObj is NULL OR @QualObj3 is not null)
   begin
   raiserror(15253,-1,-1,@objname)
   return (1)
   end


------------------  Verify database.

if (@QualObj2 is not null and @QualObj1 is null)
    begin
        raiserror(15084,-1,-1)
        return (1)
    end

/*
**  If @objname is of the form tab.col then we are unbinding a column.
**  Otherwise its a datatype.  In the column case, we need to extract
**  and verify the table and column names and make sure the user owns
**  the table that is getting the default unbound.
*/
if @QualObj1 is not null
begin
    if (@QualObj2 is not null)
        select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1)
    else
        select @vc1 = QuoteName(@QualObj1)

    select    @obj_id = c.id,    @colid = c.colid,    @domain = c.domain
        from syscolumns c, sysobjects o
    where c.id = o.id
    and c.name = @UnqualObj
    and o.id = object_id(@vc1,'local')
    and o.xtype = 'U '

    if @obj_id is null
    begin
        raiserror(15104,-1,-1,@QualObj1,@UnqualObj)
        return (1)
    end

    if @domain = 0
    begin
        raiserror(15238,-1,-1,@objname)
        return (1)
    end

    BEGIN TRANSACTION txn_unbindrule_1

        /*
        **  Update schema count
        **  for the object in the sysobjects table.
        */

        dbcc LockObjectSchema(@vc1) -- Locks Object and increments schema_ver.

        update syscolumns set domain = 0
            from syscolumns c where id = @obj_id and colid = @colid

    COMMIT TRANSACTION txn_unbindrule_1

    raiserror(15522,-1,-1)

end
else
begin

    select @oldrule = domain, @xusertype = xusertype
        from systypes where name = @UnqualObj and xusertype > 256
        AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(uid))=1)

    if @xusertype is null
    begin
        raiserror(15036,-1,-1,@UnqualObj)
        return (1)
    end

    if @oldrule = 0
    begin
        raiserror(15239,-1,-1,@UnqualObj)
        return (1)
    end

    update systypes set domain = 0
    from systypes
    where xusertype = @xusertype

    raiserror(15523,-1,-1)

    if isnull(@futureonly, ' ') <> @futurevalue
    begin

        declare csr_unbindrule_1 insensitive cursor for
        select
            distinct
             o.id
            ,user_name(o.uid)
            ,o.name
            ,c.colid
        from    syscolumns c
            ,sysobjects o
         where o.id = c.id and o.xtype = 'U '
            and c.xusertype = @xusertype
            and c.domain = @oldrule
        order by o.id
            for read only

        open csr_unbindrule_1


        BEGIN TRANSACTION txn_unbindrule_2

            fetch next from csr_unbindrule_1 into
            @obj_id
            ,@owner_name
            ,@obj_name
            ,@colid

            while @@fetch_status = 0
            begin

                select @vc1 = quotename(@owner_name) + '.' + quotename(@obj_name)

                dbcc LockObjectSchema(@vc1) --- Undocu. Locks out other schema changes until commit, and increments sysobjects.schema_ver.

                select @cur_tab_id = @obj_id

                while @cur_tab_id = @obj_id and @@fetch_status = 0
                begin
                      update syscolumns set domain = 0
                    from syscolumns
                    where id = @obj_id and colid = @colid

                    fetch next from csr_unbindrule_1 into
                    @obj_id
                    ,@owner_name
                    ,@obj_name
                    ,@colid
                end
            end

        COMMIT TRANSACTION txn_unbindrule_2
        deallocate csr_unbindrule_1
        raiserror(15524,-1,-1)

    end
end

return (0)    --sp_unbindrule
go

grant execute on sp_bindefault to public
grant execute on sp_bindrule to public
grant execute on sp_unbindefault to public
grant execute on sp_unbindrule to public
go

-- Bug 55324
------------------------------ sp_changedbowner -------------------------------
if object_id('sp_changedbowner','P') IS NOT NULL
    drop procedure sp_changedbowner
go
raiserror(15339,-1,-1,'sp_changedbowner')
go
create procedure sp_changedbowner
    @loginame       sysname,        -- login to become dbo
    @map            varchar(5) = NULL    -- True to map aliases, else drop
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @ret        int,
            @newsid     varbinary(85),
            @status     smallint

    -- CHECK PERMISSIONS (Note: All sysadmins are dbo) --
    if is_member('db_owner') = 0
    begin
        raiserror(15108,-1,-1)
        return(1)
    end

    -- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
    if db_name() in ('master', 'model', 'tempdb')
    begin
        raiserror(15109,-1,-1)
        return(1)
    end

    -- CHECK LOGIN NAME IS VALID (NT/SQL USER ONLY!) --
    select @newsid = sid, @status = 2 from master.dbo.syslogins
                    where loginname = @loginame and isntname = 0
    if @newsid is null
        select @status = 14, @newsid = get_sid('\U'+@loginame, NULL)
    if @newsid is null
    begin
        raiserror(15007,-1,-1,@loginame)
        return (1)
    end

    -- CHECK IF LOGIN ALREADY ALIASED IN DB --
    if exists (select sid from sysusers where isaliased = 1 and sid = @newsid)
    begin
        raiserror(15111,-1,-1)
        return (1)
    end

    -- CHECK IF LOGIN ALREADY KNOWN TO DATABASE --
    if exists (select sid from sysusers where sid = @newsid)
    begin
        raiserror(15110,-1,-1)
        return (1)
    end

    -- MAKE THE FOLLOWING REMOVE/REMAP/DELETES ATOMIC --
    begin transaction

    -- REMAP DBO TO NEW SID --
    update sysusers set sid = @newsid, status = @status, updatedate = getdate()
            where name = 'dbo'

    -- REMOVE OTHER DBO-ALIASES IF REMAPPING NOT REQUESTED --
    if lower(@map) <> 'true'
    begin
        delete from sysusers where isaliased = 1 and altuid = user_id('dbo')
        raiserror(15500,-1,-1)
    end
    else
        raiserror(15499,-1,-1)     -- nothing to do to <remap>

    -- REFLECT NEW OWNER IN SYSDATABASES --
    update master.dbo.sysdatabases set sid = @newsid where dbid = db_id()
    commit transaction

    -- CHECKPOINT DATABASE TO FORCE CHANGES TO IN-MEMORY STRUCTURE --
    checkpoint
    raiserror(15501,-1,-1)
    return (0) -- sp_changedbowner
go
grant execute on sp_changedbowner to public
go


-- Bug 55748
if object_id('sp_addtype','P') IS NOT NULL
    drop procedure sp_addtype
if object_id('sp_droptype','P') IS NOT NULL
    drop procedure sp_droptype
if object_id('sp_check_removable','P') IS NOT NULL
    drop procedure sp_check_removable
if object_id('sp_check_removable_sysusers','P') IS NOT NULL
    drop procedure sp_check_removable_sysusers
if object_id('sp_addrole','P') IS NOT NULL
    drop procedure sp_addrole
go

raiserror(15339,-1,-1,'sp_addtype')
go
create procedure sp_addtype --- 1996/04/08 00:00
@typename sysname,        -- name of user-defined type
@phystype sysname,        -- physical system type of user-defined type
@nulltype varchar(8) = null,    -- nullability of new type
@owner sysname = null    -- Owner of type (default is caller)
as

declare @len int        -- length of user type
declare @type tinyint        -- typeid of physical type
declare @tlen smallint        -- length of physical type
declare @typeid smallint    -- user typeid of physical type
declare @nonull bit        -- default is getansinull()
declare @prec int        -- precision of the datatype
declare @scale int        -- scale of the datatype
declare @tprec tinyint        -- precision of the datatype read from systypes
declare @tscale tinyint        -- scale of the datatype read from systypes
declare @tname sysname  -- typename from systypes
declare @tstat tinyint      -- typestat from systypes
declare @orig_phystype    sysname

select @orig_phystype = @phystype
select @nulltype = rtrim(lower(@nulltype))
select @typename = rtrim(@typename)
select @phystype = lower(rtrim(@phystype))

-- VALIDATE THE @owner NAME (and verify caller can use this name)
declare @uid smallint
if @owner is null
    select @uid = user_id()
else
    select @uid = uid from sysusers where name = @owner
        and isaliased = 0 AND uid NOT IN (0,3,4) --public/INFO_SCHEMA/etc can't own type
if @uid is null OR
    (is_member('db_owner')=0 AND
     is_member('db_ddladmin')=0 AND
     is_member(user_name(@uid))=0)
begin
    raiserror(15600, -1, -1, 'sp_addtype')
    return 1
end

-- TYPES BASED ON BIT CAN BE NULL IN SPHINX,
--    BUT MAKE NOT-NULL THE DFLT FOR BCKWRD-COMPAT
if lower(@phystype) = 'bit' and @nulltype is null
                -- If user didn't specify nullability,
                -- make sure it doesn't get set to nullable
                -- by getansinull()
        select @nulltype = 'not null'

/*
**  Should the user type allow NULLs?
*/
if @nulltype is null
    select @nonull = abs(getansinull()-1)
else if @nulltype = 'null'
    select @nonull = 0
else if @nulltype in ('not null','nonull')
    select @nonull = 1
else
    begin
        raiserror(15085,-1,-1)
        return (1)
    end

/*
**  Check to see that the @typename is valid.
*/
declare @returncode int
execute @returncode = sp_validname @typename
if @returncode <> 0
    return(1)

/*
**  Check to see if the user type already exists or a system type
**  whose name = lower(@typename) (or a synomym) already exists.
*/
if exists (select * from systypes where name = @typename)
        or lower(@typename)
        in ('character','character varying','char varying',
            'integer','dec','binary varying')

    begin
        raiserror(15029,-1,-1,@typename)
        return (1)
    end

/*
**  Check to see if the user type has been reserved for future use.
*/
if @typename in ('variant','bigint')
    begin
        raiserror(15075,-1,-1,@typename)
        return (1)
    end

/*
** Can't supply length with sysname type.
*/
if @phystype like 'sysname%(%'
    begin
        raiserror(15270,-1,-1)
        return(1)
    end

/*
** initialize the length to be NULL first.
*/
select @len = NULL

/*
** If precision and scale were given with the type - extract them
*/
if @phystype like '_%(_%,_%)'
begin
    select @prec = convert(int, substring(@phystype,
        charindex('(',@phystype) + 1,
        charindex(',',@phystype) - 1 - charindex('(',@phystype)))

    select @scale = convert(int, substring(@phystype,
        charindex(',',@phystype) + 1,
        charindex(')',@phystype) - 1 - charindex(',',@phystype)))
    /*
    ** Extract the physical type name
    */
    select @phystype = substring(@phystype, 1,
           charindex('(', @phystype) - 1)
end
else

/*
**  If a length was given with the user datatype, extract it.
*/
if @phystype like '_%(%)'
begin
    select @len = convert(int, substring(@phystype,
        charindex('(',@phystype) + 1,
        charindex(')',@phystype) - 1 - charindex('(',@phystype)))

    /*
    ** Extract the physical type name
    */
    select @phystype = substring(@phystype, 1,
           charindex('(', @phystype) - 1)
end

select @phystype = rtrim(@phystype)

if @phystype='character'
    select @phystype='char'
else if @phystype='character varying'
    select @phystype='varchar'
else if @phystype='char varying'
    select @phystype='varchar'
else if @phystype='integer'
    select @phystype='int'
else if @phystype='dec'
    select @phystype='decimal'
else if @phystype='binary varying'
    select @phystype='varbinary'

/*
**  Make sure that the physical type exists and get its characteristics.
**  System physical types have a xusertype < 256 and are owned by the
**  dbo (userid = 1).
*/
select @type = xtype, @tlen = length,
    @tprec = xprec, @tscale = xscale, @tstat = status, @tname = name
from systypes
    where xusertype < 256 and name = @phystype and uid = 1

if @type is null
begin
    raiserror(15036,-1,-1,@orig_phystype)
    return (1)
end

/*
**  Disallow user-defined datatypes on timestamps.  This is done because
**  a timestamp is not a basic type but is really a binary.  There is,
**  therefore, no way to tell if a user-defined datatype is mapped to
**  a binary or a timestamp.  Timestamps can't have rules or defaults.
*/
if @phystype = 'timestamp'
begin
    raiserror(15038,-1,-1)
    return (1)
end

/*
**  Check if the NULL status of the user type is consistent with the NULL status
**  of the physical type.  Here are the possible cases.
**
**           physical type
**          NULLs      NONULLs
**            -----------------
** user    NULLs    |  ok    |  no
** type NONULLs    |  ok    |  ok
*/
-- NOT NECESSARY: bit and timestamp both already special-cased
/**********
if @nonull = 0 and 1 = 0
    begin
        raiserror(15037,-1,-1,@orig_phystype)
        return (1)
    end
**********/

/* Decide about precision, scale, length
** First check from NUMERIC, DECIMAL
*/
if @tname in ('numeric','decimal')
begin
    /* Type is NUMERIC or DECIMAL */

    if @len > 0
        begin
            /* Length is really the precision
            ** Since no scale is specified then scale
            ** is minimum(Default, precision). Default = 4
            */
            select @prec = @len
            select @scale = 0
        end
    else
        if (@prec is NULL)
            begin
                select @prec = 18
                select @scale = 0
            end

    if (@prec > 38) or (@prec < 1)
        begin
            raiserror(15086,-1,-1)
            return (1)
        end

    if (@scale > @prec) or (@scale < 0)
        begin
            /*
            ** Illegal scale specified -- must be less than precision
            ** and positive.
            */
            raiserror(15087,-1,-1)
            return (1)
        end

    /* Compute length from precision */
    if (@prec <= 9)
        select @len = 5

    if (@prec > 9) and (@prec <= 19)
        select @len = 9

    if (@prec > 19) and (@prec <= 28)
        select @len = 13

    if (@prec > 28) and (@prec <= 38)
        select @len = 17

end
else

/*
**  Typeids 1 (char), 2 (varchar), 3 (binary) and 4 (varbinary) are the
**  only ones which allow a length to be specified.
*/
if @tname not in ('binary', 'varbinary', 'char', 'varchar', 'nchar', 'nvarchar')
    begin
        /*
        **  We can't use a length and we got one.
        */
        if @len > 0
            begin
                raiserror(15088,-1,-1)
                return (1)
            end

        /*
        **  Use the fixed length of the physical type.
        */
        select @len = @tlen
        select @prec = @tprec
        select @scale = @tscale
    end
else
    begin
        /*
        **  We need a length and we didn't get one.
        */
        if @len is null
            begin
                raiserror(15091,-1,-1)
                return (1)
            end

        -- need to adjust length for unicode (watch out for overflow!)
        if @tname in ('nchar', 'nvarchar') and (@len & 0x80000000) != 0x80000000
            select @len = @len * 2
        if @len <= 0 or @len > 8000
            begin
                raiserror(15092,-1,-1)
                return (1)
            end

        select @prec = @tprec
        select @scale = @tscale
    end

/*
**  Finally, get the maximum existing user type so we use it + 1 for this
**  new type.
*/
select @typeid = max(xusertype)
    from systypes

/*
**  There are no user defined types yet so use the first number (256).
*/
if @typeid < 256
    select @typeid = 256

-- Set null status bit
if @nonull = 1
    select @tstat = @tstat | 0x01
else
    select @tstat = @tstat & 0xFE

insert systypes (name, xtype, status, xusertype, length, xprec, xscale,
            tdefault, domain, uid, reserved)
    select @typename, @type, @tstat, @typeid + 1, @len, @prec, @scale,
            0, 0, @uid, 0

raiserror(15449,-1,-1)

return (0) -- sp_addtype
go

raiserror(15339,-1,-1,'sp_droptype')
go
create procedure sp_droptype --- 1996/04/08 00:00
@typename sysname            /* the user type to drop */
as

declare @typeid smallint        /* the typeid of the usertype to drop */

/*
**  Initialize @typeid so we can tell if we can't find it.
*/
select @typeid = 0

/*
**  Find the user type with @typename.  It must be a user type (xusertype > 256)
**  and it must be owned by the person (or special role) running the procedure.
*/
select @typeid = xusertype
    from systypes
        where name = @typename and xusertype > 256
        AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(uid))=1)

if @typeid = 0
    begin
        raiserror(15105,-1,-1)
        return (1)
    end

/*
**  Check to see if the type is being used.  If it is, it can't be dropped.
*/
if exists (select * from syscolumns where xusertype = @typeid)
    begin
        raiserror(15180,-1,-1)

        /*
        **  Show where it's being used.
        */
        select object = o.name, type = o.xtype, owner = u.name,
            [column] = c.name, datatype = t.name
        from syscolumns c, systypes t, sysusers u, sysobjects o
        where c.xusertype = @typeid
            and t.xusertype = @typeid
            and o.uid = u.uid
            and c.id = o.id
        order by object, [column]

        return (1)
    end

/*
**  Everything is consistent so drop the type.
*/
delete from systypes where xusertype = @typeid

raiserror(15467,-1,-1)

return (0) -- sp_droptype
go

raiserror(15339,-1,-1,'sp_check_removable')
go
create procedure sp_check_removable @autofix varchar(4)
as

declare @dbosid varbinary (86)
declare @dbname sysname
declare @exec_stmt nvarchar(540)
declare @fgname sysname

select @dbname=db_name()

/* Verify that SA owns the database. */

select @dbosid = sid from master..sysdatabases where name = @dbname
if @dbosid <> 0x01
    if @autofix='auto'
    begin
        -- changing DBO to SA
        update sysdatabases set sid = 0x01
            where name = @dbname
        update sysusers set sid = 0x01
            where uid = 1
    end
    else
    begin
        raiserror(15258,-1,-1, @dbname)
        return(1)
    end

    -- USE CORRECT non-dbo/guest CHECKING
    declare @ret int
    exec @ret = sp_check_removable_sysusers @autofix
    if @ret <> 0
        return 1

    -- Run UPDATE STATISTICS on all user tables if there are
    -- no user defined filegroups
    if @autofix='auto' and
        (select count(*) from sysfilegroups) = 1
    begin
        select @exec_stmt = N'USE ' + quotename( @dbname , '[')
        + N' exec sp_updatestats '
        exec (@exec_stmt)
    end

    exec('dump tran '+@dbname+' with no_log')

    if (select count(*) from sysfilegroups) > 1
    begin
        if @autofix='auto'
        begin
            -- Mark any non-primary filegroups as READONLY
            DECLARE fgcursor INSENSITIVE CURSOR
            FOR SELECT groupname FROM sysfilegroups fg
                WHERE fg.groupid > 1 -- not primary
                AND fg.status & 0x8 = 0 -- not already readonly
                AND (SELECT count (*) FROM sysfiles f WHERE
                f.groupid = fg.groupid) > 0 -- has some files
            OPEN fgcursor
            FETCH NEXT FROM fgcursor INTO @fgname
            WHILE (@@FETCH_STATUS <> -1)
            BEGIN
                SELECT @exec_stmt = 'ALTER DATABASE '
                + quotename( @dbname , '[')
                + ' MODIFY FILEGROUP '
                + quotename( @fgname , '[')
                + ' READONLY'
                EXEC (@exec_stmt)
                FETCH NEXT FROM fgcursor INTO @fgname
            END
            CLOSE fgcursor
            DEALLOCATE fgcursor
        end
        else
        begin
            if exists (SELECT groupname FROM sysfilegroups fg
                WHERE fg.groupid > 1 -- not primary
                AND fg.status & 0x8 = 0 -- not already readonly
                AND (SELECT count (*) FROM sysfiles f WHERE
                f.groupid = fg.groupid) > 0) -- has some files
            begin
                raiserror(15358,-1,-1)
                SELECT groupname FROM sysfilegroups fg
                    WHERE fg.groupid > 1 -- not primary
                    AND fg.status & 0x8 = 0 -- not already readonly
                return (-1)
            end
        end


    end
return(0)
go

raiserror(15339,-1,-1,'sp_check_removable_sysusers')
go
-----------------------------------------------------
-- NOTE: FOR INTERNAL USE ONLY (sp_certify_removable)
--      DO NOT DOCUMENT OR USE!
-----------------------------------------------------
create procedure sp_check_removable_sysusers
    @autofix    varchar(4)      -- true or other
as
    -- CHECK FOR DATABASE OWNED BY SQL USER --
    if exists (select name from sysusers where name = 'dbo' and issqluser = 1 and sid <> suser_sid('sa'))
    begin
        if @autofix <> 'auto'
        begin
            raiserror(15258,-1,-1)
            return(1)
        end

        -- MAKE SA THE DBO --
        raiserror(15502,-1,-1)
        update sysusers set sid = suser_sid('sa'), status = 2, updatedate = getdate()
                where name = 'dbo'
    end

    -- CHECK FOR PERMISSIONS GRANTED TO or BY SQL USERS --
    if exists (select grantee from syspermissions where grantee in
                (select uid from sysusers u where issqluser = 1 and u.uid > 4))
    OR exists (select grantor from syspermissions where grantor in
                (select uid from sysusers u where issqluser = 1 and u.uid > 4))
    begin
        if @autofix = 'auto'
            PRINT 'CANNOT AUTO-AUTOFIX GRANT-WITH-GRANT CHAINS'
        raiserror(15053,-1,-1)
        return(1)
    end

    -- CHECK FOR OBJECTS OWNED BY SQL USERS --
    if exists (select uid from sysobjects where uid in
                (select uid from sysusers u where issqluser = 1 and u.uid > 4))
    begin
        if @autofix <> 'auto'
        begin
            raiserror(15053,-1,-1)
            return(1)
        end

        -- ASSIGN DBO AS OWNER OF OTHER OBJECTS (MAY FAIL WITH DUPL!) --
        raiserror(15503,-1,-1)
        update sysobjects set uid = 1 where uid in
                (select uid from sysusers u where issqluser = 1 and u.uid > 4)
        if @@error <> 0
            return (1)
    end

    -- CHECK FOR TYPES OWNED BY SQL USERS --
    if exists (select uid from systypes where uid in
                (select uid from sysusers u where issqluser = 1 and u.uid > 4))
    begin
        if @autofix <> 'auto'
        begin
            raiserror(15053,-1,-1)
            return(1)
        end

        -- ASSIGN DBO AS OWNER OF TYPES --
        raiserror(15503,-1,-1)
        update systypes set uid = 1 where uid in
                (select uid from sysusers u where issqluser = 1 and u.uid > 4)
    end

    -- CHECK FOR ROLES OWNED BY SQL USERS --
    if exists (select altuid from sysusers where (issqlrole = 1 or isapprole = 1) and
        altuid in (select uid from sysusers u where u.issqluser = 1 and u.uid > 4))
    begin
        if @autofix <> 'auto'
        begin
            raiserror(15053,-1,-1)
            return(1)
        end

        -- ASSIGN DBO AS OWNER OF TYPES --
        raiserror(15503,-1,-1)
        update sysusers set altuid = 1, updatedate = getdate()
            where (issqlrole = 1 or isapprole = 1) and
            altuid in (select uid from sysusers u where u.issqluser = 1 and u.uid > 4)
    end

    -- CHECK FOR SQL LOGINS AS USERS --
    if exists (select uid from sysusers where issqluser = 1 and uid > 4)
    begin
        if @autofix <> 'auto'
        begin
            raiserror(15254,-1,-1)
            return(1)
        end

        -- DELETE SQL USERS AND DEPENDENT ALIASES --
        raiserror(15504,-1,-1)
        delete from sysusers where issqluser = 1 and uid > 4
        delete from sysusers where isaliased = 1 and user_name(altuid) is null
    end

    -- CHECK FOR SQL LOGINS ALIASED --
    if exists (select uid from sysusers where isaliased = 1 and isntname = 0)
    begin
        if @autofix <> 'auto'
        begin
            raiserror(15254,-1,-1)
            return(1)
        end

        -- DELETE ALIASED SQL USERS --
        raiserror(15504,-1,-1)
        delete from sysusers where isaliased = 1 and isntname = 0
    end

    -- Success
    return 0
go

raiserror(15339,-1,-1,'sp_addrole')
go
create procedure sp_addrole
    @rolename   sysname,        -- name of new role
    @ownername  sysname = 'dbo' -- name of owner of new role
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
    set nocount on
    declare @ret        int,    -- return value of sp call
            @uid        smallint,
            @owner      smallint

    -- CHECK PERMISSIONS --
    if (not is_member('db_securityadmin') = 1) and
       (not is_member('db_owner') = 1)
    begin
        raiserror(15000,-1,-1)
        return (1)
    end

    -- DISALLOW USER TRANSACTION --
    set implicit_transactions off
    if (@@trancount > 0)
    begin
        raiserror(15002,-1,-1,'sp_addrole')
        return (1)
    end

        -- RESOLVE OWNER NAME --
    select @owner = uid from sysusers where name = @ownername
                    and isaliased = 0 AND uid NOT IN (0,3,4) --public/INFO_SCHEMA/etc can't own role
    if @owner is null
    begin
        raiserror(15008,-1,-1,@ownername)
        return (1)
    end

    -- VALIDATE ROLE NAME --
    execute @ret = sp_validname @rolename
    if @ret <> 0
        return (1)
    if (charindex('\', @rolename) > 0)
    begin
        raiserror(15006,-1,-1,@rolename)
        return (1)
    end

    -- ERROR IF SYSUSERS NAME ALREADY EXISTS --
    if user_id(@rolename) is not null
    begin
        if exists (select name from sysusers where issqlrole = 1 and name = @rolename)
            raiserror(15363,-1,-1,@rolename)
        else
            raiserror(15023,-1,-1,@rolename)

        return (1)
    end

    -- OBTAIN NEW ROLE UID (RESERVE 16384-16399) --
    if user_name(16400) IS NULL
        select @uid = 16400
    else
        select @uid = min(uid)+1 from sysusers
            where uid >= 16400 and uid < (32767 - 1)    -- stay in role range
                and user_name(uid+1) is null            -- uid not in use
    if @uid is null
    begin
        raiserror(15065,-1,-1)
        return (1)
    end

    -- INSERT THE ROW INTO SYSUSERS --
    insert into sysusers values
        (@uid, 0, @rolename, NULL, 0x00, getdate(), getdate(), @owner, NULL)

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    if @@error <> 0
        return (1)
    raiserror(15424,-1,-1)
    return (0) -- sp_addrole
go

grant execute on sp_addtype to public
grant execute on sp_droptype to public
grant execute on sp_addrole to public
go

--------------------------------------------------------------------------------
--    Schema Procs (OLEDBSCH.SQL / ANSIVIEW.SQL)
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
--    ODBC/OLEDB Catalog Procs (INSTCAT.SQL)
--------------------------------------------------------------------------------

-- BUG 55811 and 56158 (Ported from Shiloh fix)

if (exists (select * from sysobjects
        where name = 'sp_statistics' and type = 'P '))
    drop proc sp_statistics
go

if (exists (select * from sysobjects
        where name = 'sp_pkeys' and type = 'P '))
    drop proc sp_pkeys
go

-- BUG 57054
if (exists (select * from sysobjects
        where name = 'sp_fkeys' and type = 'P '))
    drop proc sp_fkeys
go

-- sp_pkeys
CREATE PROCEDURE sp_pkeys(
               @table_name        sysname,
               @table_owner     sysname = null,
               @table_qualifier sysname = null )
as
    DECLARE @table_id            int
    DECLARE @full_table_name    varchar(255) /* 2*128 + 1 */

    if @table_qualifier is not null
    begin
        if db_name() <> @table_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror (15250, -1,-1)
            return
        end
    end
    if @table_owner is null
    begin    /* If unqualified table name */
        SELECT @full_table_name = quotename(@table_name)
    end
    else
    begin    /* Qualified table name */
        if @table_owner = ''
        begin    /* If empty owner name */
            SELECT @full_table_name = quotename(@table_owner)
        end
        else
        begin
            SELECT @full_table_name = quotename(@table_owner) +
                '.' + quotename(@table_name)
        end
    end
    /*    Get Object ID */
    SELECT @table_id = object_id(@full_table_name)

    select
        TABLE_QUALIFIER = convert(sysname,db_name()),
        TABLE_OWNER = convert(sysname,user_name(o.uid)),
        TABLE_NAME = convert(sysname,o.name),
        COLUMN_NAME = convert(sysname,c.name),
        KEY_SEQ =
            case
                when c.name = index_col(@full_table_name, i.indid,  1) then 1
                when c.name = index_col(@full_table_name, i.indid,  2) then 2
                when c.name = index_col(@full_table_name, i.indid,  3) then 3
                when c.name = index_col(@full_table_name, i.indid,  4) then 4
                when c.name = index_col(@full_table_name, i.indid,  5) then 5
                when c.name = index_col(@full_table_name, i.indid,  6) then 6
                when c.name = index_col(@full_table_name, i.indid,  7) then 7
                when c.name = index_col(@full_table_name, i.indid,  8) then 8
                when c.name = index_col(@full_table_name, i.indid,  9) then 9
                when c.name = index_col(@full_table_name, i.indid, 10) then 10
                when c.name = index_col(@full_table_name, i.indid, 11) then 11
                when c.name = index_col(@full_table_name, i.indid, 12) then 12
                when c.name = index_col(@full_table_name, i.indid, 13) then 13
                when c.name = index_col(@full_table_name, i.indid, 14) then 14
                when c.name = index_col(@full_table_name, i.indid, 15) then 15
                when c.name = index_col(@full_table_name, i.indid, 16) then 16
            end,
        PK_NAME = convert(sysname,i.name)
    from
        sysindexes i, syscolumns c, sysobjects o
    where
        o.id = @table_id
        and o.id = c.id
        and o.id = i.id
        and (i.status & 0x800) = 0x800
        and (c.name = index_col (@full_table_name, i.indid,  1) or
             c.name = index_col (@full_table_name, i.indid,  2) or
             c.name = index_col (@full_table_name, i.indid,  3) or
             c.name = index_col (@full_table_name, i.indid,  4) or
             c.name = index_col (@full_table_name, i.indid,  5) or
             c.name = index_col (@full_table_name, i.indid,  6) or
             c.name = index_col (@full_table_name, i.indid,  7) or
             c.name = index_col (@full_table_name, i.indid,  8) or
             c.name = index_col (@full_table_name, i.indid,  9) or
             c.name = index_col (@full_table_name, i.indid, 10) or
             c.name = index_col (@full_table_name, i.indid, 11) or
             c.name = index_col (@full_table_name, i.indid, 12) or
             c.name = index_col (@full_table_name, i.indid, 13) or
             c.name = index_col (@full_table_name, i.indid, 14) or
             c.name = index_col (@full_table_name, i.indid, 15) or
             c.name = index_col (@full_table_name, i.indid, 16)
            )
    order by 1, 2, 3, 5
go

grant execute on sp_pkeys to public
go

-- sp_statitics
CREATE PROCEDURE sp_statistics (
                 @table_name        sysname,
                 @table_owner        sysname = null,
                 @table_qualifier    sysname = null,
                 @index_name        sysname = '%',
                 @is_unique         char(1) = 'N',
                 @accuracy            char(1) = 'Q')
AS
    set nocount on
    DECLARE @indid                int
    DECLARE @lastindid            int
    DECLARE @table_id            int
    DECLARE @full_table_name    nvarchar(257)

    create table #TmpIndex(
        TABLE_QUALIFIER sysname NULL,
        TABLE_OWNER     sysname NULL,
        TABLE_NAME        sysname NOT NULL,
        INDEX_QUALIFIER sysname null,
        INDEX_NAME        sysname null,
        NON_UNIQUE        smallint null,
        TYPE            smallint NOT NULL,
        SEQ_IN_INDEX    smallint null,
        COLUMN_NAME     sysname null,
        COLLATION        char(1) null,
        index_id        int null,
        CARDINALITY     int null,
        PAGES            int null,
        status            int NOT NULL)

    if @table_qualifier is not null
    begin
        if db_name() <> @table_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror (15250, -1,-1)
            return
        end
    end

    if @accuracy not in ('Q','E')
        begin
            raiserror (15251,-1,-1,'accuracy','''Q'' or ''E''')
            return
        end

    if @table_owner is null
    begin    /* If unqualified table name */
        SELECT @full_table_name = quotename(@table_name)
    end
    else
    begin    /* Qualified table name */
        if @table_owner = ''
        begin    /* If empty owner name */
            SELECT @full_table_name = quotename(@table_owner)
        end
        else
        begin
            SELECT @full_table_name = quotename(@table_owner) +
                '.' + quotename(@table_name)
        end
    end
    /*    Get Object ID */
    SELECT @table_id = object_id(@full_table_name)

    /*    Start at lowest index id */
    SELECT @indid = min(indid)
    FROM sysindexes
    WHERE not (@table_id is null)
        AND id = @table_id
        AND indid > 0
        AND indid < 255

    /* Create a temp table to correct the ordinal position of the columns */
    create table #TmpColumns
    (ordinal int identity(1,1),
     colid   smallint not null)

    /* Load columns into the temp table */
    insert into #TmpColumns (colid)
    select c.colid
    from syscolumns c
    where c.id = @table_id
    order by c.colid

    WHILE @indid is not NULL
    BEGIN
        INSERT #TmpIndex    /* Add all columns that are in index */
            SELECT
                DB_NAME(),                        /* TABLE_QUALIFIER */
                USER_NAME(o.uid),                    /* TABLE_OWNER       */
                o.name,                         /* TABLE_NAME       */
                o.name,                         /* INDEX_QUALIFIER */
                x.name,                         /* INDEX_NAME       */
                case                            /* NON_UNIQUE       */
                    WHEN x.status&2 <> 2 then 1            /* Nonunique index */
                    else 0                        /* Unique index    */
                end,
                case                            /* TYPE        */
                    when @indid > 1 then 3                /* Non-Clustered   */
                    else 1                        /* Clustered index */
                end,
                tc.ordinal,                        /* SEQ_IN_INDEX    */
                INDEX_COL(@full_table_name, indid, tc.ordinal),        /* COLUMN_NAME       */
                'A',                            /* COLLATION       */
                @indid,                         /* index_id        */
                case                            /* CARDINALITY       */
                    when @indid > 1 then NULL            /* Non-Clustered   */
                    else x.rows                     /* Clustered index */
                end,
                case                            /* PAGES       */
                    when @indid > 1 then NULL            /* Non-Clustered   */
                    else x.dpages                    /* Clustered index */
                end,
                x.status                        /* status       */
            FROM sysindexes x, syscolumns c, sysobjects o, #TmpColumns tc
            WHERE
                not (@table_id is null)
                AND x.id = @table_id
                AND x.id = o.id
                AND x.id = c.id
                AND tc.colid = c.colid
                AND tc.ordinal < keycnt+(x.status&18)/18    /* all but Unique Clust indices have an extra key */
                AND INDEX_COL(@full_table_name, indid, tc.ordinal) IS NOT NULL
                AND indid = @indid
                AND (x.status&2 = 2
                    OR @is_unique <> 'Y')
                AND (x.status&32) = 0
        /*
        **      Now move @indid to the next index.
        */
        SELECT @lastindid = @indid
        SELECT @indid = NULL

        SELECT @indid = min(indid)
        FROM sysindexes
        WHERE not (@table_id is null)
            AND id = @table_id
            AND indid > @lastindid
            AND indid < 255
    END

    /* now add row for table statistics */
    INSERT #TmpIndex
        SELECT
            DB_NAME(),                /* TABLE_QUALIFIER */
            USER_NAME(o.uid),            /* TABLE_OWNER       */
            o.name,                 /* TABLE_NAME       */
            null,                    /* INDEX_QUALIFIER */
            null,                    /* INDEX_NAME       */
            null,                    /* NON_UNIQUE       */
            0,                    /* SQL_TABLE_STAT  */
            null,                    /* SEQ_IN_INDEX    */
            null,                    /* COLUMN_NAME       */
            null,                    /* COLLATION       */
            0,                    /* index_id        */
            x.rows,                 /* CARDINALITY       */
            x.dpages,                /* PAGES       */
            0                    /* status       */
        FROM sysindexes x, sysobjects o
        WHERE not (@table_id is null)
            AND o.id = @table_id
            AND x.id = o.id
            AND (x.indid = 0 or x.indid = 1)    /*    If there are no indexes */
                                /*    then table stats are in */
                                /*    a row with indid =0    */

    SELECT
        TABLE_QUALIFIER,
        TABLE_OWNER,
        TABLE_NAME,
        NON_UNIQUE,
        INDEX_QUALIFIER,
        INDEX_NAME,
        TYPE,
        SEQ_IN_INDEX,
        COLUMN_NAME,
        COLLATION,
        CARDINALITY,
        PAGES,
        FILTER_CONDITION = convert(varchar(128),null)
    FROM #TmpIndex
    WHERE
        INDEX_NAME like @index_name     /* If matching name      */
        or INDEX_NAME is null        /* If SQL_TABLE_STAT row */
    ORDER BY 4, 7, 6, 8

    DROP TABLE #TmpIndex, #TmpColumns
go

grant execute on sp_statistics to public
go

/*    Procedure for 7.0 and later servers */
CREATE PROCEDURE sp_fkeys(
               @pktable_name        sysname = null,
               @pktable_owner        sysname = null,
               @pktable_qualifier    sysname = null,
               @fktable_name        sysname = null,
               @fktable_owner        sysname = null,
               @fktable_qualifier    sysname = null )
as
    set nocount on
    DECLARE @pktable_id            int
    DECLARE @pkfull_table_name    nvarchar(257) /* 2*128 + 1 */
    DECLARE @fktable_id            int
    DECLARE @fkfull_table_name    nvarchar(257) /* 2*128 + 1 */
    declare    @order_by_pk        int

    /* select 'XXX starting table creation' */

    create table #fkeysall(
            rkeyid int NOT NULL,
            rkey1 int NOT NULL,
                rkey2 int NOT NULL,
                rkey3 int NOT NULL,
                rkey4 int NOT NULL,
                rkey5 int NOT NULL,
                rkey6 int NOT NULL,
                rkey7 int NOT NULL,
                rkey8 int NOT NULL,
                rkey9 int NOT NULL,
                rkey10 int NOT NULL,
                rkey11 int NOT NULL,
                rkey12 int NOT NULL,
                rkey13 int NOT NULL,
                rkey14 int NOT NULL,
                rkey15 int NOT NULL,
                rkey16 int NOT NULL,
            fkeyid int NOT NULL,
            fkey1 int NOT NULL,
                fkey2 int NOT NULL,
                fkey3 int NOT NULL,
                fkey4 int NOT NULL,
                fkey5 int NOT NULL,
                fkey6 int NOT NULL,
                fkey7 int NOT NULL,
                fkey8 int NOT NULL,
                fkey9 int NOT NULL,
                fkey10 int NOT NULL,
                fkey11 int NOT NULL,
                fkey12 int NOT NULL,
                fkey13 int NOT NULL,
                fkey14 int NOT NULL,
                fkey15 int NOT NULL,
                fkey16 int NOT NULL,
            constid int NOT NULL,
            name sysname NOT NULL)

    create table #fkeys(
            pktable_id        int NOT NULL,
            pkcolid         int NOT NULL,
            fktable_id        int NOT NULL,
            fkcolid         int NOT NULL,
            KEY_SEQ         smallint NOT NULL,
            fk_id            int NOT NULL,
            PK_NAME            sysname NOT NULL)

    create table #fkeysout(
            PKTABLE_QUALIFIER sysname NULL,
            PKTABLE_OWNER sysname NULL,
            PKTABLE_NAME sysname NOT NULL,
            PKCOLUMN_NAME sysname NOT NULL,
            FKTABLE_QUALIFIER sysname NULL,
            FKTABLE_OWNER sysname NULL,
            FKTABLE_NAME sysname NOT NULL,
            FKCOLUMN_NAME sysname NOT NULL,
            KEY_SEQ smallint NOT NULL,
            UPDATE_RULE smallint NULL,
            DELETE_RULE smallint NULL,
            FK_NAME sysname NULL,
            PK_NAME sysname NULL,
            DEFERRABILITY smallint null)

    /* select 'XXX starting parameter analysis' */

    select  @order_by_pk = 0

    if (@pktable_name is null) and (@fktable_name is null)
    begin    /* If neither primary key nor foreign key table names given */
        raiserror (15252,-1,-1)
        return
    end
    if @fktable_qualifier is not null
    begin
        if db_name() <> @fktable_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror (15250, -1,-1)
            return
        end
    end
    if @pktable_qualifier is not null
    begin
        if db_name() <> @pktable_qualifier
        begin    /* If qualifier doesn't match current database */
            raiserror (15250, -1,-1)
            return
        end
    end

    if @pktable_owner is null
    begin    /* If unqualified primary key table name */
        SELECT @pkfull_table_name = quotename(@pktable_name)
    end
    else
    begin    /* Qualified primary key table name */
        if @pktable_owner = ''
        begin    /* If empty owner name */
            SELECT @pkfull_table_name = quotename(@pktable_owner)
        end
        else
        begin
            SELECT @pkfull_table_name = quotename(@pktable_owner) +
                '.' + quotename(@pktable_name)
        end
    end

    if @fktable_owner is null
    begin    /* If unqualified foreign key table name */
        SELECT @fkfull_table_name = quotename(@fktable_name)
    end
    else
    begin    /* Qualified foreign key table name */
        if @fktable_owner = ''
        begin    /* If empty owner name */
            SELECT @fkfull_table_name = quotename(@fktable_owner)
        end
        else
        begin
            SELECT @fkfull_table_name = quotename(@fktable_owner) +
                '.' + quotename(@fktable_name)
        end
    end

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    begin tran

    /*    Get PK Object ID */
    SELECT @pktable_id = object_id(@pkfull_table_name)

    /*    Get FK Object ID */
    SELECT @fktable_id = object_id(@fkfull_table_name)

    if @fktable_name is not null
    begin
        if @fktable_id is null
            SELECT @fktable_id = 0    /* fk table not found, empty result */
    end

    if @pktable_name is null
    begin /*  If table name not supplied, match all */
        select @order_by_pk = 1
    end
    else
    begin
        if @pktable_id is null
        begin
            SELECT @pktable_id = 0    /* pk table not found, empty result */
        end
    end

    /*    SQL Server supports upto 16 PK/FK relationships between 2 tables */
    /*    Process syskeys for each relationship */
    /*  First, attempt to get all 16 keys for each rel'ship, then sort
        them out with a 16-way "insert select ... union select ..." */

    /* select 'XXX starting data analysis' */

    insert into #fkeysall
        select
            r.rkeyid,
            r.rkey1, r.rkey2, r.rkey3, r.rkey4,
                r.rkey5, r.rkey6, r.rkey7, r.rkey8,
                r.rkey9, r.rkey10, r.rkey11, r.rkey12,
                r.rkey13, r.rkey14, r.rkey15, r.rkey16,
            r.fkeyid,
            r.fkey1, r.fkey2, r.fkey3, r.fkey4,
                r.fkey5, r.fkey6, r.fkey7, r.fkey8,
                r.fkey9, r.fkey10, r.fkey11, r.fkey12,
                r.fkey13, r.fkey14, r.fkey15, r.fkey16,
            r.constid,
            i.name
        from
            sysreferences r, sysobjects o, sysindexes i
        where    r.constid = o.id
            AND o.xtype = 'F'
            AND r.rkeyindid = i.indid
            AND r.rkeyid = i.id
            AND r.rkeyid between isnull(@pktable_id, 0)
                            and isnull(@pktable_id, 0x7fffffff)
            AND r.fkeyid between isnull(@fktable_id, 0)
                            and isnull(@fktable_id, 0x7fffffff)

    /* select count (*) as 'XXX countall' from #fkeysall */

    insert into #fkeys
            select rkeyid, rkey1, fkeyid, fkey1, 1, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey2, fkeyid, fkey2, 2, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey3, fkeyid, fkey3, 3, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey4, fkeyid, fkey4, 4, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey5, fkeyid, fkey5, 5, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey6, fkeyid, fkey6, 6, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey7, fkeyid, fkey7, 7, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey8, fkeyid, fkey8, 8, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey9, fkeyid, fkey9, 9, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey10, fkeyid, fkey10, 10, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey11, fkeyid, fkey11, 11, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey12, fkeyid, fkey12, 12, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey13, fkeyid, fkey13, 13, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey14, fkeyid, fkey14, 14, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey15, fkeyid, fkey15, 15, constid, name
            from #fkeysall
        union all
            select rkeyid, rkey16, fkeyid, fkey16, 16, constid, name
            from #fkeysall

    /* select count (*) as 'XXX count' from #fkeys */

    insert into #fkeysout
        select
            PKTABLE_QUALIFIER = convert(sysname,db_name()),
            PKTABLE_OWNER = convert(sysname,USER_NAME(o1.uid)),
            PKTABLE_NAME = convert(sysname,o1.name),
            PKCOLUMN_NAME = convert(sysname,c1.name),
            FKTABLE_QUALIFIER = convert(sysname,db_name()),
            FKTABLE_OWNER = convert(sysname,USER_NAME(o2.uid)),
            FKTABLE_NAME = convert(sysname,o2.name),
            FKCOLUMN_NAME = convert(sysname,c2.name),
            KEY_SEQ,
            UPDATE_RULE = convert(smallint,1),
            DELETE_RULE = convert(smallint,1),
            FK_NAME = convert(sysname,OBJECT_NAME(fk_id)),
            PK_NAME,
            DEFERRABILITY = 7    /* SQL_NOT_DEFERRABLE */
        from #fkeys f,
            sysobjects o1, sysobjects o2,
            syscolumns c1, syscolumns c2
        where    o1.id = f.pktable_id
            AND o2.id = f.fktable_id
            AND c1.id = f.pktable_id
            AND c2.id = f.fktable_id
            AND c1.colid = f.pkcolid
            AND c2.colid = f.fkcolid

    commit tran

    /* select count (*) as 'XXX countout' from #fkeysout */

    if @order_by_pk = 1 /*    If order by PK fields */
        select
            PKTABLE_QUALIFIER, PKTABLE_OWNER, PKTABLE_NAME, PKCOLUMN_NAME,
            FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, FKCOLUMN_NAME,
            KEY_SEQ, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY
        from #fkeysout
        order by 1,2,3,9
    else        /*    Order by FK fields */
        select
            PKTABLE_QUALIFIER, PKTABLE_OWNER, PKTABLE_NAME, PKCOLUMN_NAME,
            FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, FKCOLUMN_NAME,
            KEY_SEQ, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY
        from #fkeysout
        order by 5,6,7,9
go

grant execute on sp_fkeys to public
go

CHECKPOINT
go


--------------------------------------------------------------------------------
--    SQLDMO System Procedures (SQLDMO.SQL)
--------------------------------------------------------------------------------
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MStablekeys')
    drop procedure sp_MStablekeys
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSforeachdb')
    drop procedure sp_MSforeachdb
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSforeachtable')
    drop procedure sp_MSforeachtable
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSloginmappings')
    drop procedure sp_MSloginmappings
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSobjectprivs')
    drop procedure sp_MSobjectprivs
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSscriptdb_worker')
    drop procedure sp_MSscriptdb_worker
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSdbuseraccess')
    drop procedure sp_MSdbuseraccess
go

/*******************************************************************************/
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 +  */
    exec(N'declare hC cursor global 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 */

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

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

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

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

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

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

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

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

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

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

    return @retval
go
/* End sp_MSforeachdb */

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

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

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

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

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

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

    return @retval
go
/* End sp_MSforeachtable */

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

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

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

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

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

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

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

    /*
     * Now bring them out by loginname, each in its own result set.
     * If this is for all logins, we'll return all logins; if for curdb,
     * only those in #loginmappings (i.e. only those mapped in curdb).
     */
    exec(N'declare hCForEachLogin cursor global 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 */

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

create proc sp_MSobjectprivs
    @objname nvarchar(776) = null,
    @mode nvarchar(10) = N'object',
    @objid int = null,
    @srvpriv int = null,
    @prottype int = null,
    @grantee nvarchar(258) = null,
   @flags int = 0,
   @rollup int = 0
as

    create table #objs(
        id  int NOT NULL
    )

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

    create table #tmp(
        action   int   NOT NULL,
        uid      int   NOT NULL,
    )

   /* mode    : 'object', 'user' or 'column'*/
   /*
    * Note:  This was expanded for 6.5 due to changes in sysprotects.columns usage, affecting
    * CPermission::ListPrivilegeColumns.  The following additional parameters are for this.
    */
   /* objid   : ID of the object we're querying */
   /* srvpriv : privilege that we're querying for (e.g. select) */
   /* prottype: Protect type, e.g. GRANT/REVOKE */
   /* grantee : Grantee name. */

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

   /* 7.5: mode 'column', and grantee != null, we want user column level permissions for CTable/CView::ListUserColumnPermissions */
   /*      @rollup added to indicate special rollup result set for column level permission, set to 1 to roll up */

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

    /* If @objid is not null, this is for the new query for perm cols. */
    if (@objid is not null) begin
        select u.name, o.name, a = col_name(p.id, a.number), a.low, a.high, a.number
            from master.dbo.spt_values a, sysprotects p, sysobjects o, sysusers u
            where p.id = @objid and p.action = @srvpriv and p.protecttype = @prottype
            and p.uid = user_id(@grantee)
            and p.columns != 0x01 and o.id = p.id and u.uid = o.uid
                and convert(tinyint, substring(isnull(p.columns, 0x01), a.low, 1)) &
                    -- 6.5 changed so that the bit 0 position is an "invert the bits" indicator:
                    --        when 0, behaviour is the same as in prior versions, and other bits
                    --            indicate columns with the specified privilege
                    --        when 1, the other bits are indicate columns lacking the specified privilege
                    a.high <> (case when (substring(isnull(p.columns, 0x00), 1, 1) & 1 = 0) then 0 else a.high end)
                    and col_name(p.id, a.number) is not null
                    and a.type = N'P' and a.number <= (select count(*) from syscolumns where id = @objid) order by a
        return 0
    end

    set nocount on

    /*
     * To get around a 4.21 subquery bug where returning count(*) of 0 (for proc cols)
     * causes the result set to return no rows, we need two passes; one to get the
     * objects, and another to explicitly use a value (@cols) instead of a subquery.
     */
    declare @id int, @uid int, @cols int
    select @id = null, @uid = null
    if (@mode like N'us%') begin
       select @uid = user_id(@objname)
   end else if (@mode like N'col%') and (@objname is null) and (@grantee is not null) begin
      /* 7.5, special path to get column level permissions from all objects on the specified user */
      select @uid = user_id(@grantee)
    end else begin
      select @id = object_id(@objname)
   end
    if (@id is null and @uid is null) begin
        RAISERROR (15001, -1, -1, @objname)
        return 1
    end

    /* Get a temp list of objects we're interested in.  Do not include repl_* users. */
   /* This is the original code */
   insert #objs select distinct p.id from sysprotects p
       where (@id is null or p.id = @id)
          and (@uid is null or p.uid = @uid)
       and p.action in (193, 195, 196, 197, 224, 26) and p.uid not in (16382, 16383)

    /* Use a "fake cursor" by deleting successive id's from #objs, as this must run on 4.21 */
    select @id = min(id) from #objs
    while (@id is not null) begin
        select @cols = count(*) from syscolumns c where c.id = @id

        insert #output select p.action, a.number, p.uid, p.protecttype, p.id, p.grantor
            from master.dbo.spt_values a, sysprotects p
            where convert(tinyint, substring( isnull(p.columns, 0x01), a.low, 1)) & a.high !=0
            and (p.id = @id)
            and (@uid is null or p.uid = @uid)
            and a.number <= @cols
            and a.type = N'P'

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

      /* First pass to correct duplicates */
      select @count = count(*) from #output where id = @id and colid = 0 and protecttype = 205
      if ( @count > 0 ) begin
         /* We might have duplicate rows for permission on single coulmn(s) at this point */
         /* Use a fake cursor to remove the duplicates first. */
         insert #tmp select action, uid from #output where id = @id and colid = 0 and protecttype = 205
         select @whataction = min(action) from #tmp
         select @whatid = uid from #tmp where action = @whataction
         while (@whataction is not null) begin
            if (@mode like N'col%') and (@objname is null) and (@grantee is not null) begin
               /* Special case for column level permissions on all objects for the specified user */
               delete #output where (@whatid = uid) and (colid = 0) and (protecttype = 205)
            end else begin
               delete #output where (@whatid = uid) and (colid <> 0) and (protecttype = 205)
            end
            delete #tmp where @whatid = uid
            select @whataction = min(action) from #tmp
            select @whatid = uid from #tmp where action = @whataction
         end
         delete #tmp
      end

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

        /* Increment our "fake cursor" column and get the next one. */
        delete #objs where id = @id
        select @id = min(id) from #objs
    end

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

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

    /*
     * Order output by uid so Public will script before other groups (we need to script privs for public before
     * other groups, before users; otherwise sysprotects doesn't hold onto things right).  Sub-order is by object id
     * so we know when we're done with one object and onto the next, then by protecttype to group all GRANTs and
     * REVOKEs together, and lastly by action (including ORDER_ACTION_BIT so scripting can be more efficient)
     * because we may have multiple rows for columns.
     */

    set nocount off
   if (@mode not like N'col%') begin
      /* Mode is not 'column', do the regular stuff */
       select p.action & ~convert(int, 0x10000000), N'column' = col_name(p.id, p.colid), p.uid, N'username' = user_name(p.uid),
               p.protecttype, o.name, N'owner' = user_name(o.uid), p.id, N'grantor' = user_name(p.grantor)
             from #output p, sysobjects o
             where o.id = p.id
             order by p.uid, p.id, p.protecttype, p.action
   end else
   /* Below are spcial cases for column level permissions */
   if (@objname is null) and (@grantee is not null) and (@rollup = 0) begin
      /* 7.5, special path to get column level permissions from all objects on the specified user */
      select N'ObjectName' = o.name, N'Owner' = user_name(o.uid), N'ColumnName' = col_name(p.id, p.colid), o.sysstat & 0x0f, p.id,
             p.action & ~convert(int, 0x10000000), p.protecttype
             from #output p, sysobjects o
             where p.id = o.id and p.uid = user_id(@grantee) and col_name(p.id, p.colid) is not null
             order by p.uid, p.id, p.protecttype, p.action
    end else if (@grantee is not null) and (@rollup = 0) begin
      /* 7.5, mode 'column', and grantee != null, we want column level permissions on this object for this user */
      select N'column' = col_name(p.id, p.colid), N'owner' = user_name(o.uid), N'username' = user_name(p.uid), o.sysstat & 0x0f, p.id,
             p.action & ~convert(int, 0x10000000), p.protecttype
             from #output p, sysobjects o
             where o.id = p.id and p.uid = user_id(@grantee) and col_name(p.id, p.colid) is not null
             order by p.uid, p.id, p.protecttype, p.action
   end else if (@grantee is null) and (@rollup = 0) begin
      /* 7.5, mode 'column', and grantee = null, we want column level permissions on this object for all users */
      select N'column' = col_name(p.id, p.colid), N'owner' = user_name(o.uid), N'username' = user_name(p.uid), o.sysstat & 0x0f, p.id,
             p.action & ~convert(int, 0x10000000), p.protecttype
             from #output p, sysobjects o
             where o.id = p.id and col_name(p.id, p.colid) is not null
             order by p.uid, p.id, p.protecttype, p.action
   end else if (@objname is null) and (@grantee is not null) and (@rollup <> 0) begin
      /* 7.5, roll up version of the special path to get column level permissions from all objects on the specified user */
      select distinct N'ObjectName' = o.name, N'owner' = user_name(o.uid),
             N'Select' = (case when ((p.action & ~convert(int, 0x10000000))=193) then 1 else 0 end),
             N'Update' = (case when ((p.action & ~convert(int, 0x10000000))=197) then 1 else 0 end),
             N'Type' = p.protecttype
             from #output p, sysobjects o
             where p.id = o.id and p.uid = user_id(@grantee) and col_name(p.id, p.colid) is not null
             order by o.name
   end else if (@grantee is null) and (@rollup <> 0) begin
      /* 7.5, roll up version of the special path to return column level permissions on this object for all users */
      select distinct N'UserName' = user_name(p.uid),
             N'Select' = (case when ((p.action & ~convert(int, 0x10000000))=193) then 1 else 0 end),
             N'Update' = (case when ((p.action & ~convert(int, 0x10000000))=197) then 1 else 0 end),
             N'Type' = p.protecttype
             from #output p, sysobjects o
             where o.id = p.id and col_name(p.id, p.colid) is not null
             order by user_name(p.uid)
   end
go
/* End sp_MSobjectprivs */

/*******************************************************************************/
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 global 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_MSdbuseraccess 'perm', 'dbname' -- selecting priv bit from specified db                       */
/* exec sp_MSdbuseraccess 'db', 'dbname'   -- select databases, need to change db if dbname is specified */
/* exec sp_MSdbuseraccess 'init', 'dbname' -- noop                                                       */
/*******************************************************************************/
print N''
print N'Creating sp_MSdbuseraccess'
print N''
go

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

   set nocount on

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

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

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

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

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

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

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

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

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

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

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

      open hCdbs

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

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

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

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

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

grant execute on sp_MStablekeys to public
go
grant execute on sp_MSforeachdb to public
go
grant execute on sp_MSforeachtable to public
go
grant execute on sp_MSloginmappings to public
go
grant execute on sp_MSobjectprivs to public
go
grant execute on sp_MSscriptdb_worker to public
go
grant execute on sp_MSdbuseraccess to public
go

CHECKPOINT
go

--------------------------------------------------------------------------------
--    End of SQLDMO System Procedures (SQLDMO.SQL)
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
-- END OF FILE: Turn off marking of system objects.
--    DO NOT ADD ANYTHING AFTER THIS POINT
--------------------------------------------------------------------------------
exec sp_MS_upd_sysobj_category 2
go

exec sp_configure 'allow updates',0
go

reconfigure with override
go