Thursday 30 May 2013

SQL Script to rename a document type table



My team came across with a situation and required to change a document type table in database. Apparently, You can change document type namespace, But it would not rename the table. Finally, my team came up with below script.

DECLARE @OldTableName VARCHAR (MAX) 
DECLARE @NewTableName VARCHAR (MAX)DECLARE @ClassID INT 
SET @OldTableName = 'OLD_TableName'SET @NewTableName = 'New_TableName'
SELECT @ClassID= ClassID   FROM dbo .CMS_Class WHERE ClassTableName = @OldTableName
UPDATE dbo. CMS_Class SET 
ClassTableName = REPLACE(ClassTableName , 'Old_' ,'New_' ), 
ClassDisplayName = REPLACE(ClassDisplayName , 'Old ' ,'New ' ), 
ClassXmlSchema = REPLACE(ClassXmlSchema , 'Old_' ,'New_' ), 
ClassName = REPLACE(ClassName , 'Old.' ,'New.' ) 
WHERE ClassID = @ClassID 
EXEC sp_rename @OldTablename, @NewTableName
It is NOT done yet. You need to go back to that document type and save it. This would trigger Kentico to regenerate the view for the document type with the new namespace. You may also remove the old view.

We ran this script on Kentico v7 instance and did not test on other versions. Please make sure you back up database before running the script.

-----------

Updated: I've tried the script on Kentico v8. It works fine.