Rank: Administration
Groups: Administration
Joined: 10/8/2008(UTC) Posts: 188  Was thanked: 1 time(s) in 1 post(s)
|
Sometimes for some reason you want to change the CID of a website. I made some sql script which can make this happen for you. however, it worked on the site I tested it on, SEO isn't included jet, I might at this at a later time. And also stuff which isn't used in this site isn't added. Here is My query changing CID ES to EYESALE Those parameters is set, but in the query you have to change some tablenames: enjoy;) Code:BEGIN TRANSACTION
BEGIN TRY
Declare @NewCid nvarchar(100) = 'Eyesale'
Declare @OldCid nvarchar(100) = 'ES'
if (Select COUNT(*) from pbw_cid where Cid = @NewCid) = 0
begin
Update pbw_cid
Set Cid= @NewCid
,UpdateWebConfig = 'Ja'
,UpdateIIS = 'Ja'
where Cid = @OldCid
Update [PBW_Cid_Adm_Toegang]
Set Cid= @NewCid
where Cid = @OldCid
Update [PBW_Packages_Jobs]
Set Cid= @NewCid
where Cid = @OldCid
Update [PBW_Gebruikers]
Set Cid= @NewCid
where Cid = @OldCid
Update [PBW_CID_Websites]
Set Cid= @NewCid
where Cid = @OldCid
Update [PBW_Support_Meldingen]
Set Cid= @NewCid
where Cid = @OldCid
update [ES_CONFIG]
set WAARDE = Replace(Cast(waarde as nvarchar(max)),@OldCid + '_',@NewCid + '_')
update [ES_CONFIG]
set WAARDE = Replace(Cast(waarde as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
update [ES_FORMULIEREN]
set [VERVOLGURL] = Replace(Cast([VERVOLGURL] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
update [ES_MENU]
set [QUERYOVERRULE] = Replace(Cast([QUERYOVERRULE] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
,[QUERYSTRING] = Replace(Cast([QUERYSTRING] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
,[ORDERBYOVERRULE] = Replace(Cast([ORDERBYOVERRULE] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
,[ORDERBYSTRING] = Replace(Cast([ORDERBYSTRING] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
update [ES_TEKSTEN]
set [TEKST] = Replace(Cast([TEKST] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
update [ES_VIEW]
set [TEKST] = Replace(Cast([TEKST] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
,[ZOEKLAYOUT] = Replace(Cast([ZOEKLAYOUT] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
,[KOPLAYOUT] = Replace(Cast([KOPLAYOUT] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
,[VOETLAYOUT] = Replace(Cast([VOETLAYOUT] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
,[SUBKOPLAYOUT] = Replace(Cast([SUBKOPLAYOUT] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
,[SUBVOETLAYOUT] = Replace(Cast([SUBVOETLAYOUT] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
,[TEKSTEVEN] = Replace(Cast([TEKSTEVEN] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
,[SORTERING] = Replace(Cast([SORTERING] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
,[SELECTIE] = Replace(Cast([SELECTIE] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
Update [ES_menu]
SET [TEKST] = Replace(Cast([TEKST] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
Where [Type] = 'EXTERN'
update [ES_TABEL_VELDEN]
set KOPPELQUERY = REPLACE(KOPPELQUERY, @OldCid + 'U_',@NewCid + 'U_')
update [ES_SEO]
set [DestinationUrlTemplate] = Replace(Cast([DestinationUrlTemplate] as nvarchar(max)),@OldCid + 'U_',@NewCid + 'U_')
DECLARE @TABELNAAM varchar(500)
DECLARE table_cursor CURSOR FOR
SELECT [TABELNAAM] FROM ES_TABEL
OPEN table_cursor
FETCH NEXT FROM table_cursor
into @TABELNAAM
WHILE @@FETCH_STATUS = 0
BEGIN
declare @oldtablename nvarchar(max)= @OldCid + 'U_' + @TABELNAAM
declare @Newtablename nvarchar(max)= @NewCid + 'U_' + @TABELNAAM
exec sp_rename @oldtablename, @Newtablename
FETCH NEXT FROM table_cursor
into @TABELNAAM
END
CLOSE table_cursor
DEALLOCATE table_cursor
declare @oldtablenameSystem nvarchar(max)= @OldCid + '_CONFIG'
declare @NewtablenameSystem nvarchar(max)= @NewCid + '_CONFIG'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_DOWNLOAD'
set @NewtablenameSystem = @NewCid + '_DOWNLOAD'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_FORMULIEREN'
set @NewtablenameSystem = @NewCid + '_FORMULIEREN'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_FOTO'
set @NewtablenameSystem = @NewCid + '_FOTO'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_GROEPEN'
set @NewtablenameSystem = @NewCid + '_GROEPEN'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_MAILING'
set @NewtablenameSystem = @NewCid + '_MAILING'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_MENU'
set @NewtablenameSystem = @NewCid + '_MENU'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_PMatrix'
set @NewtablenameSystem = @NewCid + '_PMatrix'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[' + @oldtablenameSystem + ']') AND type in (N'U'))
begin
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
end
set @oldtablenameSystem = @OldCid + '_SEO'
set @NewtablenameSystem = @NewCid + '_SEO'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_SEOHISTORY'
set @NewtablenameSystem = @NewCid + '_SEOHISTORY'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_TABEL'
set @NewtablenameSystem = @NewCid + '_TABEL'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_TABEL_VELDEN'
set @NewtablenameSystem = @NewCid + '_TABEL_VELDEN'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_TEKSTEN'
set @NewtablenameSystem = @NewCid + '_TEKSTEN'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_TOEGANG'
set @NewtablenameSystem = @NewCid + '_TOEGANG'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_VIEW'
set @NewtablenameSystem = @NewCid + '_VIEW'
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
set @oldtablenameSystem = @OldCid + '_TABEL_INDEX'
set @NewtablenameSystem = @NewCid + '_TABEL_INDEX'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[' + @oldtablenameSystem + ']') AND type in (N'U'))
begin
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
end
set @oldtablenameSystem = @OldCid + '_TABEL_INDEX_VELD'
set @NewtablenameSystem = @NewCid + '_TABEL_INDEX_VELD'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[' + @oldtablenameSystem + ']') AND type in (N'U'))
begin
exec sp_rename @oldtablenameSystem, @NewtablenameSystem
end
end
COMMIT TRANSACTION
select 'success'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
END CATCH
EDIT: 1. for the user-tables you need a right trim because else you get black spaces in your tablename 2. I think it's better to uppercase the New CID before starting I might will update the sql script later but first I need to have a CID to rename so I can test it. Edited by user Tuesday, May 29, 2018 9:03:49 AM(UTC)
| Reason: Not specified
|
