Tuesday, August 30, 2011

Renaming a table or storedprocedure or a column

Rename any object in sql server 2005

if u want to rename any object with command then you have to use in-built system stored procedure which is sp_rename

for renaming the column of a table you have to write the following statement

sp_RENAME 'dbo.tablename.oldname', 'NameChange' , 'COLUMN'

but for the storedprocedure and table you dont have to mention the third parameter

sp_rename 'old_table_name', 'new_table_name'


You can change the name of an object or data type in the current database only. The names of most system data types and system objects cannot be changed.

When you rename a view, information about the view is updated in the sysobjects table. When you rename a stored procedure, information about the procedure is changed in the sysobjects table.

sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the primary key is also automatically renamed by sp_rename.

Important After renaming stored procedures and views, flush the procedure cache to ensure all dependent stored procedures and views are recompiled.

Renaming a stored procedure, view or trigger will not change the name of the corresponding object name in the syscomments table. This may result in problems generating a script for the object as the old name will be inserted from the syscomments table into the CREATE statement. For best results, do not rename these object types. Instead, drop and re-create the object by its new name.

No comments:

Post a Comment