Archive

Posts Tagged ‘sp_rename’

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 – Moving table with LOB data to different file group

19.10.2011 3 comments

Last week, i posted about moving tables to different file groups by recreating indexes. However, this method does not *work* when there is LOB data involved.

When we recreate indexes on a table containing LOB data, the LOB data pages are not moved to destination file group. This can be demonstrated with below example:

USE [master]

GO

 

/* Create a database with 2 file groups */

CREATE DATABASE [TestDB]

ON PRIMARY

(     NAME = N'TestDB',

      FILENAME = N'C:DatabaseDataTestDB.mdf' ),

FILEGROUP [SecondaryFG]

(     NAME = N'TestDB_2',

      FILENAME = N'C:DatabaseDataTestDB_2.ndf' )

LOG ON

(     NAME = N'TestDB_log',

      FILENAME = N'C:DatabaseLogTestDB_log.ldf' )

GO

 

/* Create a table on [PRIMARY] file group */

CREATE TABLE T2

(     [ID] INT IDENTITY(1,1),

      [Name] VARCHAR(MAX),

      [Status] SMALLINT

)

GO

 

/* store LOB data out of row */

EXEC sp_tableoption 'dbo.T2', 'large value types out of row', 1

GO

 

CREATE UNIQUE CLUSTERED INDEX [IX_CL_T2_ID] ON [dbo].[T2]

(     [ID] ASC

) GO

 

CREATE NONCLUSTERED INDEX [IX_NCL_T2_Status] ON [dbo].[T2]

(     [Status] ASC

) GO

 

INSERT INTO T2([Name], [Status])

VALUES ('LOB data', 1)

GO

 

SELECT      OBJ.name, OBJ.type, FG.name

FROM        sys.indexes IDX

INNER JOIN  sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

INNER JOIN  sys.objects OBJ ON IDX.object_id = OBJ.object_id

WHERE       OBJ.type = 'U'

GO

 

DBCC IND('TestDB', 'T2', 1)

GO

 

SELECT      DF.file_id, F.name

FROM        sys.filegroups F

INNER JOIN  sys.database_files DF ON DF.data_space_id = F.data_space_id

GO

Result Set:

name   type   name

T2     U      PRIMARY

T2     U      PRIMARY

 

PageFID PagePID     IAMFID IAMPID      ObjectID    …  iam_chain_type

1       158         NULL   NULL        2105058535  …  In-row data

1       157         1      158         2105058535  …  In-row data

1       156         NULL   NULL        2105058535  …  LOB data

1       155         1      156         2105058535  …  LOB data

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

file_id     name

1           PRIMARY

3           SecondaryFG

From the result set we can see that:

1. Both indexes are on [PRIMARY] file group.

2. All allocated pages are from file with file_id = 1

3. File with file_id = 1 is in [PRIMARY] file group.

Now let’s try to move the table to [SecondaryFG] by rebuilding indexes:

USE [TestDB]

GO

 

/* Re-create indexes on [SecondaryFG] */

CREATE UNIQUE CLUSTERED INDEX [IX_CL_T2_ID] ON [dbo].[T2]

(

      [ID] ASC

) WITH (DROP_EXISTING = ON)

ON [SecondaryFG]

GO

 

CREATE NONCLUSTERED INDEX [IX_NCL_T2_Status] ON [dbo].[T2]

(

      [Status] ASC

) WITH (DROP_EXISTING = ON)

ON [SecondaryFG]

GO

 

/* Indexes have moved to [SecondaryFG] */

SELECT      OBJ.name, OBJ.type, FG.name

FROM        sys.indexes IDX

INNER JOIN  sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

INNER JOIN  sys.objects OBJ ON IDX.object_id = OBJ.object_id

WHERE       OBJ.type = 'U'

GO

 

/* But not LOB pages,

PageFID = FileID of file from which the page is allocated */

DBCC IND('TestDB', 'T2', 1)

GO

 

/* File ID 1 is from [PRIMARY] file group

File ID 3 is from [SecondaryFG] file group */

SELECT      DF.file_id, F.name

FROM        sys.filegroups F

INNER JOIN  sys.database_files DF ON DF.data_space_id = F.data_space_id

GO

Result Set:

name   type   name

T2     U      SecondaryFG

T2     U      SecondaryFG

 

PageFID PagePID     IAMFID IAMPID      ObjectID    … iam_chain_type

3       9           NULL   NULL        2105058535  … In-row data

3       8           3      9           2105058535  … In-row data

1       156         NULL   NULL        2105058535  … LOB data

1       155         1      156         2105058535  … LOB data

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

file_id     name

1           PRIMARY

3           SecondaryFG

From this result set we can see that:

1. Rebuilding indexes have moved them to [SecondaryFG] file group.

2. In-row data pages are now allocated from File with file_id = 3

3. LOB data pages have not moved, and are still in file with file_id = 1

4. File with file_id = 1 is in [PRIMARY] file group, and File with file_id = 3 is in
[SecondaryFG] file group.

So how do we move the LOB data pages? You need to drop and recreate the table if you need to move LOB data. The general steps to follow are:

1. Create a new table with same structure on destination file group i.e. [SecondaryFG]

2. Import rows from old table

3. Delete old table

4. Rename new table

For example:

USE [TestDB]

GO

 

/* Create a new table on new file group */

CREATE TABLE Tmp_T2

(

      [ID]        INT    IDENTITY(1,1),

      [Name]      VARCHAR(MAX),

      [Status]    SMALLINT

) ON [SecondaryFG]

GO

 

EXEC sp_tableoption 'dbo.Tmp_T2', 'large value types out of row', 1

GO

 

CREATE UNIQUE CLUSTERED INDEX [IX_CL_Tmp_T2_ID] ON [dbo].[Tmp_T2]

(

      [ID] ASC

) ON [SecondaryFG]

GO

 

CREATE NONCLUSTERED INDEX [IX_NCL_Tmp_T2_Status] ON [dbo].[Tmp_T2]

(

      [Status] ASC

) ON [SecondaryFG]

GO

 

/* Import data from old table */

SET IDENTITY_INSERT Tmp_T2 ON

INSERT INTO Tmp_T2([ID], [Name], [Status])

SELECT * FROM T2

SET IDENTITY_INSERT Tmp_T2 OFF

GO

 

/* Delete old table */

DROP TABLE T2

 

/* Rename the temporary table */

EXEC sp_rename 'dbo.Tmp_T2', 'T2'

GO

 

/* Rename indexes, not necessary but useful when they have been used in hints. */

EXEC sp_rename    N'dbo.T2.IX_CL_Tmp_T2_ID',

                  N'IX_CL_T2_ID',

                  N'INDEX'

GO

 

EXEC sp_rename    N'dbo.T2.IX_NCL_Tmp_T2_Status',

                  N'IX_NCL_T2_Status',

                  N'INDEX'

GO

This script implements the steps described above:

1. It creates a new table/indexes dbo.Tmp_T2 on [SecondaryFG] with the same structure
as dbo.T2

2. Imports all rows from dbo.T2 into dbo.Tmp_T2

3. Deletes dbo.T2

4. Renames dbo.Tmp_T2 to dbo.T2. It also renames the indexes as required.

You can check the page allocations using the queries we used earlier:

USE [TestDB]

GO

 

/* Indexes are on [SecondaryFG] .. OK. */

SELECT      OBJ.name, OBJ.type, FG.name

FROM        sys.indexes IDX

INNER JOIN  sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

INNER JOIN  sys.objects OBJ ON IDX.object_id = OBJ.object_id

WHERE       OBJ.type = 'U'

GO

 

/* LOB Pages are on File ID = 3 */

DBCC IND('TestDB', 'T2', 1)

GO

 

/* File ID 3 is in [SecondaryFG] .. OK. */

SELECT      DF.file_id, F.name

FROM        sys.filegroups F

INNER JOIN  sys.database_files DF ON DF.data_space_id = F.data_space_id

GO

Result Set:

name   type   name

T2     U      SecondaryFG

T2     U      SecondaryFG

 

PageFID PagePID     IAMFID IAMPID      ObjectID    …    iam_chain_type

3       11          NULL   NULL        2121058592  …    In-row data

3       10          3      11          2121058592  …    In-row data

3       9           NULL   NULL        2121058592  …    LOB data

3       8           3      9           2121058592  …    LOB data

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

file_id     name

1           PRIMARY

3           SecondaryFG

From this result we can see that all indexes and LOB data pages have moved to file with file_id = 3, which is in [SecondaryFG].

Hope This Helps!

Vishal