/************************************************************************************************************************
 *                                     sp_rename_logical_filename                                                       *
 *                                                                                                                      *
 *   This stored procedure was created to allow one to change logical file names of datbase files in a  7.0 database.   *
 *   After doing a restore with the move option, one can change the physical file path and physcal file name, but not   *
 *   the logical name.  This posed a problem because we (GMAC) have internal standards for database names and for       *
 *   logical and physical file names.                                                                                   *
 *                                                                                                                      *
 *   This stored procedure does change the configuration paramater 'allow updates' so it can directly update the        *
 *   sysfiles1 table in the database in question.  I thought it was very interesting that I was actually able to make   *
 *   this change from within a storeded procedure.   By the way,  I've never run this on a production server ... yet.   *
 *                                                                                                                      *
 *   Usage:   use database in question                                                                                  *
 *            go                                                                                                        *
 *            exec sp_rename_logical_filename current_logical_name, new_name                                            *
 *                                                                                                                      *
 *   You will have to execute this stored procedure for every logical name you wish to change in the database.  It was  *
 *   interesting that even with 'allow updates' set,  I could not update the sysfiles table ... so I settled for        *
 *   updating sysfiles1 instead.    From within the database,  I used sp_helpfile and sp_helpdb to verify the changes.  *
 *                                                                                                                      *
 *   I'm looking for ideas to improve this stored procedure ... this is just a first run.   Please feel free to         *
 *   contact me with any ideas or constructive criticism.     You could go nuts and add checks for all of the db        *
 *   options and maybe only allow this if the db is in single user mode ?  What if the db is marked suspect ?  To be    *
 *   more official, we could have more raiserrors and less prints.                                                      *
 *                                                                                                                      *
 *   Original Author:   Joe Toscano   6/1/2000   (jtoscano@ix.netcom.com)                                               *
 *   Revision History:                                                                                                  *
 *                                                                                                                      *
 ************************************************************************************************************************/

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

create proc sp_rename_logical_filename
@current_name varchar(64)= null,
@new_name varchar(64)= null
as

  declare @msg varchar(200),
          @cmd varchar(240),
          @err int

  set nocount on

  if @current_name is null or @new_name is null 
  begin
        select @msg = 'Usage:  exec sp_rename_logical_filename ' + char(39) 
                      + 'current_name' + char(39) + ',' + char(39) + 'new_name' + char(39)
        print @msg
        print ''
        print '(While in the database in question)'
        return (1)
  end

  if db_name() ='master'
  begin
        print 'You must be in the database that contains the logical filenames you wish to modify ... not in master'
        return (1)
  end

/*
**  Only the SA can execute sp_configure to change config parameters
*/
if (not is_srvrolemember('serveradmin') = 1)
   begin
      print 'In order to execute this stored procedure, you must be permitted to directly update system tables'
      raiserror(15125,-1,-1)
      return (1)
   end
/*
 * We are not going to let you  be in a transaction ... at least for now.
 */

if @@trancount > 0
begin
      raiserror(15002,-1,-1, 'sp_rename_logical_filename')
      print 'Please perform a commit or rollback and then execute this stored procedure'
      return(1)
end

/*
 * Let's make sure the logical file name you specify exists
 */

if not exists (select 1 from sysfiles1 where name = @current_name)
begin
      select @msg = 'Logical File Name:  ' + @current_name + ',  not found in ' + db_name() + '.  Please check the name'
      print @msg
      return (1)
end

/*
 *  Change the option to allow updates to system tables ... this is a server wide setting ... that's a bit scarry!
 */

      select @cmd = 'exec sp_configure '+  char(39) + 'allow updates' + char(39) + ', 1' + char(13)
      exec (@cmd)

      select @cmd = 'reconfigure WITH override'
      exec (@cmd)

      select @err = 0
 
      update sysfiles1
      set name = @new_name
      where name = @current_name

      if @@error <> 0
       begin
            print 'sp_rename_logical_filename failed:  Unable to update sysfiles1 table with new logical name'
            select @err = 1
       end
  
              
 /*
  *  Regardless of the status of our updates ...  change the option to allow updates to system tables back to 0
  */

     select @cmd = 'exec sp_configure '+  char(39) + 'allow updates' + char(39) + ', 0' + char(13)
     exec (@cmd)

     select @cmd = 'reconfigure WITH override'
     exec (@cmd)


     select @msg =  'sp_rename_logical_filename:  Changed filename: ' + @current_name + ' to :  ' + @new_name
     print @msg

     set nocount off

return @err

grant exec on sp_rename_logical_filename to public