Archive

Posts Tagged ‘rename database’

SQL Server – How to Rename Table/Column in SQL Server

04.03.2013 1 comment

To rename an object in SQL Server you can use sp_rename system stored procedure or you can do this via Object Explorer in SQL Server Management Studio.

Method 1: To rename an object using SQL Server Management Studio:

Step 1. Right Click on the object in Object Explorer and select "Rename"

image

Step 2. Specify new name and press Enter.

image

You can rename any object using object in Object Explorer.

 

Method 2: Renaming an object using sp_rename:

You can also rename using sp_rename system stored procedure.

sp_rename takes below arguments:

Parameter Description
@objname Object Name. When renaming a column you need to specify table name.column name optionally you can also prefix schema name
@newname New name for the specified object
@objtype Type of the object. You can rename below objects using sp_rename:
COLUMN
DATABASE
INDEX
OBJECT
STATISTICS
USERDATATYPE
Default value for this parameter is TABLE

Following example demonstrates how you can use sp_rename to rename table and column names:

Let’s create a Test Table to work with:

USE [SqlAndMe]

GO

 

CREATE TABLE tblProduct

(     

       ID     INT,

       pName  NVARCHAR(50)

)

GO

Now we can rename the Table Name and column names as below:

— Rename table tblProduct to Table_Products

EXEC sp_rename 'tblProduct', 'Table_Products'

GO

 

— Rename Column ID to ProductID

EXEC sp_rename 'Table_Products.ID', 'ProductID', 'COLUMN'

GO

 

— Rename Column pName to ProductName

EXEC sp_rename 'Table_Products.pName', 'ProductName', 'COLUMN'

GO

Result Set:

Caution: Changing any part of an object name could break scripts and stored procedures.

Caution: Changing any part of an object name could break scripts and stored procedures.

Caution: Changing any part of an object name could break scripts and stored procedures.

You can verify that the changes have been made by issuing a SELECT against the table using new table name. SQL Server is also nice enough to warn us that renaming an object can break scripts and stored procedures. What this means is if you have used table/column names in any script/stored procedure it will not be updated automatically, you will need to go through your scripts/stored procedures manually and update them accordingly.

Hope This Helps!

Vishal

SQL Server – Renaming database and physical database files

02.08.2011 5 comments

Sometimes we need to change the name of the database, for example, a database might have been restored using a different name. Whatever the reason may be, this can be easily done via Management Studio or T-SQL.

 

1. Rename a Database using Management Studio:

Step 1: Renaming Database:

Right Click the Database Name in Object Explorer and Select "Rename", now you can specify new name:

image image

However, this does not change the database file names:

SELECT      name, physical_name

FROM        [SqlAndMe].sys.database_files

Result Set:

name           physical_name

————   ——————-

MyDatabase     C:…MyDatabase.mdf

MyDatabase_log C:…MyDatabase_log.LDF

 

(2 row(s) affected)

 

Step 2: Renaming Files:

To change filenames, Right Click on Database in Object Explorer and Select "Properties", Then, go to "Files" Tab:

image

Here, you can change the logical filenames for DATA and LOG files.

 

2. Renaming a Database using T-SQL:

Step 1: Renaming Database:

To Rename a database using T-SQL, use below script:

USE [master]

— Set Database to Single-User Mode

ALTER DATABASE [MyDatabase] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

 

— Rename Database

ALTER DATABASE [MyDatabase] MODIFY Name = [SqlAndMe]

 

— Set Database to Multi-User Mode

ALTER DATABASE [SqlAndMe] SET  MULTI_USER WITH ROLLBACK IMMEDIATE

Result Set:

The database name 'SqlAndMe' has been set.

Now, we have renamed the database, but we still need to rename the database files.

 

Step 2: Renaming Files:

We can rename the logical name for database files using ALTER DATABASE:

— Rename Logical File Names

ALTER DATABASE [SqlAndMe]

            MODIFY FILE (NAME=N'MyDatabase', NEWNAME=N'SqlAndMe')

ALTER DATABASE [SqlAndMe]

            MODIFY FILE (NAME=N'MyDatabase_log', NEWNAME=N'SqlAndMe_log')

Result Set:

The file name 'SqlAndMe' has been set.

The file name 'SqlAndMe_log' has been set.

However, none of these two methods can change the physical database file name, you can check this using sys.database_files:

SELECT      name, physical_name

FROM        [SqlAndMe].sys.database_files

Result Set:

name          physical_name

——        —————

SqlAndMe      C:…MyDatabase.mdf

SqlAndMe_log  C:…MyDatabase_log.LDF

 

(2 row(s) affected)

 

Sometimes we also need to rename the physical database files, this cannot be done via two methods described above.

3. Renaming a Database using detach/attach:

Step 1: Detach database:

You can detach a database either using Management Studio or T-SQL, To detach a database using Management Studio, right-click on database > go to "Tasks" > "Detach", click OK to detach a database,

To detach a database using T-SQL, you can use master..sp_detach_db procedure:

— Detach Database using sp_detach_db

USE [master]

ALTER DATABASE [MyDatabase]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

 

EXEC master.dbo.sp_detach_db @dbname = N'MyDatabase'

 

Step 2: Rename Physical files:

Once the database is detached, SQL Server releases the locks on physical files, now you can rename the physical files using Windows Explorer, or command prompt:

image

 

Step 3: Attaching database with New Name:

Once, the physical files have been renamed, you can attach the database using new database and file names:

— Attach Database

USE [master]

CREATE DATABASE [SqlAndMe] ON

( FILENAME = N'C:…SqlAndMe.mdf'),

( FILENAME = N'C:…SqlAndMe_log.LDF')

 FOR ATTACH

 

— Rename Logical file names

USE [SqlAndMe]

ALTER DATABASE [SqlAndMe]

      MODIFY FILE (NAME=N'MyDatabase', NEWNAME=N'SqlAndMe')

ALTER DATABASE [SqlAndMe]

      MODIFY FILE (NAME=N'MyDatabase_log', NEWNAME=N'SqlAndMe_log')

SELECT      name, physical_name

FROM        [SqlAndMe].sys.database_files

Result Set:

name          physical_name

————  —————–

SqlAndMe      C:…SqlAndMe.mdf

SqlAndMe_log  C:…SqlAndMe_log.LDF

 

(2 row(s) affected)

 

Hope This Helps!

Vishal