Friday, June 5, 2015

Rename SQL Server Database, Table, Column, Index or Constraint

I can't count the number of times over the years that I have referenced BOL, or done a quick google, checking the syntax for my renames.  Not just constraints or indexes, but tables and columns, too.  Even databases!  In this post I am going to to give you several useful examples, compatible with all versions from v2000 to v2014. 

Side note;  In most cases you can perform these renames in the GUI, but I am a huge advocate of NOT doing renames through the GUI. The performance goes to the tubes very quickly, and you won't have near enough control over the system when these manipulations are occurring.  Don't use the GUI.  Learn and master the tSQL. You won't regret it.

    #1  Rename Database
    EXEC sp_renamedb @dbname = 'OldDatabaseName', @newname = 'NewDatabaseName' 

    v2005 - v2014     
    ALTER DATABASE OldDatabaseName MODIFY NAME = NewDatabaseName

    #2  Rename Table
    EXEC sp_rename 
          @objname = 'OldTableName'
          @newname = 'NewName'
          @objtype = 'OBJECT'

    #3  Rename Index
    EXEC sp_rename 
          @objname = 'TableName.IndexName',
          @newname = 'NewName',
          @objtype = 'INDEX'

     #4  Rename Column
     EXEC sp_rename 
           @objname = 'TableName.ColumnName',
           @newname = 'NewName',
           @objtype = 'COLUMN'

     #5  Rename Constraint
     EXEC sp_rename 
           @objname = 'ConstraintName',
           @newname 'NewConstraintName',
           @objtype = 'OBJECT'

Remember, #4 only renames the column! It does not account for any referencing objects. For example, if you rename a table that is being called in a procedure, you will need to revise the procedure to reference the new table name. Dropping and re-creating the referencing objects is the recommended way to do this.

No comments:

Post a Comment