Remove a Pre-Installed Language

SQL Script

Step 1: Locate the variable @LanguageId in the script below and set the value to the Id of the language that has to be removed. The language id's can be found in the Languages table in the Sana Commerce database. This script can also be downloaded as a sql file.

CREATE FUNCTION remfld19823
(
 @xml XML,
 @field nvarchar(50)
)
RETURNS nvarchar(MAX)
AS
BEGIN
 DECLARE @value nvarchar(MAX);
 SELECT @value = N'<FieldsDictionary>'+CONVERT(nvarchar(max),@xml.query('
        for $v in /FieldsDictionary/*[@name != sql:variable("@field")]
        return $v'))+N'</FieldsDictionary>';
 RETURN @value;
END
GO
CREATE PROCEDURE remove_localized_field_21122011
(
 @fieldName nvarchar(255)
)
AS
BEGIN
 UPDATE Products SET Fields.modify('delete //field[@name = sql:variable("@fieldName")]');
 UPDATE ProductCategories SET Fields.modify('delete //field[@name = sql:variable("@fieldName")]');
 UPDATE VariantComponentGroups SET Fields.modify('delete //field[@name = sql:variable("@fieldName")]');
 UPDATE VariantComponents SET Fields.modify('delete //field[@name = sql:variable("@fieldName")]');
END
GO
BEGIN TRANSACTION
 BEGIN TRY
  DECLARE @languageId int;
  SET @languageId = [languageId]; /* the Locale ID of the language which should be removed */
  IF ((SELECT count(id) FROM Websites WHERE DefaultLanguageId = @languageId) > 0)
  BEGIN
   raiserror('ERROR: language %d is used as default for website!', 11, 1, @languageId)
  END
  --Delete NavigationItems
  DELETE FROM dbo.NavigationItems
  WHERE NavigationId IN (SELECT Id 
         FROM dbo.Navigations 
         WHERE LanguageId = @languageId);
  --Delete Navigations
  DELETE FROM dbo.Navigations
  WHERE LanguageId = @languageId;
  --Delete MobileNavigationItems
  DELETE FROM dbo.MobileNavigationItems
  WHERE NavigationId IN (SELECT Id 
         FROM dbo.MobileNavigations 
         WHERE LanguageId = @languageId);
    --Delete MobileNavigations
  DELETE FROM dbo.MobileNavigations
  WHERE LanguageId = @languageId;
    --Delete ProductEnrichments 
  DELETE FROM ProductEnrichments 
  WHERE LanguageId = @languageId;
  --Delete ChannelReferences
  DELETE FROM ChannelReferences
  WHERE (WebsiteLanguageId IN (SELECT Id
            FROM WebsiteLanguages
            WHERE LanguageId = @languageId))
  --Delete ContentLandingPages
  DELETE FROM ContentLandingPages
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = ContentLandingPages.Id) = 0)
  --Delete WebsiteLanguages
  DELETE FROM WebsiteLanguages
  WHERE LanguageId = @languageId
  --Delete SanaTexts
  DELETE FROM SanaTexts
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = SanaTexts.Id) = 0)
  --Delete NewsItems
  DELETE FROM NewsItems
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = NewsItems.Id) = 0)
  --Delete ContentPages
  DELETE FROM ContentPages
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = ContentPages.Id) = 0)
  --Delete ProductLandingPages
  DELETE FROM ProductLandingPages
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = ProductLandingPages.Id) = 0)
  --Delete ProductListPages
  DElETE FROM ProductListPages
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = ProductListPages.Id) = 0)
  --Delete MailTemplates
  DELETE FROM MailTemplates
  WHERE ((SELECT count(*) FROM ChannelReferences WHERE ChannelReferences.ItemId = MailTemplates.Id) = 0)
  -- remove localized fields
  DECLARE @stringLanguageId nvarchar(255)
  SET @stringLanguageId = CAST (@languageId as nvarchar(255));
  DECLARE @fieldName nvarchar(255)
  SET @fieldName = 'Description_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'Title_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'Name_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'HorizontalText_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'VerticalText_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  SET @fieldName = 'VariantDescription_' + @stringLanguageId;
  EXEC remove_localized_field_21122011 @fieldName
  ------------
  --Delete Translations
  DELETE FROM Translations
  WHERE LanguageId = @languageId
  --Delete LanguagePreference from ShopAccount
  UPDATE dbo.ShopAccounts SET Fields = dbo.remfld19823(Fields,'LanguagePreference')
  WHERE CAST( Fields
    .query('
     for $descr in /FieldsDictionary/*[@name = "LanguagePreference"]
     return string($descr)
    ')
    .value('/', 'nvarchar(max)') AS xml)
    .value('/', 'nvarchar(max)') = @languageId
  --Delete Languages
  DELETE FROM Languages 
  WHERE Id = @languageId;
 END TRY
 BEGIN CATCH
  PRINT ERROR_MESSAGE() + ' Transaction rolled back.'
  IF @@TRANCOUNT > 0
   ROLLBACK TRANSACTION
 END CATCH
GO
IF @@TRANCOUNT > 0
 COMMIT TRANSACTION
GO
IF EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME = 'remfld19823' 
    AND ROUTINE_TYPE = 'FUNCTION'
) 
    DROP FUNCTION remfld19823
GO
GO
IF EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME = 'remove_localized_field_21122011' 
    AND ROUTINE_TYPE = 'PROCEDURE'
) 
    DROP PROCEDURE remove_localized_field_21122011
GO