SQL Server Performance Forum – Threads Archive
sp_rename a table and keys and constraints
Hi I have a question hopefully someone can have a look at. I need to create a new table (with identity), and use it to replace another table in the database. I am slightly confused with indexes, constraints, etc. So ‘tableANew’ is to replace ‘tableA’. I create tableANew to be exactly the same as tableA except for the identity part. Although I have to give tableANew the PK index name of PK_tableANew because tableA already has PK_tableA. So when I use sp_rename to rename the tables tableA becomes tableAOLD, and tableANew becomes tableA. Now here is where the problem is, tableA still has it’s PK index called PK_tableANew and tableAOLD has the PK index of PK_tableA. I hope you were able to keep up with that, and I haven’t made your brain explode with my dodgy explaination of the situation. Is there a way to rename the keys/constraints when renaming the table? Am I worrying over nothing? Will my views, stored procedures, etc still work with the new table even though the indexes have different names?? Any help would be greatly appreciated.SQL Server Books online states
quote:
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 constraint is also automatically renamed by sp_rename. sp_rename can be used to rename primary and secondary XML indexes. Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name. Renaming an object such as a table or column will not automatically rename references to that object. You must modify any objects that reference the renamed object manually. For example, if you rename a table column and that column is referenced in a trigger, you must modify the trigger to reflect the new column name. Use sys.sql_dependencies to list dependencies on the object before renaming it.
That is why Books online is very useful in this case to checkover.
Satya SKJsp_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 constraint is also automatically renamed by sp_rename. sp_rename can be used to rename primary and secondary XML indexes. Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name. Renaming an object such as a table or column will not automatically rename references to that object. You must modify any objects that reference the renamed object manually. For example, if you rename a table column and that column is referenced in a trigger, you must modify the trigger to reflect the new column name. Use sys.sql_dependencies to list dependencies on the object before renaming it.
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
since Index,Constraints(FK,PK,Check) are seperate objects itself and how will system know that what should be the new name for these object to do it automatically ? How will system decide u r naming convension… so u have to rename all these object explicitly. it is clearly given in BOL that SP_Rename can be used to rename all these objects…
Madhu
Also check if your foreign keys are still referring to the old table? You might have to drop/recreate the FK. ***********************
Dinakar Nethi
SQL Server MVP
***********************
Imagine I created the following table which replaces tblKey, the difference between the 2 tables is the test has identity. Notice how the PK has to be called something other than PK_tblKey, as that already exists in tblKey. CREATE TABLE [dbo].[tblKeyTEST](
[intKeyID] [int] NOT NULL IDENTITY(1,1),
[intOriginalKeyID] [int] NULL,
[intPersonID] [int] NULL,
CONSTRAINT [PK_tblKeyTEST] PRIMARY KEY CLUSTERED
(
[intKeyID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] Now, when I do a ‘sp_rename tblKeyTEST, tblKey’, it will not rename the PK. How do I do this, and if I don’t do this, will it cause a problem?
u should rename the PrimaryConstraint also sp_rename [PK_tblKeyTEST] ,[PK_tblKey] Madhu
True and I believe you have to take time thru BOL to understand on SP_rename. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>