Systems Engineering and RDBMS

Re-naming objects in SQL Server

Posted by decipherinfosys on August 11, 2007

In one of our previous blog entry, we had discussed about re-naming tables/ columns in Oracle. Today, we will discuss how we can rename objects in SQLServer. MS SQLServer has sp_rename stored procedure which can be used to rename user created objects. This includes table, column, index, constraints, views, stored procedures, functions and user defined data types. We will create a table first for this post. You can execute all the queries either from query analyzer(SQL 2000) or from management studio (SQL 2005).

CREATE TABLE dbo.NEW
(
NEW_ID INT NOT NULL,
NAME VARCHAR(10),
CONSTRAINT PK_NEW PRIMARY KEY(NEW_ID)
)
GO

We will also create the following dependent objects to find out what changes we need to make when we alter or rename the table.

CREATE PROCEDURE dbo.TEST_PROC
@v_test_id INT
AS
BEGIN
SELECT @v_test_id = count(NEW_ID) FROM dbo.NEW
END
GO

CREATE VIEW dbo.TEST_VIEW
AS
SELECT NAME
FROM dbo.NEW
GO

Following command is used to rename a table:

exec sp_rename ‘NEW’,’NEW_TEST’

Column can be renamed in slightly different way but using same stored procedure:

exec sp_rename ‘NEW_TEST.NEW_ID’,’NEW_TEST_ID’,’COLUMN’

In order to rename the index, we have to use following syntax. Let’s create the index first:

CREATE INDEX NEW_IND_1 ON NEW_TEST(NAME)
GO

exec sp_rename ‘NEW_TEST.NEW_IND_1′,’NEW_TEST_IND_1′,’INDEX’

In a similar way, we can rename constraints, views, stored procedure and functions. To rename them, we need to use ‘OBJECT’ as an object_type parameter value. Following is an example of renaming primary key constraint:

exec sp_rename ‘PK_NEW’,’PK_NEW_TEST’,’OBJECT’

Unlike Oracle, when primary key or unique key constraints are renamed, associated index is also automatically renamed by the sp_rename stored procedure. It also holds true for reverse scenario. If index is tied to a primary key constraint, renaming index will also rename the constraint. Also, whenever an object is renamed, the following message will be displayed. It is just a caution message and reminds dba/developer to change the corresponding scripts/ stored procedures/ functions etc.

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

SQL Server also invalidates the dependent objects (view etc.) when table is renamed. One way to find out dependent objects is to go after sysdepends or the sys.sql_dependencies system objects. For the purpose of this post, we can make use of sysdepends or sys.sql_dependencies however, that is not a reliable way of checking for dependencies between objects. We will cover that in a future blog post as to why that is not a reliable way of checking for object dependencies in SQL Server and how one can do a fool-proof dependency search.

Following is the query and output.

SELECT DISTINCT
CASE
WHEN so.type = ‘P’ THEN ‘Procedure’
WHEN so.type =’TR’ THEN ‘Trigger’
WHEN so.type = ‘FN’ THEN ‘Scalar Function’
WHEN so.type = ‘TF’ THEN ‘Table Function’
WHEN so.type = ‘V’ THEN ‘View’
ELSE NULL
END as Object_Type,
so.name as Object_Name
FROM sysobjects so
INNER JOIN sysdepends sd
ON so.id = sd.id
WHERE sd.depid = OBJECT_ID(‘NEW_TEST’)
GO

Object_Type Object_Name
———— ————
Procedure TEST_PROC
View TEST_VIEW

When column name is renamed, we can execute above shown SQL to get all the dependent objects of the table, but it will show all the objects irrespective of column being renamed is used or not. In such cases, we can go against syscomments table rather than sysdepends table. Following SQL will show us that in which all places, column being renamed is referenced.

SELECT DISTINCT
CASE
WHEN so.type = ‘P’ THEN ‘Procedure’
WHEN so.type =’TR’ THEN ‘Trigger’
WHEN so.type = ‘FN’ THEN ‘Scalar Function’
WHEN so.type = ‘TF’ THEN ‘Table Function’
WHEN so.type = ‘V’ THEN ‘View’
ELSE NULL
END as Object_Type,
so.name as Object_Name
FROM sysobjects so
INNER JOIN syscomments sc
ON so.id = sc.id
WHERE sc.text LIKE ‘%NEW_ID%’
GO

Output is as shown below.

Object_Type Object_Name
———— ————
Procedure TEST_PROC

2 Responses to “Re-naming objects in SQL Server”

  1. […] by decipherinfosys on August 21st, 2007 In our previous posts, we had covered re-naming of objects in SQL Server and re-naming of tables and columns in Oracle. In this post, we will cover how one can re-name a […]

  2. […] by decipherinfosys on October 24th, 2007 In one of our previous blog post, we had covered how to rename objects in SQL Server. Today we will cover how we can rename the […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: