Accidentaly dropped sp_rename from master database (500pts)

I need help ASAP!  Somehow, I dropped the stored procedure "sp_rename" from my master db in MSSQL and as a result, I can't make any changes to any of the other databases in Enterprise Manager.  Please help, I need this fixed immediately!

Thanks!
wcotechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BillPowellCommented:
Here is the full code for sp_rename.

CREATE PROCEDURE sp_rename
      @objname      nvarchar(776),            -- up to 3-part "old" name
      @newname      sysname,                  -- one-part new name
      @objtype      varchar(13) = null      -- identifying the name
as
/********1*********2*********3*********4*********5**
DOCUMENTATION:
   [1]  To rename a table, the @objname (meaning OldName) parm can be
passed in totally unqualified or fully qualified.
   [2]  The SA or DBO can rename objects owned by lesser users,
without the need for SetUser.
   [3]  The Owner portion of a qualified name can usually be
passed in in the omitted form (as in MyDb..MyTab or MyTab).  The
typical exception is when the SA/DBO is trying to rename a table
where the @objname is present twice in sysobjects as a table
owned only by two different lesser users; requiring an explicit
owner qualifier in @objname.
   [4]  An unspecified Owner qualifier will default to the
current user if doing so will either resolve what would
otherwise be an ambiguity within @objtype, or will result
in exactly one match.
   [5]  If Database is part of the qualified @objname,
then it must match the current database.  The @newname parm can
never be qualified.
   [6]  Here are the valid @objtype values.  They correspond to
system tables which track each type:
      'column'  'database'  'index'  'object'  'userdatatype'
The @objtype parm is sometimes required.  It is always required
for databases.  It is required whenever ambiguities would
otherwise exist.  Explicit use of @objtype is always encouraged.
   [7]  Parms can use quoted_identifiers.  For example:
   Execute sp_rename 'amy."his table"','"her table"','object'
*********1*********2*********3*********4*********5*/
Set nocount      on
Set ansi_padding on

Declare @objtypeIN            varchar(13),
            @ExecRC                  integer,
            @CurrentDb            sysname,
            @CountNumNodes      integer,
            @UnqualOldName      sysname,
            @QualName1            sysname,
            @QualName2            sysname,
            @QualName3            sysname,
            @OwnAndObjName      nvarchar(517),      -- "[owner].[object]"
            @objid                  integer,
            @xtype                  nchar(2),
            @indid                  smallint,
            @colid                  smallint,
            @cnstid                  integer,
            @parent_obj            integer,
            @xusertype            smallint,
            @ownerid            smallint,
            @objid_tmp            integer,
            @xtype_tmp            nchar(2),
            @retcode            int,
            @replinfo            int,
            @replbits            int
-- initial (non-null) settings
Select      @CurrentDb            = db_name(),
            @objtypeIN            = @objtype,
            @replbits            = 129      --Indicates table is used in replication

-- make type case insensitive
select @objtype = lower(@objtypeIN)

------------------------------------------------------------------------
-------------------  PHASE 10:  Simple parm edits  ---------------------
------------------------------------------------------------------------

-- Valid rename-type param?
IF (@objtype is not null AND
      @objtype not in ('column', 'database', 'index', 'object', 'userdatatype'))
begin
      raiserror(15249,-1,-1,@objtypeIN,0)
      return 1
end
-- null names?
IF (@newname IS null)
begin
      raiserror(15223,-1,11,'NewName')
      return 1
end
if (@objname IS null)
begin
      raiserror(15223,-1,-1,'OldName')
      return 1
end

---------------  Is NewName minimally valid?

--Check for valid rename name
exec @retcode = sp_validname @newname
if @retcode <> 0
begin
      raiserror(15224,-1,15,@newname)
      return 1
end

-------- Parse apart the perhaps dots-qualified old name.

select @UnqualOldName = parsename(@objname, 1),
        @QualName1 = parsename(@objname, 2),
        @QualName2 = parsename(@objname, 3),
        @QualName3 = parsename(@objname, 4)
IF (@UnqualOldName IS Null)
begin
      raiserror(15253,-1,-1,@objname)
      return 1
end

-- count name parts --
select @CountNumNodes = CASE WHEN @QualName3 IS NOT NULL THEN 4
                             WHEN @QualName2 IS NOT NULL THEN 3
                             WHEN @QualName1 IS NOT NULL THEN 2
                             ELSE 1 END
IF (@objtype  = 'database' AND @CountNumNodes > 1)
begin
      Raiserror(15395,-1,20,@objtypeIN)
      return 1
end
if (@objtype in ('object','userdatatype') AND @CountNumNodes > 3)
begin
      raiserror(15225,-1,-1,@objname, @CurrentDb, @objtypeIN)
      return 1
end


---------------------------------------------------------------------------
----------------------  PHASE 20:  Settle Parm1ItemType  ------------------
---------------------------------------------------------------------------

------------- database?
IF (@objtype  = 'database')
begin
      execute @ExecRC = sp_renamedb @UnqualOldName ,@newname -- de-docu old sproc
      IF @ExecRC <> 0
            return 1
      GOTO LABEL_51_AFTERUPDATES
end

-- assuming column/index-name, obtain object/column id's
if @QualName2 is not null
      select @objid = object_id(QuoteName(@QualName2) +'.'+ QuoteName(@QualName1))
else
      select @objid = object_id(QuoteName(@QualName1))

select @xtype = xtype, @replinfo = replinfo from sysobjects where id = @objid

------------ column?
if (@objtype = 'column' or @objtypeIN is null)
begin
      -- find column
      select @colid = NULL
      if (@xtype in ('U','V'))
            select @colid = colid from syscolumns
                        where id = @objid and name = @UnqualOldName

      -- check for wrong param
      if ((@colid is not null AND @objtype <> 'column') OR
            (@colid is null AND @objtype = 'column'))
      begin
            raiserror(15248,-1,-1,@objtypeIN)
            return 1
      end

      -- remember if we've found a column
      IF (@colid is not null)
      begin
            if (@replinfo & @replbits <> 0)
                  begin
                        raiserror(15051,-1,-1)
                        return (0)
                  end
            select @objtype = 'column'
      end
end

------------ index?
if (lower(@objtype) = 'index' or @objtypeIN is null)
begin
      -- find index
      if (@xtype in ('U','V'))
            select @indid = indid from sysindexes
                        where id = @objid and name = @UnqualOldName
                              AND indid NOT IN (0, 255)

      -- check for wrong param
      if ((@indid is not null AND @objtype <> 'index') OR
            (@indid is null AND @objtype = 'index'))
      begin
            raiserror(15248,-1,-1,@objtypeIN)
            return 1
      end

      if (@indid is not null)
      begin
            select @objtype = 'index'
            select @cnstid = id, @xtype = xtype from sysobjects
                  where name = @UnqualOldName AND parent_obj = @objid and xtype in ('PK','UQ')
      end
end

------------ object?
if (@objtype = 'object' or @objtypeIN is null)
begin
      -- get object id, type
      select @objid_tmp = object_id(@objname)

      select @xtype_tmp = xtype, @replinfo = replinfo
      from sysobjects where id = @objid_tmp

      -- if object is a system table, a Scalar function, or a table valued function, skip it.

      -- Cannot rename system table
      if @xtype_tmp = 'S'
            select @objid_tmp = NULL

      -- check for wrong param
      if ((@objid_tmp is not null AND @objtype <> 'object') OR
            (@objid_tmp is null AND @objtype = 'object'))
      begin
            raiserror(15248,-1,-1,@objtypeIN)
            return 1
      end

      if (@objid_tmp is not null)
      begin

            if (@xtype_tmp in ('U'))
            begin
                  if (@replinfo & @replbits <> 0)
                  begin
                        raiserror(15051,-1,-1)
                        return (0)
                  end
            end

            select @objtype = 'object', @objid = @objid_tmp, @xtype = @xtype_tmp

            if (@xtype in ('PK','UQ'))
                  select @parent_obj = parent_obj from sysobjects where id = @objid
      end
end


------------ type?
if (@objtype = 'userdatatype' or @objtypeIN is null)
begin
      select @xusertype = xusertype from systypes
            where name = @UnqualOldName and xusertype > 256
                  AND (@QualName1 is null or uid = user_id(@QualName1))

      -- check for wrong param
      if ((@xusertype is not null AND @objtype <> 'userdatatype') OR
            (@xusertype is null AND @objtype = 'userdatatype'))
      begin
            raiserror(15248,-1,-1,@objtypeIN)
            return 1
      end

      if (@xusertype IS NOT null)
            select @objtype = 'userdatatype'
end

---------------------------------------------------------------------
-------------------  PHASE 30:  More parm edits  --------------------
---------------------------------------------------------------------

-- item type determined?
if (@objtype IS null)
begin
      raiserror(15225,-1,-1,@objname, @CurrentDb, @objtypeIN)
      return 1
end

-- was the original name valid given this type?
if (@objtype in ('object','userdatatype') AND @CountNumNodes > 3)
begin
      raiserror(15225,-1,-1,@objname, @CurrentDb, @objtypeIN)
      return 1
end

-- verify db qualifier is current db
if (@objtype in ('object','userdatatype'))
      select @QualName3 = @QualName2
if (isnull(@QualName3, @CurrentDb) <> @CurrentDb)
begin
      raiserror(15333,-1,-1,@QualName3)
      return 1
end

-- get owner id and check permissions
if (@objtype = 'userdatatype')
      select @ownerid = uid from systypes where xusertype = @xusertype
else
      select @ownerid = ObjectProperty(@objid, 'ownerid')
if (      (not (1 = is_member('db_owner')))
      AND (not (1 = is_member('db_ddladmin')))
      AND (not (1 = is_member(user_name(@ownerid)))) )
begin
      raiserror(15247,-1,-1)
      return 1
end

-- check if system object
if (ObjectProperty(@objid, 'IsMSShipped') = 1 OR
      ObjectProperty(@objid, 'IsSystemTable') = 1)
begin
      raiserror(15001,-1,-1, @objname)
      return 1
end

-- make sure orig no longer shows null
if @objtypeIN is null
      select @objtypeIN = @objtype

-- Check for name clashing with existing name(s)
if (@newname <> @UnqualOldName)
begin
      -- column name clash?
      if (@objtype = 'column')
            if (ColumnProperty(@objid, @newname, 'isidentity') is not null)
                  select @UnqualOldName = NULL
      -- object name clash?
      if ( (@objtype = 'object' AND @xtype in ('PK','UQ'))
                  OR @objtype = 'index')
            if exists (select * from sysindexes where id = @objid and name = @newname
                              and indid not in (0,255))
                  select @UnqualOldName = NULL
      -- index name clash?
      if (@objtype = 'object' OR @cnstid IS NOT null)
            if (object_id(QuoteName(user_name(@ownerid)) +'.'+ QuoteName(@newname)) is not null)
                  select @UnqualOldName = NULL
      -- type name clash?
      if (@objtype = 'userdatatype')
            if exists (select * from systypes where name = @newname)
                  select @UnqualOldName = NULL
      -- stop on clash
      if (@UnqualOldName is null)
      begin
            raiserror(15335,-1,-1,@newname,@objtypeIN)
            return 1
      end
end

--------------------------------------------------------------------------
--------------------  PHASE 32:  Temporay Table Isssue -------------------
--------------------------------------------------------------------------
-- Disallow renaming object to or from a temp name (starts with #)
if (@objtype = 'object' AND
      (substring(@newname,1,1) = N'#' OR
      substring(object_name(@objid),1,1) = N'#'))
begin
      raiserror(15600,-1,-1, 'sp_rename')
      return 1
end

--------------------------------------------------------------------------
--------------------  PHASE 34:  Cautionary messages  --------------------
--------------------------------------------------------------------------

if @objtype = 'column'
begin
      -- Check for Dependencies: No column rename if enforced dependency on column
      IF EXISTS (SELECT * FROM sysdepends WHERE depid = @objid AND depnumber = @colid AND deptype > 0)
      begin
            raiserror(15336,-1,-1, @objname)
            return 1
      end
end
else if @objtype = 'object'
begin
      -- Check for Dependencies: No RENAME or CHANGEOWNER of OBJECT when exists:
      IF EXISTS (SELECT * FROM sysdepends d WHERE
            d.depid = @objid            -- A dependency on this object
            AND d.deptype > 0            -- that is enforced
            AND @objid <> d.id            -- that isn't a self-reference (self-references don't use object name)
            AND @objid <>                  -- And isn't a reference from a child object (also don't use object name)
                  (SELECT o.parent_obj FROM sysobjects o WHERE o.id = d.id)
            )
      begin
            raiserror(15336,-1,-1, @objname)
            return 1
      end
end

-- WITH DEFERRED RESOLUTION, SYSDEPENDS IS NOT VERY ACCURATE, SO WE ALSO
--      RAISE THIS WARNING **UNCONDITIONALLY**, EVEN FOR NON-OBJECT RENAMES
raiserror(15477,-1,-1)

-- warn about dependencies...
if (@objtype = 'objects' and exists (select * from sysdepends where depid = @objid))
      raiserror(15337,-1,-1)

--------------------------------------------------------------------------
---------------------  PHASE 40:  Update system tables  ------------------
--------------------------------------------------------------------------

-- obtain owner-qual object name (for most below)
select @OwnAndObjName = QuoteName(user_name(@ownerid))+'.'+QuoteName(object_name(@objid))

-- DO THE UPDATES --
if (@objtype = 'userdatatype')                                    -------- change type name
      UPDATE systypes set name = @newname where xusertype = @xusertype
else if (@objtype = 'object')                                    -------- change object name
begin
      BEGIN  TRANSACTION
      -- Locks Object and increments schema_ver
      DBCC LockObjectSchema(@OwnAndObjName)
      -- update the object name
      UPDATE sysobjects set name = @newname where id = @objid
      -- update index-cnst name (no rows changed if not 'PK' or 'UQ')
      if (@xtype in ('PK','UQ'))
            UPDATE sysindexes set name = @newname where id = @parent_obj and name = @UnqualOldName
      -- update base/text index name (no rows changed if not there)
      else if (@xtype in ('U', 'TF'))
      begin
            UPDATE sysindexes set name = @newname where id = @objid AND indid = 0
            UPDATE sysindexes set name = convert(sysname,'t'+@newname)
                                          where id = @objid AND indid = 255
      end
      COMMIT TRANSACTION
end
else if (@objtype = 'index')                                    -------- change index name
begin
      BEGIN  TRANSACTION
      -- Locks Object and increments schema_ver.
      DBCC LockObjectSchema(@OwnAndObjName)
      -- update the index name
      UPDATE sysindexes set name = @newname where id = @objid and indid = @indid
      -- change object name if cnst
      if (@cnstid IS NOT null)
            UPDATE sysobjects set name = @newname where id = @cnstid
      COMMIT TRANSACTION
end
else if (@objtype = 'column')                                    -------- change column name
begin
      -- Use DBCC to check for column in use by check-constraint, computed-column, etc
      -- THIS IS NOT A DOCUMENTED DBCC: DO NOT USE DIRECTLY!
      DBCC RENAMECOLUMN ( @OwnAndObjName, @UnqualOldName, @newname )
end


-------------------------  Finalization  -----------------------
LABEL_51_AFTERUPDATES:
Raiserror(15338,-1,-1,@objtypeIN,@newname)
return 0 -- sp_rename

GO

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BillPowellCommented:
Copy this into QA and run it.  Be sure you have master selected from the dropdown box.
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
After above is done, do not forget to mark it as a system function else it will remain marked as a user stored procedure (which may cause some surprise some day)...for that use...

EXEC dbo.sp_MS_marksystemobject 'dbo.sp_rename'

Hope this helps...
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

BillPowellCommented:
Good catch Racimo :)
wcotechAuthor Commented:
Thanks guys, I will be back in the office tomorrow and I'll let you know if this works.
wcotechAuthor Commented:
Guys, I tried to paste the code into QA and when I execute, I get the following errors:

Server: Msg 259, Level 16, State 2, Procedure sp_rename, Line 406
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.
Server: Msg 259, Level 16, State 1, Procedure sp_rename, Line 413
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.
Server: Msg 259, Level 16, State 1, Procedure sp_rename, Line 416
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.
Server: Msg 259, Level 16, State 1, Procedure sp_rename, Line 420
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.
Server: Msg 259, Level 16, State 1, Procedure sp_rename, Line 421
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.
Server: Msg 259, Level 16, State 1, Procedure sp_rename, Line 432
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.
Server: Msg 259, Level 16, State 1, Procedure sp_rename, Line 435
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Before you run the query creating the procedure, run this...

exec SP_CONFIGURE 'ALLOW UPDATES', 1
GO
reconfigure with override
GO
run the sp_rename create script...
mark it as a system object...

Once you are done run this
exec SP_CONFIGURE 'ALLOW UPDATES', 0
GO
reconfigure with override
GO

Hope this helps...
wcotechAuthor Commented:
ABSOLUTELY OUTSTANDING GUYS!!!!!!!

After doing what Racimo suggested, I was able to get sp_rename working again.  You guys are awesome.  I'll split the points unless anyone has any objections.
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Glad it works for you...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.