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.