Rank: Administration
Groups: Administration
Joined: 10/8/2008(UTC) Posts: 190  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
DECLARE @SPNAME nvarchar(max)
DECLARE sp_cursor CURSOR FOR
SELECT
tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
AND tc.CONSTRAINT_NAME LIKE '%' + @OldCid + '%'
ORDER BY
KU.TABLE_NAME
,KU.ORDINAL_POSITION
open sp_cursor
FETCH NEXT FROM sp_cursor
INTO @SPNAME
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @oldSPNAME nvarchar(max) = 'dbo.' + @SPNAME
DECLARE @newSPNAME nvarchar(max) = REPLACE(@SPNAME,@OldCid,@NewCid)
exec sp_rename @oldSPNAME , @newSPNAME;
FETCH NEXT FROM sp_cursor
INTO @SPNAME
END
CLOSE sp_cursor;
DEALLOCATE sp_cursor;
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 Wednesday, October 21, 2020 1:10:05 PM(UTC)
| Reason: Not specified
|
