Публикация

Вопрос

1) пользователи не имеют доступа на операцию
2) все справочники унаследованы от объекта "Базовый справочник"
3) при вводе текста в справочное поле пользователи видят контрол быстрого создания записей (см скрин), хотя фактически не имеют прав на редактирование справочников (читай на создание записей в справочниках)
4) по нашему представлению и, главное, по представлению клиента, пользователи НЕ должны видеть контрол быстрого создания записей, когда у них нет доступа на эту операцию

Вопрос: 1) как сделать так, чтобы пользователи видели контрол быстрого создания записей в справочном поле для справочников только тогда, когда имеют доступ на операцию CanManageLookups?

У пользователей, не имеющих прав на редактирование справочников (CanManageLookups) при вводе текста в справочное поле появляется строка быстрого создания записи, не смотря на то, что они не фактически не могут это сделать

Ответ

Данная настройка только ограничивает доступ к разделу «Справочники» (https://academy.terrasoft.ru/documents/sales-commerce/7-9/razdel-spravochniki#HT_lookups) и к справочникам, которые наследуются от объекта «Базовый справочник» (в объекте происходит проверка на данную операцию, см. «Файлы»).

Если ограничить права на данную операцию, то в результате отобразиться сообщение - Текущий пользователь не имеет прав на запуск администрируемой операции с кодом "CanManageLookups".

Если справочник наследуются от объекта «Базовый объект», то права распределяются на уровне объекте, т.е. если у пользователя есть доступ к разделу, то он может создавать новые значение. Если необходимо ограничить права, то это необходимо осуществлять через «Права доступа на объекты».

Если Вам необходимо, то как вариант на странице редактирования (например, для «AccountPageV2») добавить метод:

getPreventQuickAddSchemaNames: function() {
    return [
        "SysAdminUnit""VwSysSchemaInfo""VwQueueSysProcess""City""Country""AccountIndustry"
    ];
},

И перечислить в этом методе объекты, в которых необходимо убрать возможность отображения быстрого добавления записи (например «AccountIndustry» или «City»).

Наглядный скриншот:

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Вопрос

Здравствуйте,

имеется InsertQuery по созданию контакта. Проблема в том что после создания контакта надо ещё заполнить его детали аналогичными Insert-ами по другим таблицам, а для связи нужен ID созданной записи. Может ли InsertQuery возвращать этот ID? Или проще SelectQuery получить ID только что созданной записи?

Ответ

InsertQuery не возвращает ID записи. Как Вы уже сказали для заполнения деталей нужен ID.
Следует сперва создать сущность, получить ее идентификатор и по этом ID заполнять детали контакта.

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Симптомы

Суть проблемы: в чешской локализации невозможно добавить стандартную деталь. При сохранении изменений в консоли ошибка «Cannot read property 'name' of undefined» (версия 7.7).

 Кейс 1:

- открываем в мастере мобильного приложения «Настроить детали» в разделе «Заказы» (Objednбvky).

 

- добавляем деталь «Продукт в заказе» (Produkt v objednбvce)

 

- в результате добавления детали «Продукты в заказе» в дизайнере мобильного приложения происходит свал:

 

Если опять перейти в настройки детали, то caption этой детали не отображается.

 

Кейс 2:

- в мастере мобильного приложения открыть настройки стандартной детали, например, раздела «Контрагенты» (Firmy)

 

- ничего не добавляем, а только необходимо нажать на кнопку сохранить;

 

- заново открываем настройки детали раздела «Контрагенты».  Увидим, что caption снова не отображаются. Снова нажимаем на сохранить.

 

- в результате возникает такая же ошибка.

 

Доп. информация:

- На русской и английской локализации не удается воспроизвести ошибку.

- В 7.8 не удалось воспроизвести, так как нет демки с чешской локализацией.

- При создании детали нормально формируется код:

{
    "operation": "insert",
    "name": "settings",
    "values": {
        "entitySchemaName": "Order",
        "details": [],
        "columnSets": [],
        "localizableStrings": {
            "primaryColumnSetOrder_caption": "Zбkladnн informace",
            "OrderProductDetailV2StandartDetailOrder_caption": "Produkt v objednбvce"
        },
        "settingsType": "RecordPage",
        "operation": "insert"
    }
},

но у самой локализационной строки OrderProductDetailV2StandartDetailOrder_caption значение пустое. Если же туда добавить руками Produkt v objednбvce, то на странице перечня деталей карты Заказ мобильного приложения название появляется. Если же мы будем добавлять деталь сразу на страницу - то тоже самое сначала название детали видно, но после ее конфигурирования, сохранения страницы и повторного ее открытия видим undefined вместо названия. При этом никаких ошибок ни в консоли, ни в запросах нет.

Решение

Для решения проблемы необходимо:

1)      Создать замещающий клиентский модуль(Nahrazující uživatelský modul) для схемы «Mobile app module - Section designer»

Внутри написать такой код:

define("MobileSectionDesignerModule", [],
    function() {
        return {};
    }
);
Ext.define("Terrasoft.MobileDesignerSchemaManager.override", {
    override: "Terrasoft.MobileDesignerSchemaManager",
    statics: {
        getCultureValues: function(value) {
            var res = this.callParent(arguments);
            res["cs-CZ"] = value;
            return res;
        }
    }
});

 

2)      После чего, удалить проблемный раздел из конфигурации:

А) Удалить схемы

UsrMobileOrderActionsSettingsDefaultWorkplace

UsrMobileOrderGridPageSettingsDefaultWorkplace

UsrMobileOrderProductActionsSettingsDefaultWorkplace

UsrMobileOrderProductGridPageSettingsDefaultWorkplace

UsrMobileOrderProductRecordPageSettingsDefaultWorkplace

UsrMobileOrderRecordPageSettingsDefaultWorkplace

 

Б) В манифесте MobileApplicationManifestDefaultWorkplace

Удалить локализируемую строку OrderSectionTitle

И в блоке "Modules" удалить "Order"

3)      И снова добавить раздел и деталь  через дизайнер.

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Публикация
  1. Групповые задачи в календаре. Например, один из пользователей BPMonline создал задачу в календаре и добавил двух участников, email которых указаны у контактов в BPMonline. Он запустил синхронизацию с календарем, создалась задача, ответственным которой является тот, кто запустил синхронизацию, а на детали участники находится он и те контакты, чьи email были указаны как гости в календаре. Если два других пользователя, которые участвуют в задаче запустят синхронизацию с календарем, для них создадутся их собственные задачи, отличающиеся от предыдущей только тем, что ответственные в них будут те пользователи, которые запустили синхронизацию. Таким образом, у каждого из них будет свою копия задачи и она будет обновляться только при синхронизации с их календарями. Если задача изменится и первый из них запустит синхронизацию, то поменяется его экземпляр задачи.
  2. Сопоставление записей происходит только по ID. Если два пользователя BPMonline затянули в систему двух контактов с одинаковой фамилией, то создадутся два контакта с одинаковой фамилией. При импорте из Google Contacts не ищется запись в BPMonline по имени или по каким-либо другим признакам, она создается заново.
  3. Слияние дублей. Если потом будет обнаружено что есть дублирующиеся данные и эти два контакта не однофамильцы, а действительно дубли, их сольют слиянием дублей. При этом в таблице метаданных одной записи контакта будут находиться две записи и соответственно эта запись контакта будет при изменении попадать в оба аккаунта гугл и изменения в нее также будут приходить из двух аккаунтов.
  4. Отключение/включение синхронизации. Один пользователь BPMonline может настроить синхронизацию только с одним аккаунтом google. Но он может сначала настроить один аккаунт, потом второй. Если после этого пользователь захочет вернуться к первому, то при авторизации ему будет сказано, что он не может этого сделать. Нужно перейти по ссылке https://accounts.google.com/b/1/IssuedAuthSubTokens?hl=ru и предварительно отключить доступ к аккаунту из приложения BPMonline.
  5. 410 Gone. Ошибка возникает при попытке синхронизировать записи календаря за период более одного месяца. 
  6. Другие особенности:
  • Для синхронизации с google хранятся даты начала и окончания последней синхронизации, т.к. записи берутся измененные в период между датой начала текущей синхронизации и после даты окончания предыдущей.
  • Синхронизированные записи из календаря имеют признак "Отображать в расписании"
  • Если у записи синхронизированной с google есть более одной записи в метаданных, что может произойти при слиянии дублей, то она не удаляется до тех пор, пока не удалится из всех аккаунтов, с которыми связана. Если эта запись удалено только в одном аккаунте, то для него будет храниться признак удаления, благодаря которому запись в этом аккаунте не будет создаваться заново.

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Вопрос

Отображать на карте не стандартный раздел "Щиты". Версия 7.5.0.966

Ответ

Реализовывается данный вопрос по аналогии с его реализацией в разделе «Контрагенты». 
Пример реализации:
 

/**
* Действие "Показать на карте"
*/
openShowOnMap: function() {
    var items = this.getSelectedItems();
    var select = Ext.create("Terrasoft.EntitySchemaQuery", {
        rootSchemaName: "Account"
    });
    select.addColumn("Id");
    select.addColumn("Name");
    select.addColumn("Address");
    select.addColumn("City");
    select.addColumn("Region");
    select.addColumn("Country");
    select.addColumn("GPSN");
    select.addColumn("GPSE");
    select.filters.add("AcountIdFilter", this.Terrasoft.createColumnInFilterWithParameters("Id", items));
    select.getEntityCollection(function(result) {
        if (result.success) {
            var mapsConfig = {
                mapsData: []
            };
            result.collection.each(function(item) {
                var address = [];
                if (item.get("Country") && item.get("Country").displayValue) {
                    address.push(item.get("Country").displayValue);
                }
                if (item.get("Region") && item.get("Region").displayValue) {
                    address.push(item.get("Region").displayValue);
                }
                if (item.get("City") && item.get("City").displayValue) {
                    address.push(item.get("City").displayValue);
                }
                address.push(item.get("Address"));
                var dataItem = {
                    caption: item.get("Name"),
                    content: "<h2>" + item.get("Name") + "</h2><div>" + address.join(", ") + "</div>",
                    address: item.get("Address") ? address.join(", ") : null,
                    gpsN: item.get("GPSN"),
                    gpsE: item.get("GPSE"),
                    updateCoordinatesConfig: {
                        schemaName: "Account",
                        id: item.get("Id")
                    }
                };
                mapsConfig.mapsData.push(dataItem);
            });
            var mapsModuleSandboxId = this.sandbox.id + "_MapsModule" + this.Terrasoft.generateGUID();
            this.sandbox.subscribe("GetMapsConfig", function() {
                return mapsConfig;
            [mapsModuleSandboxId]);
            this.sandbox.loadModule("MapsModule", {
                id: mapsModuleSandboxId,
                keepAlive: true
            });
        }
    }, this);
},

 

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Вопрос

Можно ли сделать чтобы "JS Error Report" не ругался на отступы, To many errors. (37% scanned)

Ответ

Функциональность JSHint зашита в ядро приложения и не является отключаемой.

Данный функционал реализован для того, чтобы код, написанный разработчиками, был читабелен и легко поддерживался другими разработчиками в будущем.

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Симптомы

Ошибка: у сотрудника не отображается в мобильной версии завершенная активность (визит). Сам же визит отображается в десктопе (представление «Расписание» в активностях). Используется платформа UIv1. Версия приложения 7.6 pharma

Кейс:

  1. Засихронизироваться под пользователем;
  2. Открыть расписание/список активностей за 23.03.2017;
  3. В результате визит «Визит к врачу: ФИО, Адрес, ДЕРМАТОЛОГ» не отображается за 23.03.2017.

Доп. инфа:

В десктопе данный визит есть

Также данная активность если открыть активности контакта врача.

Причина

В этом проекте доработана базовая фильтрация раздела Активности (проектное решение)

Происходит дополнительная фильтрация по НОВЫМ полям «Начало факт» и «Завершение факт»

Примерно такая:

SELECT *
FROM [Activity] AS [Activity]
WHERE 
(
    (
        NOT([Activity].[StatusId] = '4bdbb88f-58e6-df11-971b-001d60e938c6')
        AND
        datetime([Activity].[DueDate]) >= datetime('2017-03-22 22:00:00.000Z')
    )
    OR
    (
        [Activity].[StatusId] = '4bdbb88f-58e6-df11-971b-001d60e938c6'
        AND
        datetime([Activity].[ActualDueDate]) >= datetime('2017-03-22 22:00:00.000Z')
    )
)
AND
(
    (
        [Activity].[StatusId] = '4bdbb88f-58e6-df11-971b-001d60e938c6'
        AND
        datetime([Activity].[ActualStartDate]) <= datetime('2017-03-23 21:59:59.000Z')
    )
    OR
    (
        NOT([Activity].[StatusId] = '4bdbb88f-58e6-df11-971b-001d60e938c6')
        AND
        datetime([Activity].[StartDate]) <= datetime('2017-03-23 21:59:59.000Z')
    )
)

У данной активности

Не заполнено поле «Завершение факт» поэтому она и не попадает в реестр мобильного приложения.

Решение

Заполнить колонку "Завершение факт" и/или "Начало факт" (ActualDueDate или ActualStartDate) тем же значением, что и в колонках "StartDate" и "DueDate" на уровне БД.

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Для MS Sql Server

/*
** Project: BPMonline
** DBMS   : MSSQL 2008
** Type   : Stored Procedure
** Name   : tsp_AnonymizeAllData
*/
 
IF NOT OBJECT_ID('[dbo].[tsp_GetAnonymizationIngoreColumns]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_GetAnonymizationIngoreColumns]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_GetAnonymizationIngoreColumns] 
AS
BEGIN
		SET NOCOUNT ON;
		DECLARE @baseLookupSchemaUId uniqueidentifier = '11AB4BCB-9B23-4B6D-9C86-520FAE925D75'
		DECLARE @baseCodeLookupSchemaUId uniqueidentifier = '2681062B-DF59-4E52-89ED-F9B7DC909AB2'
		DECLARE @ignoreColumns table (tableName SYSNAME, columnName SYSNAME)
 
		INSERT INTO @ignoreColumns VALUES(N'Activity', N'AllowedResult')
		INSERT INTO @ignoreColumns VALUES(N'ActivityCategory', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ActivityParticipantRole', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ActivityPriority', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ActivityStatus', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ActivityType', N'Code')
		INSERT INTO @ignoreColumns VALUES(N'ActivityType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'AdditionalParam', N'ColumnCaption')
		INSERT INTO @ignoreColumns VALUES(N'Amenity', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'AmenitySubtype', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'BulkEmailCategory', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'BulkEmailLaunchOption', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'CallDirection', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'Campaign', N'SchemaData')
		INSERT INTO @ignoreColumns VALUES(N'CampaignFilter', N'SearchData')
		INSERT INTO @ignoreColumns VALUES(N'CampaignStepType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'CashflowType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'CashflowType', N'Description')
		INSERT INTO @ignoreColumns VALUES(N'CaseStatus', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'CaseStatus', N'ButtonCaption')
		INSERT INTO @ignoreColumns VALUES(N'Category', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ChartAggregationType', N'AggregationTypeCode')
		INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'ChartEntityColumn')
		INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'ChartSeriesKind')
		INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'Filter')
		INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'GroupByField')
		INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'GroupByTypeDateTimeId')
		INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'ModuleObjAccessField')
		INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'OrderByChartPropertyColumn')
		INSERT INTO @ignoreColumns VALUES(N'ChartProperty', N'OrderDirection')
		INSERT INTO @ignoreColumns VALUES(N'Command', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'Communication', N'Code')
		INSERT INTO @ignoreColumns VALUES(N'Communication', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ConfigItemCategory', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ConfigItemStatus', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ConfigItemType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ContentBlock', N'Config')
		INSERT INTO @ignoreColumns VALUES(N'ContextHelp', N'ContextHelpId')
		INSERT INTO @ignoreColumns VALUES(N'ContextHelp', N'LMSUrl')
		INSERT INTO @ignoreColumns VALUES(N'ContextHelp', N'ProductEdition')
		INSERT INTO @ignoreColumns VALUES(N'DataType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'DependencyCategory', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'DependencyType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'DependencyType', N'ReverseTypeName')
		INSERT INTO @ignoreColumns VALUES(N'Dimension', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'Dimension', N'Path')
		INSERT INTO @ignoreColumns VALUES(N'DisplayDateFormat', N'Format')
		INSERT INTO @ignoreColumns VALUES(N'DeadlineCalcSchemas', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'DeadlineCalcSchemas', N'Default')
		INSERT INTO @ignoreColumns VALUES(N'DeadlineCalcSchemas', N'Handler')
		INSERT INTO @ignoreColumns VALUES(N'DocumentState', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'DocumentType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'EmailSendStatus', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'EmailTemplateMacros', N'ColumnPath')
		INSERT INTO @ignoreColumns VALUES(N'EmailTemplateMacros', N'Position')
		INSERT INTO @ignoreColumns VALUES(N'EmailTemplateMacros', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'EmailType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'EntitySchemaRecRightOperation', N'Description')
		INSERT INTO @ignoreColumns VALUES(N'EntitySchemaRecRightOperation', N'Value')
		INSERT INTO @ignoreColumns VALUES(N'ESNNotificationType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ESNNotificationType', N'Action')
		INSERT INTO @ignoreColumns VALUES(N'ExternalSource', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'FolderType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'Gender', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'GroupByTypeDateTime', N'Description')
		INSERT INTO @ignoreColumns VALUES(N'GroupByTypeDateTime', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'AmenityType')
		INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'ParameterClass')
		INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'ParameterDisplayName')
		INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'ParameterName')
		INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'ParameterProperty')
		INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'PropertyPath')
		INSERT INTO @ignoreColumns VALUES(N'IntegrationParameter', N'SelectedParameter')
		INSERT INTO @ignoreColumns VALUES(N'IntegrationParamValue', N'AmenityValue')
		INSERT INTO @ignoreColumns VALUES(N'IntegrationParamValue', N'ParameterDisplayValue')
		INSERT INTO @ignoreColumns VALUES(N'IntegrationParamValue', N'ParameterValue')
		INSERT INTO @ignoreColumns VALUES(N'IntegrationSettings', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'IntroPageLookup', N'CodePage')
		INSERT INTO @ignoreColumns VALUES(N'IntroPageLookup', N'VideoUrl')
		INSERT INTO @ignoreColumns VALUES(N'IntroPageLookup', N'VideoCaption')
		INSERT INTO @ignoreColumns VALUES(N'IntroPageLookup', N'AcademyUrl')
		INSERT INTO @ignoreColumns VALUES(N'KnowledgeBaseType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'LDAPAuthType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'LDAPAuthType', N'Value')
		INSERT INTO @ignoreColumns VALUES(N'LeadDisqualifyReason', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'LeadMedium', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'LeadRegisterMethod', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'LeadType', N'Description')
		INSERT INTO @ignoreColumns VALUES(N'LeadType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'LeadStage', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'LeadTypeStatus', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'LendingState', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ListingType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'Listing', N'Latitude')
		INSERT INTO @ignoreColumns VALUES(N'Listing', N'Longitude')
		INSERT INTO @ignoreColumns VALUES(N'Lookup', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'LookupFolder', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'Macros', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'MailServer', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'MailServer', N'Address')
		INSERT INTO @ignoreColumns VALUES(N'MailServer', N'Port')
		INSERT INTO @ignoreColumns VALUES(N'MailServer', N'UseSSL')
		INSERT INTO @ignoreColumns VALUES(N'MailServer', N'UseSSLforSending')
		INSERT INTO @ignoreColumns VALUES(N'MailServer', N'SMTPServerAddress')
		INSERT INTO @ignoreColumns VALUES(N'MailServer', N'SMTPPort')
		INSERT INTO @ignoreColumns VALUES(N'MailServer', N'ExchangeEmailAddress')
		INSERT INTO @ignoreColumns VALUES(N'MailServerType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'MailSyncPeriod', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'MailSyncPeriod', N'Code')
		INSERT INTO @ignoreColumns VALUES(N'MainParam', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'MessageListener', N'ClassName')
		INSERT INTO @ignoreColumns VALUES(N'MessagePublisher', N'ClassName')
		INSERT INTO @ignoreColumns VALUES(N'MessageNotifier', N'ClassName')
		INSERT INTO @ignoreColumns VALUES(N'OAuthApplications', N'AppClassName')
		INSERT INTO @ignoreColumns VALUES(N'OAuthApplications', N'ClientId')
		INSERT INTO @ignoreColumns VALUES(N'OAuthApplications', N'ClientClassName')
		INSERT INTO @ignoreColumns VALUES(N'OAuthApplications', N'SecretKey')
		INSERT INTO @ignoreColumns VALUES(N'MLModel', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'MLModel', N'MetaData')
		INSERT INTO @ignoreColumns VALUES(N'MLModel', N'TrainingSetQuery')
		INSERT INTO @ignoreColumns VALUES(N'MLModel', N'BatchPredictionQuery')
		INSERT INTO @ignoreColumns VALUES(N'MLModelState', N'Code')
		INSERT INTO @ignoreColumns VALUES(N'MLPrediction', N'Value')
		INSERT INTO @ignoreColumns VALUES(N'MLPrediction', N'Significance')
		INSERT INTO @ignoreColumns VALUES(N'MLProblemType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'MLProblemType', N'ServiceUrl')
		INSERT INTO @ignoreColumns VALUES(N'MLProblemType', N'TrainingEndpoint')
		INSERT INTO @ignoreColumns VALUES(N'MLProblemType', N'PredictionEndpoint')
		INSERT INTO @ignoreColumns VALUES(N'MLTrainSession', N'Error')
		INSERT INTO @ignoreColumns VALUES(N'NotificationProvider', N'ClassName')
		INSERT INTO @ignoreColumns VALUES(N'NotificationProvider', N'Type')
		INSERT INTO @ignoreColumns VALUES(N'ParamMatchingType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'Period', N'DueDate')
		INSERT INTO @ignoreColumns VALUES(N'Period', N'StartDate')
		INSERT INTO @ignoreColumns VALUES(N'PeriodType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'PortalSchemaAccessList', N'AccessEntitySchemaName')
		INSERT INTO @ignoreColumns VALUES(N'ProblemPriority', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ProblemStatus', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ProblemType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ProcessSchemaType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ProjectEntryType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'Property', N'Latitude')
		INSERT INTO @ignoreColumns VALUES(N'Property', N'Longitude')
		INSERT INTO @ignoreColumns VALUES(N'QualifyStatus', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'QualifyStatus', N'StageNumber')
		INSERT INTO @ignoreColumns VALUES(N'QualifyStatus', N'StageOrder')
		INSERT INTO @ignoreColumns VALUES(N'QualifyStatus', N'StageInnerOrder')
		INSERT INTO @ignoreColumns VALUES(N'QualifyStatus', N'IsFinal')
		INSERT INTO @ignoreColumns VALUES(N'QuickAddMenuItem', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ReleasePriority', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ReleaseStatus', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ReleaseType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'RemindInterval', N'Description')
		INSERT INTO @ignoreColumns VALUES(N'RemindInterval', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'RemindInterval', N'Value')
		INSERT INTO @ignoreColumns VALUES(N'RequestType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'RoleInServiceTeam', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ServiceCategory', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ServiceObjectType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ServiceStatus', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'Status', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'TimeUnit', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'TimeZone', N'Offset')
		INSERT INTO @ignoreColumns VALUES(N'TimeZone', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'DayOfWeek', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'DayOfWeek', N'Code')
		INSERT INTO @ignoreColumns VALUES(N'DayOfWeek', N'Number')
		INSERT INTO @ignoreColumns VALUES(N'LMColumnTemplate', N'ColumnName')
		INSERT INTO @ignoreColumns VALUES(N'LMStartEvent', N'ConditionData')
		INSERT INTO @ignoreColumns VALUES(N'LMStartEvent', N'ChangedColumns')
		INSERT INTO @ignoreColumns VALUES(N'LMStartEvent', N'MessageTemplate')
		INSERT INTO @ignoreColumns VALUES(N'ListenerByNotifier', N'NotifierConnectionColumn')
		INSERT INTO @ignoreColumns VALUES(N'DisplayDateFormat', N'Format')
		INSERT INTO @ignoreColumns VALUES(N'SysProcessLog', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'SysProcessLog', N'DurationInMinutes')
		INSERT INTO @ignoreColumns VALUES(N'SysProcessLog', N'DurationInHours')
		INSERT INTO @ignoreColumns VALUES(N'SysProcessLog', N'DurationInDays')
		INSERT INTO @ignoreColumns VALUES(N'SysProcessElementLog', N'Type')
		INSERT INTO @ignoreColumns VALUES(N'SysProcessElementLog', N'Caption')
		INSERT INTO @ignoreColumns VALUES(N'SysProcessElementLog', N'ErrorDescription')
		INSERT INTO @ignoreColumns VALUES(N'SysProcessElementLog', N'DurationInMinutes')
		INSERT INTO @ignoreColumns VALUES(N'SysProcessElementLog', N'DurationInHours')
		INSERT INTO @ignoreColumns VALUES(N'SysProcessElementLog', N'DurationInDays')
		INSERT INTO @ignoreColumns VALUES(N'FileType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'FileType', N'Code')
		INSERT INTO @ignoreColumns VALUES(N'BulkEmailStatus', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'BulkEmailStatus', N'IsActive')
		INSERT INTO @ignoreColumns VALUES(N'LeadSource', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'LeadSource', N'Description')
		INSERT INTO @ignoreColumns VALUES(N'Country', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'Country', N'Image')
		INSERT INTO @ignoreColumns VALUES(N'Country', N'BilingInfo')
		INSERT INTO @ignoreColumns VALUES(N'QueueStatus', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'QueueStatus', N'IsInitial')
		INSERT INTO @ignoreColumns VALUES(N'QueueStatus', N'IsFinal')
		INSERT INTO @ignoreColumns VALUES(N'City', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'Region', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'QueuePriority', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'QueuePriority', N'Value')
		INSERT INTO @ignoreColumns VALUES(N'Calendar', N'Name')	
		INSERT INTO @ignoreColumns VALUES(N'ContractState', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'ContractState', N'Position')
		INSERT INTO @ignoreColumns VALUES(N'Tax', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'Tax', N'Percent')
		INSERT INTO @ignoreColumns VALUES(N'AddressType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'AddressType', N'ForContact')
		INSERT INTO @ignoreColumns VALUES(N'AddressType', N'ForAccount')
		INSERT INTO @ignoreColumns VALUES(N'RelationType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'RelationType', N'ForContactContact')
		INSERT INTO @ignoreColumns VALUES(N'RelationType', N'ForAccountContact')
		INSERT INTO @ignoreColumns VALUES(N'RelationType', N'ForContactAccount')
		INSERT INTO @ignoreColumns VALUES(N'RelationType', N'ForAccountAccount')
		INSERT INTO @ignoreColumns VALUES(N'SpecificationType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'SpecificationType', N'Description')
		INSERT INTO @ignoreColumns VALUES(N'Unit', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'Unit', N'ShortName')
		INSERT INTO @ignoreColumns VALUES(N'QualifyStatus', N'Color')
		INSERT INTO @ignoreColumns VALUES(N'CaseStatus', N'Color')
		INSERT INTO @ignoreColumns VALUES(N'OpportunityStage', N'Color')
		INSERT INTO @ignoreColumns VALUES(N'QueueObjectColumn', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'QueueObjectColumn', N'Caption')
		INSERT INTO @ignoreColumns VALUES(N'BWSSalesPeriod', N'BWSYearMonthUnq')
		INSERT INTO @ignoreColumns VALUES(N'DuplicatesHistory', N'SchemaTableName')
		INSERT INTO @ignoreColumns VALUES(N'FieldForceActionType', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'FieldForceActionType', N'Code')
		INSERT INTO @ignoreColumns VALUES(N'FieldForceActionsInRule', N'Priority')
		INSERT INTO @ignoreColumns VALUES(N'FieldForceRules', N'Name')
		INSERT INTO @ignoreColumns VALUES(N'FieldForceRules', N'StartDate')
		INSERT INTO @ignoreColumns VALUES(N'FieldForceRules', N'DueDate')
		INSERT INTO @ignoreColumns VALUES(N'FieldForceRules', N'VisitCount')
 
		INSERT INTO @ignoreColumns
			SELECT DISTINCT [Name], N'Name'
			FROM SysSchema
			WHERE [ParentId] IN (
				SELECT [Id]
				FROM SysSchema
				WHERE [UId] IN (@baseLookupSchemaUId, @baseCodeLookupSchemaUId))
		INSERT INTO @ignoreColumns
			SELECT DISTINCT [Name], N'Code'
			FROM SysSchema
			WHERE [ParentId] IN (
				SELECT [Id]
				FROM SysSchema
				WHERE [UId] = @baseCodeLookupSchemaUId)
		SELECT DISTINCT tableName, columnName FROM @ignoreColumns
END
GO
 
IF NOT OBJECT_ID('[dbo].[tsp_DoAnonymizationCheck]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_DoAnonymizationCheck]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_DoAnonymizationCheck] 
	@DBName SYSNAME,
	@moduleTablesExpression NVARCHAR(MAX),
	@errorModuleCaptionExpression NVARCHAR(MAX)
AS
BEGIN
		SET NOCOUNT ON;
 
		DECLARE	@tableName SYSNAME
		DECLARE @tables TABLE ([TABLE_NAME] SYSNAME)
		DECLARE @columns TABLE (COLUMN_NAME SYSNAME)
 
		INSERT INTO @tables EXEC sp_executesql @moduleTablesExpression
 
		DECLARE @ignoreColumns TABLE (tableName SYSNAME, columnName SYSNAME)
		INSERT INTO @ignoreColumns EXEC [dbo].[tsp_GetAnonymizationIngoreColumns]
 
		DECLARE moduleTablesDB CURSOR LOCAL
		FOR SELECT * FROM @tables
 
		OPEN moduleTablesDB
 
		WHILE 1 = 1
		BEGIN
				FETCH NEXT FROM moduleTablesDB INTO @tableName
				IF @@FETCH_STATUS = -1 BREAK
				IF @@FETCH_STATUS = -2 CONTINUE
				IF @tableName = 'CaseStatus' CONTINUE
				IF @tableName = 'SysProcessElementLog' CONTINUE
 
				PRINT ('Checking ' + @tableName)
				DECLARE @getUnananymazedDataCountExpression NVARCHAR(MAX)
				DECLARE @countUnananymazedData INT = 0
				DECLARE @displayColumnNameExpression NVARCHAR(MAX),
						@defaultColumnNameExpression NVARCHAR(MAX),
						@templateColumnName NVARCHAR(MAX),
						@primaryColumnNameExpression NVARCHAR(MAX)
 
				DECLARE @errorMessage NVARCHAR(MAX),
						@moduleCaptionExpression NVARCHAR(MAX),
						@moduleName NVARCHAR(MAX)
 
				SET @primaryColumnNameExpression = 'SELECT kcu.[COLUMN_NAME]
					FROM [' + @DBName + '].[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu               
								JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
										ON [it].[TABLE_NAME] = [kcu].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
					WHERE kcu.[TABLE_NAME] = ''' + @tableName + 
								''' AND kcu.[CONSTRAINT_NAME] LIKE ''PK%''' 
 
				INSERT INTO @columns EXEC sp_executesql @primaryColumnNameExpression
 
				DECLARE @keyName NVARCHAR(MAX)
				SET @keyName = (SELECT TOP 1 [COLUMN_NAME] FROM @columns)
 
				DELETE FROM @columns
 
				IF @keyName IS NULL
				BEGIN
					PRINT @tableName + N' пропускаем... KeyName = ' + ISNULL(@keyName, 'NULL')
					CONTINUE
				END
 
				SET @displayColumnNameExpression = 'SELECT [ic].[COLUMN_NAME]
							FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
							WHERE [DATA_TYPE] IN (N''NVARCHAR'')
								AND [ic].[TABLE_NAME] = ''' + @tableName + '''
								AND [ic].[COLUMN_NAME] IN (''Name'', ''Title'', ''Number'', ''Type'')' 
 
				DELETE FROM @columns
				INSERT INTO @columns EXEC sp_executesql @displayColumnNameExpression
				IF EXISTS (SELECT * FROM @columns)
				BEGIN
					SELECT @templateColumnName = [COLUMN_NAME] FROM @columns
 
					IF EXISTS(SELECT NULL FROM @ignoreColumns WHERE tableName = @tableName AND columnName = @templateColumnName)
						CONTINUE
 
					SET @getUnananymazedDataCountExpression = 'SELECT @countOUT = COUNT(*) 
							FROM [' + @DBName + '].[dbo].[' + @tableName + '] t1
							WHERE NOT [' + @templateColumnName + '] LIKE ''' + @templateColumnName +' %'' 
								AND [' + @templateColumnName + '] IS NOT NULL
								AND [' + @templateColumnName + '] <> '''''
 
					SELECT @getUnananymazedDataCountExpression = @getUnananymazedDataCountExpression +
						CASE
							WHEN @tableName='Contact' AND @templateColumnName='Name'
								THEN ' AND NOT EXISTS(SELECT 1 FROM [' + @DBName + N'].[dbo].[SysAdminUnit] [u] WHERE [u].[ContactId] = [t1].[Id])'
							WHEN @tableName='Account' AND @templateColumnName='Name'
								THEN ' AND (Name != ''Наша компания'' OR [t1].[Id] != ''E308B781-3C5B-4ECB-89EF-5C1ED4DA488E'')' 
							ELSE ''
						END
 
					EXEC sp_executesql @getUnananymazedDataCountExpression, N'@countOUT INT OUTPUT', @countOUT = @countUnananymazedData OUTPUT
					IF @countUnananymazedData > 0 
					BEGIN
						SET @moduleCaptionExpression = @errorModuleCaptionExpression + '''' + @tableName + ''''
						EXEC sp_executesql @moduleCaptionExpression, N'@outModuleName NVARCHAR(MAX) OUTPUT', @outModuleName = @moduleName OUTPUT
						SET @errorMessage = N'Данные раздела ' + @moduleName + ' не обезличенны!'
						print @templateColumnName
						RAISERROR (@errorMessage, 16, 1)
						RETURN 
					END
					DELETE FROM @columns
					CONTINUE
				END
 
				SET @defaultColumnNameExpression = 'SELECT Top 1 [ic].[COLUMN_NAME]
							FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
							WHERE [DATA_TYPE] IN (N''NVARCHAR'')
								AND [ic].[TABLE_NAME] = ''' + @tableName + ''''
 
				DELETE FROM @columns
				INSERT INTO @columns EXEC sp_executesql @defaultColumnNameExpression
				IF EXISTS (SELECT * FROM @columns)
				BEGIN
					SELECT @templateColumnName = [COLUMN_NAME] FROM @columns
					SET @getUnananymazedDataCountExpression = 'SELECT @countOUT = COUNT(*) 
							FROM [' + @DBName + '].[dbo].[' + @tableName + '] t1
							WHERE NOT [' + @templateColumnName + '] LIKE ''' + @templateColumnName +' %'' 
								AND [' + @templateColumnName + '] IS NOT NULL
								AND [' + @templateColumnName + '] <> '''''
 
					SELECT @getUnananymazedDataCountExpression = @getUnananymazedDataCountExpression +
						CASE
							WHEN @tableName='Contact' and @templateColumnName='Name'
								THEN ' AND NOT EXISTS(SELECT 1 FROM [' + @DBName + N'].[dbo].[SysAdminUnit] [u] WHERE [u].[ContactId] = [t1].[Id])'
							WHEN @tableName='Account' and @templateColumnName='Name'
								THEN ' AND (Name != N''Наша компания'' OR [t1].[Id] != ''E308B781-3C5B-4ECB-89EF-5C1ED4DA488E'')' 
						ELSE ''
					END
 
					EXEC sp_executesql @getUnananymazedDataCountExpression, N'@countOUT INT OUTPUT', @countOUT = @countUnananymazedData OUTPUT
					IF @countUnananymazedData > 0
					BEGIN
						SET @moduleCaptionExpression = @errorModuleCaptionExpression + '''' + @tableName + ''''
						EXEC sp_executesql @moduleCaptionExpression, N'@outModuleName NVARCHAR(MAX) OUTPUT', @outModuleName = @moduleName OUTPUT
						SET @errorMessage = N'Данные раздела ' + @moduleName + ' не обезличенны!'
						print @templateColumnName
						RAISERROR (@errorMessage, 16, 1)
						RETURN
					END
				END
				DELETE FROM @columns
		END
		CLOSE moduleTablesDB
		DEALLOCATE moduleTablesDB
END
GO
 
IF NOT OBJECT_ID('[dbo].[tsp_DoDataAnonymization]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_DoDataAnonymization]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_DoDataAnonymization]
	@DBName SYSNAME,
	@tablesExpression NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;
 
	DECLARE
			@tableName SYSNAME,
			@columnName SYSNAME,
			@keyName SYSNAME,
			@expression NVARCHAR(MAX),
			@type NVARCHAR(128)
 
		DECLARE @tables TABLE ([TABLE_NAME] SYSNAME, [COLUMN_NAME] SYSNAME, [DATA_TYPE] SYSNAME)
		DECLARE @columns TABLE (COLUMN_NAME SYSNAME)
 
		DECLARE @updateChunkRowCount INT = 1000
 
		PRINT ('Data Anonymization Tables Expression:')
		PRINT (@tablesExpression)
 
		INSERT INTO @tables EXEC sp_executesql @tablesExpression
 
		DECLARE @ignoreColumns TABLE (tableName SYSNAME, columnName SYSNAME)
		INSERT INTO @ignoreColumns EXEC [dbo].[tsp_GetAnonymizationIngoreColumns]
 
		DECLARE @ignoreColumnsExpression NVARCHAR(MAX)
		SET @ignoreColumnsExpression =
			'SELECT [ic].TABLE_NAME, COLUMN_NAME
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
				JOIN [INFORMATION_SCHEMA].[TABLES] [it] ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
			WHERE COLUMNPROPERTY(object_id([ic].TABLE_NAME), COLUMN_NAME, ''IsIdentity'') = 1
				OR COLUMNPROPERTY(object_id([it].TABLE_NAME), COLUMN_NAME, ''IsComputed'') = 1'
		INSERT INTO @ignoreColumns EXEC sp_executesql @ignoreColumnsExpression
 
		DECLARE allDB CURSOR LOCAL
		FOR SELECT * FROM @tables
 
		OPEN allDB
		WHILE 1 = 1
		BEGIN
				FETCH NEXT FROM allDB INTO @tableName, @columnName, @type
				IF @@FETCH_STATUS = -1 BREAK
				IF @@FETCH_STATUS = -2 CONTINUE
 
				IF EXISTS(SELECT NULL FROM @ignoreColumns WHERE tableName = @tableName AND columnName = @columnName)
					CONTINUE
 
				DECLARE @columnNameExpression NVARCHAR(MAX)
 
				SET @columnNameExpression = 'SELECT kcu.[COLUMN_NAME]
					FROM [' + @DBName + '].[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu               
								JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
										ON [it].[TABLE_NAME] = [kcu].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
					WHERE kcu.[TABLE_NAME] = ''' + @tableName + 
								''' AND kcu.[CONSTRAINT_NAME] LIKE ''PK%''' 
 
				INSERT INTO @columns EXEC sp_executesql @columnNameExpression
 
				SET @keyName = (SELECT TOP 1 [COLUMN_NAME] FROM @columns)
 
				DELETE FROM @columns
 
				IF @keyName IS NULL
				BEGIN
					PRINT @tableName + N' пропускаем... KeyName = ' + ISNULL(@keyName, 'NULL')
					CONTINUE
				END
 
				SET @expression = N'ALTER TABLE [' + @DBName + N'].[dbo].[' + @tableName + N'] DISABLE TRIGGER ALL '
				EXEC(@expression)
 
				IF (@tableName = 'MailboxSynchronizationSettings' AND @columnName = 'UserPassword')
					SET @expression = N'UPDATE [' + @DBName + N'].[dbo].[' + @tableName + N'] SET [' + @columnName + N'] = ''p0mzIa0Nfdo='''
				ELSE IF (@tableName = 'SysMsgUserSettings' AND @columnName = 'ConnectionParams')
					SET @expression = N'UPDATE [' + @DBName + N'].[dbo].[' + @tableName + N'] SET [' + @columnName + N'] =
						REPLACE([' + @columnName + N'], ''"disableCallCentre":false'', ''"disableCallCentre":true'')'
				ELSE IF (@tableName = 'ActivityFile' AND @columnName = 'Data')
					SET @expression = 
						N'WHILE (1 = 1)
						BEGIN
							UPDATE TOP (' + Convert(nvarchar(10), @updateChunkRowCount) + N') [' + @DBName + N'].[dbo].[' + @tableName + N'] SET [' + @columnName + N'] = 0x
							WHERE [' + @columnName + N'] IS NOT NULL AND [' + @columnName + N'] <> 0x
							IF @@ROWCOUNT = 0 
							BEGIN 
								BREAK 
							END 
						END'
				ELSE IF (@type = N'nvarchar')
					SET @expression =
						N'UPDATE [CurrentTable] SET [' + @columnName + N'] = ''' + @columnName + N''' + '' '' + [rownum]
						FROM (SELECT CONVERT(NVARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT)) AS rownum, [' + @columnName + N']
									FROM [' + @DBName + N'].[dbo].[' + @tableName + N']
									WHERE [' + @columnName + N'] IS NOT NULL AND LEN([' + @columnName + N']) > 0 ' +
						CASE
							WHEN @tableName='Contact' and @columnName='Name'
								THEN ' AND NOT EXISTS(SELECT 1 FROM [' + @DBName + N'].[dbo].[SysAdminUnit] [u] WHERE [u].[ContactId] = [' + @tableName + N'].[Id])'
							WHEN @tableName='Account' and @columnName='Name'
								THEN ' AND (Name != N''Наша компания'' OR Name != N''Our company'' OR [Id] != ''E308B781-3C5B-4ECB-89EF-5C1ED4DA488E'')'
							ELSE ''
						END +
						') AS [CurrentTable]'
				ELSE IF (@type = 'int')
					SET @expression = N'WITH TableWithRowNumber AS(
						SELECT
							ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as RowNumber, 
							[' + @columnName + N']
						FROM [' + @DBName + N'].[dbo].[' + @tableName + N']
						WHERE [' + @columnName + N'] IS NOT NULL
					) 
					UPDATE TableWithRowNumber SET [' + @columnName + N'] = RowNumber'
				ELSE
					SET @expression = 
						N'UPDATE [' + @DBName + N'].[dbo].[' + @tableName + N'] SET [' + @columnName + N'] = ' +
							CASE
								WHEN @type = N'varbinary'
										THEN N'0x'
								WHEN @type = N'binary'
										THEN N'0x'
								WHEN @type = N'datetime' OR @type = N'datetime2' OR @type = N'date'
										THEN N'CAST(DATEADD(dd, -(SELECT CEILING(150*RAND())), GETDATE()) AS ' + @type + ')'
								ELSE N'1'
							END +
						N' WHERE [' + @columnName + N'] IS NOT NULL'
 
				PRINT ('Updating ' + @tableName + ' column ' + @columnName)
				EXEC (@expression)
 
				SET @expression = N'ALTER TABLE [' + @DBName + N'].[dbo].[' + @tableName + N'] ENABLE TRIGGER ALL '
				EXEC(@expression)
 
		END
		CLOSE allDB
		DEALLOCATE allDB
END
GO
 
IF NOT OBJECT_ID('[dbo].[tsp_AnonymizeAllData700]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_AnonymizeAllData700]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_AnonymizeAllData700]
		@DBName SYSNAME
AS
BEGIN 
		 SET NOCOUNT ON;
 
		IF (NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBName))
		BEGIN
			RAISERROR (N'Базы данных с таким именем не существует!', 16, 1)
			RETURN
		END
 
		IF (PATINDEX('%anonymous', @DBName) = 0 AND (PATINDEX('sbx-%', @DBName) = 0) AND (PATINDEX('upd-%', @DBName) = 0))
		BEGIN
			RAISERROR (N'Попытка анонимизировать данные из рабочей БД!', 16, 1)
			RETURN 
		END
 
		 DECLARE @tablesExpression NVARCHAR(MAX)
		 SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
			JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
				ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
			WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
			AND
			(
				(
					NOT [ic].[TABLE_NAME] LIKE N''Sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
					AND NOT [ic].[TABLE_NAME] IN (
							SELECT [s].[Name]
							FROM [' + @DBName + '].[dbo].[SysLookup] [l]
									JOIN [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
									ON [l].[SysEntitySchemaUId] = [s].[UId]
								WHERE  [s].[ManagerName] = ''EntitySchemaManager''
								AND NOT EXISTS(SELECT * FROM [' + @DBName + '].[dbo].[SysModule] [m]
										WHERE [m].[SysModuleEntityId] IN (SELECT [Id] 
																		FROM [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
																		WHERE [SysEntitySchemaUId] = [l].[SysEntitySchemaUId]))
						)
					AND [ic].[TABLE_NAME] IN (
							SELECT [s].[Name]
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
							WHERE [s].[ManagerName] = ''EntitySchemaManager''
							)
				)
				OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
				OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
			)
			AND [COLUMN_NAME] NOT IN (''Code'',''SchemaToSearchName'',''ContainerId'',''ModuleName'')
			ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]' 
 
		PRINT (@tablesExpression)
		EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
 
		SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
			JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
					ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE'' 
			WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
			AND 
			(
				(
					NOT [ic].[TABLE_NAME] LIKE N''Sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
					AND NOT [ic].[TABLE_NAME] IN (
							SELECT DISTINCT([s].[Name])
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
							)
				)
				OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
				OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
			)
			ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
		PRINT (@tablesExpression)
		EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
 
		DECLARE 
			@moduleTablesExpression NVARCHAR(MAX),
			@errorModuleCaptionExpression NVARCHAR(MAX)
 
		SET @moduleTablesExpression = '
			SELECT Distinct([it].[TABLE_NAME])
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
			WHERE 
				[it].[TABLE_TYPE] = ''BASE TABLE'' 
				AND
				(
					(
						NOT [it].[TABLE_NAME] LIKE N''Sys%''
						AND NOT [it].[TABLE_NAME] LIKE N''QRTZ%''
						AND [it].[TABLE_NAME] IN (
							SELECT distinct([s].[Name])
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
								JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
									ON 	[sme].[SysEntitySchemaUId] = [s].UId
								JOIN [' + @DBName + '].[dbo].[SysModule] [m]
									ON [m].SysModuleEntityId = [sme].[Id]
							WHERE [s].[ManagerName] = ''EntitySchemaManager''
							)
					)
					OR [it].[TABLE_NAME] LIKE N''Sys%Log''
				)
			ORDER BY [it].[TABLE_NAME]' 
 
		SET @errorModuleCaptionExpression = '
			SELECT Top 1 @outModuleName = [m].[Caption]
			FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
				JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
					ON 	[sme].[SysEntitySchemaUId] = [s].UId
				JOIN [' + @DBName + '].[dbo].[SysModule] [m]
					ON [m].SysModuleEntityId = [sme].[Id]
			WHERE [s].[Name] = '
 
		EXEC tsp_DoAnonymizationCheck @DBName, @moduleTablesExpression, @errorModuleCaptionExpression
 
END
GO
IF NOT OBJECT_ID('[dbo].[tsp_AnonymizeAllData710]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_AnonymizeAllData710]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_AnonymizeAllData710]
		 @DBName SYSNAME
AS
BEGIN 
		 SET NOCOUNT ON;
 
		 IF (NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBName))
		 BEGIN
			RAISERROR (N'Базы данных с таким именем не существует!', 16, 1)
			RETURN 
		 END
 
		 IF (PATINDEX('%anonymous', @DBName) = 0 AND (PATINDEX('sbx-%', @DBName) = 0) AND (PATINDEX('upd-%', @DBName) = 0))
		BEGIN
			RAISERROR (N'Попытка анонимизировать данные из рабочей БД!', 16, 1)
			RETURN 
		END          
 
		 DECLARE @tablesExpression NVARCHAR(MAX)
		 SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
			JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
					ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE'' 
			WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
			AND
			(
				(
					NOT [ic].[TABLE_NAME] LIKE N''Sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
					AND NOT [ic].[TABLE_NAME] IN (
							SELECT [s].[Name]
							FROM [' + @DBName + '].[dbo].[SysLookup] [l]
									JOIN [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
									ON [l].[SysEntitySchemaUId] = [s].[UId]
								WHERE  [s].[ManagerName] = ''EntitySchemaManager''
								AND NOT EXISTS(SELECT * FROM [' + @DBName + '].[dbo].[SysModule] [m]
										WHERE [m].[SysModuleEntityId] IN (SELECT [Id] 
																		FROM [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
																		WHERE [SysEntitySchemaUId] = [l].[SysEntitySchemaUId]))
						)
					AND [ic].[TABLE_NAME] IN (
							SELECT [s].[Name]
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
							WHERE [s].[ManagerName] = ''EntitySchemaManager''
							)
				)
				OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
				OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
			)
			AND [COLUMN_NAME] NOT IN (''Code'',''SchemaToSearchName'',''ContainerId'',''ModuleName'')
			ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]' 
 
		PRINT (@tablesExpression)         
		EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
 
		SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
			JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
					ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE'' 
			WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
			AND 
			(
				(
					NOT [ic].[TABLE_NAME] LIKE N''Sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
					AND NOT [ic].[TABLE_NAME] IN (
							SELECT DISTINCT([s].[Name])
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
							)
				)
				OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
				OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
			)
			ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
		PRINT (@tablesExpression)         
		EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
 
		DECLARE 
				@moduleTablesExpression NVARCHAR(MAX),
				@errorModuleCaptionExpression NVARCHAR(MAX)
 
		SET @moduleTablesExpression = '
			SELECT Distinct([it].[TABLE_NAME])
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
			WHERE
				[it].[TABLE_TYPE] = ''BASE TABLE''
				AND
				(
					(
						NOT [it].[TABLE_NAME] LIKE N''Sys%''
						AND NOT [it].[TABLE_NAME] LIKE N''QRTZ%''
						AND [it].[TABLE_NAME] IN (
							SELECT distinct([s].[Name])
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
								JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
									ON [sme].[SysEntitySchemaUId] = [s].UId
								JOIN [' + @DBName + '].[dbo].[SysModule] [m]
									ON [m].SysModuleEntityId = [sme].[Id]
							WHERE [s].[ManagerName] = ''EntitySchemaManager''
							)
					)
					OR [it].[TABLE_NAME] LIKE N''Sys%Log''
				)
			ORDER BY [it].[TABLE_NAME]'
 
		SET @errorModuleCaptionExpression = '
			SELECT Top 1 @outModuleName = [m].[Caption]
			FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
				JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
					ON [sme].[SysEntitySchemaUId] = [s].UId
				JOIN [' + @DBName + '].[dbo].[SysModule] [m]
					ON [m].SysModuleEntityId = [sme].[Id]
			WHERE [s].[Name] = '
 
		EXEC tsp_DoAnonymizationCheck @DBName, @moduleTablesExpression, @errorModuleCaptionExpression
 
END
GO
IF NOT OBJECT_ID('[dbo].[tsp_AnonymizeAllData720]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_AnonymizeAllData720]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_AnonymizeAllData720]
	@DBName SYSNAME
AS
BEGIN 
		 SET NOCOUNT ON;
 
		 IF (NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBName))
		 BEGIN
			RAISERROR (N'Базы данных с таким именем не существует!', 16, 1)
			RETURN 
		 END
 
		 IF (PATINDEX('%anonymous', @DBName) = 0 AND (PATINDEX('sbx-%', @DBName) = 0) AND (PATINDEX('upd-%', @DBName) = 0))
		BEGIN
			RAISERROR (N'Попытка анонимизировать данные из рабочей БД!', 16, 1)
			RETURN
		END
 
		 DECLARE @tablesExpression NVARCHAR(MAX)
		 SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
			JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
					ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE'' 
			WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
			AND 
			(
				( 
					NOT [ic].[TABLE_NAME] LIKE N''Sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
					AND NOT [ic].[TABLE_NAME] IN (
							SELECT [s].[Name]
							FROM [' + @DBName + '].[dbo].[SysLookup] [l]
									JOIN [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
									ON [l].[SysEntitySchemaUId] = [s].[UId]
								WHERE  [s].[ManagerName] = ''EntitySchemaManager''
								AND NOT EXISTS(SELECT * FROM [' + @DBName + '].[dbo].[SysModule] [m]
										WHERE [m].[SysModuleEntityId] IN (SELECT [Id] 
																		FROM [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
																		WHERE [SysEntitySchemaUId] = [l].[SysEntitySchemaUId]))
						)
					AND [ic].[TABLE_NAME] IN (
							SELECT [s].[Name]
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
							WHERE [s].[ManagerName] = ''EntitySchemaManager''
							)
				)
				OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
				OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
			)
			AND [COLUMN_NAME] NOT IN (''Code'',''SchemaToSearchName'',''ContainerId'',''ModuleName'')
			ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]' 
 
		PRINT ('First Data Anonymization Started')   
		PRINT (@tablesExpression)         
		EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
 
		SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
			JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
					ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE'' 
			WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
			AND 
			(
				(
					NOT [ic].[TABLE_NAME] LIKE N''Sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
					AND NOT [ic].[TABLE_NAME] IN (
							SELECT DISTINCT([s].[Name])
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
							)
				)
				OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
				OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
			)
			ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
		PRINT ('Second Data Anonymization Started')   
		PRINT (@tablesExpression)
		EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
 
		DECLARE 
			@moduleTablesExpression NVARCHAR(MAX),
			@errorModuleCaptionExpression NVARCHAR(MAX)
 
		SET @moduleTablesExpression = '
			SELECT Distinct([it].[TABLE_NAME])
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
			WHERE 
				[it].[TABLE_TYPE] = ''BASE TABLE'' 
				AND
				(
					(
						NOT [it].[TABLE_NAME] LIKE N''Sys%''
						AND NOT [it].[TABLE_NAME] LIKE N''QRTZ%''
						AND [it].[TABLE_NAME] IN (
							SELECT distinct([s].[Name])
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
								JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
									ON [sme].[SysEntitySchemaUId] = [s].UId
								JOIN [' + @DBName + '].[dbo].[SysModule] [m]
									ON [m].SysModuleEntityId = [sme].[Id]
							WHERE [s].[ManagerName] = ''EntitySchemaManager''
							)
					)
					OR [it].[TABLE_NAME] LIKE N''Sys%Log''
				)
			ORDER BY [it].[TABLE_NAME]' 
 
		SET @errorModuleCaptionExpression = '
			SELECT Top 1 @outModuleName = [m].[Caption]
			FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
				JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
					ON [sme].[SysEntitySchemaUId] = [s].UId
				JOIN [' + @DBName + '].[dbo].[SysModule] [m]
					ON [m].SysModuleEntityId = [sme].[Id]
			WHERE [s].[Name] = '
 
		PRINT ('Anonymization Check Started')
		EXEC tsp_DoAnonymizationCheck @DBName, @moduleTablesExpression, @errorModuleCaptionExpression
 
END
GO
IF NOT OBJECT_ID('[dbo].[tsp_AnonymizeAllData721]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_AnonymizeAllData721]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_AnonymizeAllData721]
		@DBName SYSNAME
AS
BEGIN 
		SET NOCOUNT ON;
 
		IF (NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBName))
		BEGIN
			RAISERROR (N'Базы данных с таким именем не существует!', 16, 1)
			RETURN 
		END
 
		IF (PATINDEX('%anonymous', @DBName) = 0 AND (PATINDEX('sbx-%', @DBName) = 0) AND (PATINDEX('upd-%', @DBName) = 0))
		BEGIN
			RAISERROR (N'Попытка анонимизировать данные из рабочей БД!', 16, 1)
			RETURN
		END
 
		 DECLARE @tablesExpression NVARCHAR(MAX)
		 SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
			JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
				ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE'' 
			WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
			AND 
			(
				( 
					NOT [ic].[TABLE_NAME] LIKE N''Sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
					AND NOT [ic].[TABLE_NAME] IN (
							SELECT [s].[Name]
							FROM [' + @DBName + '].[dbo].[SysLookup] [l]
									JOIN [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
									ON [l].[SysEntitySchemaUId] = [s].[UId]
								WHERE  [s].[ManagerName] = ''EntitySchemaManager''
								AND NOT EXISTS(SELECT * FROM [' + @DBName + '].[dbo].[SysModule] [m]
										WHERE [m].[SysModuleEntityId] IN (SELECT [Id] 
																		FROM [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
																		WHERE [SysEntitySchemaUId] = [l].[SysEntitySchemaUId]))
						)
					AND [ic].[TABLE_NAME] IN (
							SELECT [s].[Name]
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
							WHERE [s].[ManagerName] = ''EntitySchemaManager''
							)
				)
				OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
				OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
			)
			AND [COLUMN_NAME] NOT IN (''Code'',''SchemaToSearchName'',''ContainerId'',''ModuleName'')
			ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]' 
 
		PRINT (@tablesExpression)
		EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
 
		SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
			JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
				ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE'' 
			WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
			AND 
			(
				(
					NOT [ic].[TABLE_NAME] LIKE N''Sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
					AND NOT [ic].[TABLE_NAME] IN (
							SELECT DISTINCT([s].[Name])
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
							)
				)
				OR [ic].[TABLE_NAME] LIKE N''Sys%Log''
				OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
			)
			ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
		PRINT (@tablesExpression)         
		EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
 
		DECLARE 
			@moduleTablesExpression NVARCHAR(MAX),
			@errorModuleCaptionExpression NVARCHAR(MAX)
 
		SET @moduleTablesExpression = '
			SELECT Distinct([it].[TABLE_NAME])
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
			WHERE 
				[it].[TABLE_TYPE] = ''BASE TABLE'' 
				AND
				(
					(
						NOT [it].[TABLE_NAME] LIKE N''Sys%''
						AND NOT [it].[TABLE_NAME] LIKE N''QRTZ%''
						AND [it].[TABLE_NAME] IN (
							SELECT distinct([s].[Name])
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
								JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
									ON [sme].[SysEntitySchemaUId] = [s].UId
								JOIN [' + @DBName + '].[dbo].[SysModule] [m]
									ON [m].SysModuleEntityId = [sme].[Id]
							WHERE [s].[ManagerName] = ''EntitySchemaManager''
							)
					)
					OR [it].[TABLE_NAME] LIKE N''Sys%Log''
				)
			ORDER BY [it].[TABLE_NAME]' 
 
		SET @errorModuleCaptionExpression = '
			SELECT Top 1 @outModuleName = [m].[Caption]
			FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
				JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
					ON [sme].[SysEntitySchemaUId] = [s].UId
				JOIN [' + @DBName + '].[dbo].[SysModule] [m]
					ON [m].SysModuleEntityId = [sme].[Id]
			WHERE [s].[Name] = '
 
		EXEC tsp_DoAnonymizationCheck @DBName, @moduleTablesExpression, @errorModuleCaptionExpression
 
END
GO
IF NOT OBJECT_ID('[dbo].[tsp_AnonymizeAllData730]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_AnonymizeAllData730]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_AnonymizeAllData730]
		@DBName SYSNAME
AS
BEGIN 
		 SET NOCOUNT ON;
 
		 IF (NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBName))
		 BEGIN
			RAISERROR (N'Базы данных с таким именем не существует!', 16, 1)
			RETURN
		 END
 
		IF (PATINDEX('%anonymous', @DBName) = 0 AND (PATINDEX('sbx-%', @DBName) = 0) AND (PATINDEX('upd-%', @DBName) = 0))
		BEGIN
			RAISERROR (N'Попытка анонимизировать данные из рабочей БД!', 16, 1)
			RETURN
		END
 
		DECLARE @clearQrtzTriggerExpression NVARCHAR(MAX)
		SET @clearQrtzTriggerExpression = 'DELETE FROM [' + @DBName + '].[dbo].[QRTZ_TRIGGERS]'
		EXEC sp_executesql @clearQrtzTriggerExpression
 
		DECLARE @sysAdminUnitExpression NVARCHAR(MAX)
		DECLARE @supervisorId NVARCHAR(50)
		SET @supervisorId = '7F3B869F-34F3-4F20-AB4D-7480A5FDF647'
		SET @sysAdminUnitExpression = 'UPDATE [' + @DBName + '].[dbo].[SysAdminUnit] SET Name = ''Supervisor_Current'' WHERE Name = ''Supervisor'''
		EXEC sp_executesql @sysAdminUnitExpression
		SET @sysAdminUnitExpression = 'UPDATE [' + @DBName + '].[dbo].[SysAdminUnit] SET Name = ''Supervisor'', UserPassword = ''eF4f7sfUgBuNFei7bk+3djw/zXkaB1ODf3huR0RJDD4='', Active = 1 WHERE Id = ''' + @supervisorId + ''''
		EXEC sp_executesql @sysAdminUnitExpression
 
		DECLARE @sysSettingsValuesExpression NVARCHAR(MAX)
		DECLARE @sysSettingsCodes NVARCHAR(MAX)
		SET @sysSettingsCodes = '''GoogleTagManagerScript'',''UseGoogleTagManager'''
		SET @sysSettingsValuesExpression = 'UPDATE [' + @DBName + '].[dbo].[SysSettingsValue] SET TextValue = '''', IntegerValue=0, FloatValue=0.00, BooleanValue=0, DateTimeValue=NULL, GuidValue=NULL, BinaryValue=NULL WHERE SysSettingsId IN (SELECT Id FROM [' + @DBName + '].[dbo].[SysSettings] WHERE Code IN('+ @sysSettingsCodes +'))'
		EXEC sp_executesql @sysSettingsValuesExpression
 
		 DECLARE @tablesExpression NVARCHAR(MAX)
		 SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
			JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
					ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
			WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
			AND
			(
				(
					NOT [ic].[TABLE_NAME] LIKE N''Sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
					AND [ic].[COLUMN_NAME] NOT IN (''CreatedOn'',''ModifiedOn'')
					AND NOT [ic].[TABLE_NAME] IN (
							SELECT [s].[Name]
							FROM [' + @DBName + '].[dbo].[SysLookup] [l]
									JOIN [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
									ON [l].[SysEntitySchemaUId] = [s].[UId]
								WHERE  [s].[ManagerName] = ''EntitySchemaManager''
								AND NOT EXISTS(SELECT * FROM [' + @DBName + '].[dbo].[SysModule] [m]
										WHERE [m].[SysModuleEntityId] IN (SELECT [Id] 
																		FROM [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
																		WHERE [SysEntitySchemaUId] = [l].[SysEntitySchemaUId]))
						)
					AND [ic].[TABLE_NAME] IN (
							SELECT [s].[Name]
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
							WHERE [s].[ManagerName] = ''EntitySchemaManager''
							)
					AND NOT EXISTS(
							SELECT 1
							FROM  [' + @DBName + '].sys.computed_columns
								WHERE object_id = OBJECT_ID([ic].[TABLE_NAME]) AND name = [ic].[COLUMN_NAME]
							)
								)
								OR ([ic].[TABLE_NAME] LIKE N''Sys%Log'' AND [ic].[TABLE_NAME] NOT IN (''SysProcessLog'', ''SysProcessElementLog'', ''SysSqlLog''))
								OR [ic].[TABLE_NAME] = N''SysMsgUserSettings''
							)
							AND [COLUMN_NAME] NOT IN (''Code'',''SchemaToSearchName'',''ContainerId'',''ModuleName'', ''ProcessListeners'')
							ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
 
		PRINT (@tablesExpression)
		EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
 
		SET @tablesExpression = 'SELECT [ic].[TABLE_NAME], [ic].[COLUMN_NAME], [ic].[DATA_TYPE]
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[COLUMNS] [ic]
			JOIN [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
				ON [it].[TABLE_NAME] = [ic].[TABLE_NAME] AND [it].[TABLE_TYPE] = ''BASE TABLE''
			WHERE [DATA_TYPE] IN (N''decimal'', N''int'', N''nvarchar'', N''varbinary'', N''date'', N''datetime'', N''datetime2'', N''binary'')
			AND
			(
				(
					NOT [ic].[TABLE_NAME] LIKE N''Sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''sys%''
					AND NOT [ic].[TABLE_NAME] LIKE N''QRTZ%''
					AND NOT [ic].[TABLE_NAME] IN (
							SELECT DISTINCT([s].[Name])
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
							)
				)
			)
			ORDER BY [ic].[TABLE_NAME], [COLUMN_NAME]'
		PRINT (@tablesExpression)
		EXEC tsp_DoDataAnonymization @DBName, @tablesExpression
 
		DECLARE 
			@moduleTablesExpression NVARCHAR(MAX),
			@errorModuleCaptionExpression NVARCHAR(MAX)
 
		SET @moduleTablesExpression = '
			SELECT Distinct([it].[TABLE_NAME])
			FROM [' + @DBName + '].[INFORMATION_SCHEMA].[TABLES] [it]
			WHERE
				[it].[TABLE_TYPE] = ''BASE TABLE'' 
				AND
				(
					(
						NOT [it].[TABLE_NAME] LIKE N''Sys%''
						AND NOT [it].[TABLE_NAME] LIKE N''QRTZ%''
						AND [it].[TABLE_NAME] IN (
							SELECT distinct([s].[Name])
							FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
								JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
									ON 	[sme].[SysEntitySchemaUId] = [s].UId
								JOIN [' + @DBName + '].[dbo].[SysModule] [m]
									ON [m].SysModuleEntityId = [sme].[Id]
							WHERE [s].[ManagerName] = ''EntitySchemaManager''
							)
					)
					OR ([it].[TABLE_NAME] LIKE N''Sys%Log'' AND [it].[TABLE_NAME] NOT IN (''SysProcessLog'', ''SysProcessElementLog'', ''SysSqlLog''))
				)
			ORDER BY [it].[TABLE_NAME]'
 
		SET @errorModuleCaptionExpression = '
			SELECT Top 1 @outModuleName = [m].[Caption]
			FROM [' + @DBName + '].[dbo].[VwSysSchemaInWorkspace] [s]
				JOIN [' + @DBName + '].[dbo].[SysModuleEntity] [sme]
					ON [sme].[SysEntitySchemaUId] = [s].UId
				JOIN [' + @DBName + '].[dbo].[SysModule] [m]
					ON [m].SysModuleEntityId = [sme].[Id]
			WHERE [s].[Name] = '
 
		EXEC tsp_DoAnonymizationCheck @DBName, @moduleTablesExpression, @errorModuleCaptionExpression
 
END
GO
 
IF NOT OBJECT_ID('[dbo].[tsp_AnonymizeAllData]') IS NULL
BEGIN
	DROP PROCEDURE [dbo].[tsp_AnonymizeAllData]
END
GO
 
CREATE PROCEDURE [dbo].[tsp_AnonymizeAllData]
	@DBName SYSNAME
AS
BEGIN 
	SET NOCOUNT ON;
 
	IF (NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBName))
	BEGIN
		RAISERROR (N'Базы данных с таким именем не существует!', 16, 1)
		RETURN 
	END
	IF (PATINDEX('%anonymous', @DBName) = 0 AND (PATINDEX('sbx-%', @DBName) = 0) AND (PATINDEX('upd-%', @DBName) = 0))
	 BEGIN
		RAISERROR (N'Попытка анонимизировать данные из рабочей БД!', 16, 1)
		RETURN 
	 END  
 
	DECLARE @configurationVersion NVARCHAR(MAX)         
 
	DECLARE @sqlSolution NVARCHAR(MAX)
	SET @sqlSolution = N'SELECT TOP 1 @nameOUT = [TextValue]
		FROM [' + @DBName + '].[dbo].[SysSettingsValue]
		WHERE [SysSettingsId] IN (SELECT [Id]
								FROM [' + @DBName + '].[dbo].[SysSettings]
								WHERE [Code] LIKE ''ConfigurationVersion'') 
		ORDER BY [Position]'
	EXEC sp_executesql @sqlSolution, N'@nameOUT NVARCHAR(10) OUTPUT', @nameOUT = @configurationVersion OUTPUT
 
	IF (@configurationVersion = '0.0.0' OR @configurationVersion = '' OR @configurationVersion = NULL)
	BEGIN
		RAISERROR (N'Версия конфигурации невалидна!', 16, 1)
		RETURN 
	END
	ELSE
	BEGIN
		IF (@configurationVersion = '7.0.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData700 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.1.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData710 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.2.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData720 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.2.1') 
		BEGIN
			EXEC tsp_AnonymizeAllData721 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.2.2') 
		BEGIN
			EXEC tsp_AnonymizeAllData720 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.3.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.4.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.4.1') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.5.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.6.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.7.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.8.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.8.1') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.8.2') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.8.3') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.8.4') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.9.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.9.1') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.9.2') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.10.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.10.1') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.10.2') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.10.3') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.11.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.11.1') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.11.2') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.11.3') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.12.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.12.1') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.12.2') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.12.3') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.12.4') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
		IF (@configurationVersion = '7.13.0') 
		BEGIN
			EXEC tsp_AnonymizeAllData730 @DBName
			RETURN
		END
 
		DECLARE @errorMessageUnknownConfigVersion NVARCHAR(MAX)
		SET @errorMessageUnknownConfigVersion = N'Версия конфигурации ' + @configurationVersion + N' не поддерживается!'
		RAISERROR (@errorMessageUnknownConfigVersion, 16, 1)
	END
END
GO

Инструкция:

Для анонимизации базы, необходимо сделать копию базы, назвать ее с приставкой «anonymous», к примеру, mybse_anonymous, выполнить на ней скрипт.
После выполнения скрипта, в базе появится хранимая процедура «tsp_AnonymizeAllData», с единственным параметром «DBName». Ее необходимо выполнить, заполнив этот параметр, один в один с именем этой базы, с которой вы и запускаете хранимую процедуру (к примеру: mybse_anonymous).
После того как отработает хранимая процедура анонимизации базы, в базе (в нашем примере: mybse_anonymous) не останется важных данных.

Для Oracle

/*
** Project: BPMonline
** DBMS   : Oracle
** Type   : Stored Procedure
** Name   : tsp_AnonymizeAllData
*/
 
DECLARE
	existsObject number;
BEGIN
	SELECT COUNT(1) INTO existsObject FROM user_objects WHERE object_name = 'TmpAnonimizationData' AND object_type = 'TABLE';
	IF existsObject > 0 THEN
		EXECUTE IMMEDIATE 'DROP TABLE "TmpAnonimizationData"';
	END IF;
END;
/
 
CREATE GLOBAL TEMPORARY TABLE "TmpAnonimizationData" (
	TABLE_NAME  VARCHAR2(100),
	COLUMN_NAME VARCHAR2(100),
	DATA_TYPE   VARCHAR2(100)
) ON COMMIT PRESERVE ROWS;
/
 
CREATE OR REPLACE PROCEDURE tsp_DoDataAnonymization (schemaName IN varchar2) AS
BEGIN
	DELETE FROM "TmpAnonimizationData" WHERE
		(TABLE_NAME = 'Activity' AND COLUMN_NAME = 'AllowedResult') OR
		(TABLE_NAME = 'ActivityCategory' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ActivityParticipantRole' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ActivityPriority' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ActivityStatus' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ActivityType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'AdditionalParam' AND COLUMN_NAME = 'ColumnCaption') OR
		(TABLE_NAME = 'Amenity' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'AmenitySubtype' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'BulkEmailCategory' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'BulkEmailLaunchOption' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'CallDirection' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'Campaign' AND COLUMN_NAME = 'SchemaData') OR
		(TABLE_NAME = 'CampaignFilter' AND COLUMN_NAME = 'SearchData') OR
		(TABLE_NAME = 'CampaignStepType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'CashflowType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'CashflowType' AND COLUMN_NAME = 'Description') OR
		(TABLE_NAME = 'CaseStatus' AND COLUMN_NAME = 'Color') OR
		(TABLE_NAME = 'CaseStatus' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'CaseStatus' AND COLUMN_NAME = 'ButtonCaption') OR
		(TABLE_NAME = 'Category' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ChartAggregationType' AND COLUMN_NAME = 'AggregationTypeCode') OR
		(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'ChartEntityColumn') OR
		(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'ChartSeriesKind') OR
		(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'Filter') OR
		(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'GroupByField') OR
		(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'GroupByTypeDateTimeId') OR
		(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'ModuleObjAccessField') OR
		(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'OrderByChartPropertyColumn') OR
		(TABLE_NAME = 'ChartProperty' AND COLUMN_NAME = 'OrderDirection') OR
		(TABLE_NAME = 'Command' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'Communication' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ConfigItemCategory' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ConfigItemStatus' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ConfigItemType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ContentBlock' AND COLUMN_NAME = 'Config') OR
		(TABLE_NAME = 'ContextHelp' AND COLUMN_NAME = 'ContextHelpId') OR
		(TABLE_NAME = 'ContextHelp' AND COLUMN_NAME = 'LMSUrl') OR
		(TABLE_NAME = 'ContextHelp' AND COLUMN_NAME = 'ProductEdition') OR
		(TABLE_NAME = 'DataType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'DeadlineCalcSchemas' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'DeadlineCalcSchemas' AND COLUMN_NAME = 'Default') OR
		(TABLE_NAME = 'DeadlineCalcSchemas' AND COLUMN_NAME = 'Handler') OR
		(TABLE_NAME = 'DependencyCategory' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'DependencyType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'DependencyType' AND COLUMN_NAME = 'ReverseTypeName') OR
		(TABLE_NAME = 'Dimension' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'Dimension' AND COLUMN_NAME = 'Path') OR
		(TABLE_NAME = 'DisplayDateFormat' AND COLUMN_NAME = 'Format') OR
		(TABLE_NAME = 'DocumentState' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'DocumentType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'EmailSendStatus' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'EmailTemplateMacros' AND COLUMN_NAME = 'ColumnPath') OR
		(TABLE_NAME = 'EmailTemplateMacros' AND COLUMN_NAME = 'Position') OR
		(TABLE_NAME = 'EmailTemplateMacros' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'EmailType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'EntitySchemaRecRightOperation' AND COLUMN_NAME = 'Description') OR
		(TABLE_NAME = 'EntitySchemaRecRightOperation' AND COLUMN_NAME = 'Value') OR
		(TABLE_NAME = 'ESNNotificationType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ESNNotificationType' AND COLUMN_NAME = 'Action') OR
		(TABLE_NAME = 'ExternalSource' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'FolderType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'Gender' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'GroupByTypeDateTime' AND COLUMN_NAME = 'Description') OR
		(TABLE_NAME = 'GroupByTypeDateTime' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'AmenityType') OR
		(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'ParameterClass') OR
		(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'ParameterDisplayName') OR
		(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'ParameterName') OR
		(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'ParameterProperty') OR
		(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'PropertyPath') OR
		(TABLE_NAME = 'IntegrationParameter' AND COLUMN_NAME = 'SelectedParameter') OR
		(TABLE_NAME = 'IntegrationParamValue' AND COLUMN_NAME = 'AmenityValue') OR
		(TABLE_NAME = 'IntegrationParamValue' AND COLUMN_NAME = 'ParameterDisplayValue') OR
		(TABLE_NAME = 'IntegrationParamValue' AND COLUMN_NAME = 'ParameterValue') OR
		(TABLE_NAME = 'IntegrationSettings' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'IntroPageLookup' AND COLUMN_NAME = 'CodePage') OR
		(TABLE_NAME = 'IntroPageLookup' AND COLUMN_NAME = 'VideoUrl') OR
		(TABLE_NAME = 'IntroPageLookup' AND COLUMN_NAME = 'VideoCaption') OR
		(TABLE_NAME = 'IntroPageLookup' AND COLUMN_NAME = 'AcademyUrl') OR
		(TABLE_NAME = 'KnowledgeBaseType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'LDAPAuthType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'LDAPAuthType' AND COLUMN_NAME = 'Value') OR
		(TABLE_NAME = 'LeadDisqualifyReason' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'LeadMedium' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'LeadRegisterMethod' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'LeadType' AND COLUMN_NAME = 'Description') OR
		(TABLE_NAME = 'LeadType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'LeadStage' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'LeadTypeStatus' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'LendingState' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ListingType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'Listing' AND COLUMN_NAME = 'Latitude') OR
		(TABLE_NAME = 'Listing' AND COLUMN_NAME = 'Longitude') OR
		(TABLE_NAME = 'Lookup' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'LookupFolder' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'Macros' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'Address') OR
		(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'Port') OR
		(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'UseSSL') OR
		(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'UseSSLforSending') OR
		(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'SMTPServerAddress') OR
		(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'SMTPPort') OR
		(TABLE_NAME = 'MailServer' AND COLUMN_NAME = 'ExchangeEmailAddress') OR
		(TABLE_NAME = 'MailServerType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'MailSyncPeriod' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'MainParam' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'MessageListener' AND COLUMN_NAME = 'ClassName') OR
		(TABLE_NAME = 'MessagePublisher' AND COLUMN_NAME = 'ClassName') OR
		(TABLE_NAME = 'MessageNotifier' AND COLUMN_NAME = 'ClassName') OR
		(TABLE_NAME = 'OAuthApplications' AND COLUMN_NAME = 'AppClassName') OR
		(TABLE_NAME = 'OAuthApplications' AND COLUMN_NAME = 'ClientId') OR
		(TABLE_NAME = 'OAuthApplications' AND COLUMN_NAME = 'ClientClassName') OR
		(TABLE_NAME = 'OAuthApplications' AND COLUMN_NAME = 'SecretKey') OR
		(TABLE_NAME = 'MLModel' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'MLModel' AND COLUMN_NAME = 'MetaData') OR
		(TABLE_NAME = 'MLModel' AND COLUMN_NAME = 'TrainingSetQuery') OR
		(TABLE_NAME = 'MLModel' AND COLUMN_NAME = 'BatchPredictionQuery') OR
		(TABLE_NAME = 'MLModelState' AND COLUMN_NAME = 'Code') OR
		(TABLE_NAME = 'MLPrediction' AND COLUMN_NAME = 'Value') OR
		(TABLE_NAME = 'MLPrediction' AND COLUMN_NAME = 'Significance') OR
		(TABLE_NAME = 'MLProblemType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'MLProblemType' AND COLUMN_NAME = 'ServiceUrl') OR
		(TABLE_NAME = 'MLProblemType' AND COLUMN_NAME = 'TrainingEndpoint') OR
		(TABLE_NAME = 'MLProblemType' AND COLUMN_NAME = 'PredictionEndpoint') OR
		(TABLE_NAME = 'MLTrainSession' AND COLUMN_NAME = 'Error') OR
		(TABLE_NAME = 'NotificationProvider' AND COLUMN_NAME = 'ClassName') OR
		(TABLE_NAME = 'NotificationProvider' AND COLUMN_NAME = 'Type') OR
		(TABLE_NAME = 'ParamMatchingType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'Period' AND COLUMN_NAME = 'DueDate') OR
		(TABLE_NAME = 'Period' AND COLUMN_NAME = 'StartDate') OR
		(TABLE_NAME = 'PeriodType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ProblemPriority' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ProblemStatus' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ProblemType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ProcessSchemaType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ProjectEntryType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'Property' AND COLUMN_NAME = 'Latitude') OR
		(TABLE_NAME = 'Property' AND COLUMN_NAME = 'Longitude') OR
		(TABLE_NAME = 'QualifyStatus' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'QualifyStatus' AND COLUMN_NAME = 'StageNumber') OR
		(TABLE_NAME = 'QualifyStatus' AND COLUMN_NAME = 'StageOrder') OR
		(TABLE_NAME = 'QualifyStatus' AND COLUMN_NAME = 'StageInnerOrder') OR
		(TABLE_NAME = 'QualifyStatus' AND COLUMN_NAME = 'IsFinal') OR
		(TABLE_NAME = 'QuickAddMenuItem' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ReleasePriority' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ReleaseStatus' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ReleaseType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'RemindInterval' AND COLUMN_NAME = 'Description') OR
		(TABLE_NAME = 'RemindInterval' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'RemindInterval' AND COLUMN_NAME = 'Value') OR
		(TABLE_NAME = 'RequestType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'RoleInServiceTeam' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ServiceCategory' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ServiceObjectType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ServiceStatus' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'Status' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'TimeUnit' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'TimeZone' AND COLUMN_NAME = 'Offset') OR
		(TABLE_NAME = 'TimeZone' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'DayOfWeek' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'DayOfWeek' AND COLUMN_NAME = 'Number') OR
		(TABLE_NAME = 'LMColumnTemplate' AND COLUMN_NAME = 'ColumnName') OR
		(TABLE_NAME = 'LMStartEvent' AND COLUMN_NAME = 'ConditionData') OR
		(TABLE_NAME = 'LMStartEvent' AND COLUMN_NAME = 'ChangedColumns') OR
		(TABLE_NAME = 'LMStartEvent' AND COLUMN_NAME = 'MessageTemplate') OR
		(TABLE_NAME = 'ListenerByNotifier' AND COLUMN_NAME = 'NotifierConnectionColumn') OR
		(TABLE_NAME = 'DisplayDateFormat' AND COLUMN_NAME = 'Format') OR
		(TABLE_NAME = 'SysProcessLog' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'SysProcessLog' AND COLUMN_NAME = 'DurationInMinutes') OR
		(TABLE_NAME = 'SysProcessLog' AND COLUMN_NAME = 'DurationInHours') OR
		(TABLE_NAME = 'SysProcessLog' AND COLUMN_NAME = 'DurationInDays') OR
		(TABLE_NAME = 'SysProcessElementLog' AND COLUMN_NAME = 'Type') OR
		(TABLE_NAME = 'SysProcessElementLog' AND COLUMN_NAME = 'Caption') OR
		(TABLE_NAME = 'SysProcessElementLog' AND COLUMN_NAME = 'ErrorDescription') OR
		(TABLE_NAME = 'SysProcessElementLog' AND COLUMN_NAME = 'DurationInMinutes') OR
		(TABLE_NAME = 'SysProcessElementLog' AND COLUMN_NAME = 'DurationInHours') OR
		(TABLE_NAME = 'SysProcessElementLog' AND COLUMN_NAME = 'DurationInDays') OR
		(TABLE_NAME = 'FileType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'BulkEmailStatus' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'BulkEmailStatus' AND COLUMN_NAME = 'IsActive') OR
		(TABLE_NAME = 'LeadSource' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'LeadSource' AND COLUMN_NAME = 'Description') OR
		(TABLE_NAME = 'Country' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'Country' AND COLUMN_NAME = 'Image') OR
		(TABLE_NAME = 'Country' AND COLUMN_NAME = 'BilingInfo') OR
		(TABLE_NAME = 'QueueStatus' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'QueueStatus' AND COLUMN_NAME = 'IsInitial') OR
		(TABLE_NAME = 'QueueStatus' AND COLUMN_NAME = 'IsFinal') OR
		(TABLE_NAME = 'City' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'Region' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'QueuePriority' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'QueuePriority' AND COLUMN_NAME = 'Value') OR
		(TABLE_NAME = 'Calendar' AND COLUMN_NAME = 'Name') OR	
		(TABLE_NAME = 'ContractState' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'ContractState' AND COLUMN_NAME = 'Position') OR
		(TABLE_NAME = 'Tax' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'Tax' AND COLUMN_NAME = 'Percent') OR
		(TABLE_NAME = 'AddressType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'AddressType' AND COLUMN_NAME = 'ForContact') OR
		(TABLE_NAME = 'AddressType' AND COLUMN_NAME = 'ForAccount') OR
		(TABLE_NAME = 'RelationType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'RelationType' AND COLUMN_NAME = 'ForContactContact') OR
		(TABLE_NAME = 'RelationType' AND COLUMN_NAME = 'ForAccountContact') OR
		(TABLE_NAME = 'RelationType' AND COLUMN_NAME = 'ForContactAccount') OR
		(TABLE_NAME = 'RelationType' AND COLUMN_NAME = 'ForAccountAccount') OR
		(TABLE_NAME = 'SpecificationType' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'SpecificationType' AND COLUMN_NAME = 'Description') OR
		(TABLE_NAME = 'Unit' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'Unit' AND COLUMN_NAME = 'ShortName') OR
		(TABLE_NAME = 'QualifyStatus' AND COLUMN_NAME = 'Color') OR
		(TABLE_NAME = 'OpportunityStage' AND COLUMN_NAME = 'Color') OR
		(TABLE_NAME = 'QueueObjectColumn' AND COLUMN_NAME = 'Name') OR
		(TABLE_NAME = 'QueueObjectColumn' AND COLUMN_NAME = 'Caption') OR
		(TABLE_NAME = 'DuplicatesHistory' AND COLUMN_NAME = 'SchemaTableName');
 
	UPDATE "SysAdminUnit"
	SET "Name" = 'Supervisor_Current' 
	WHERE "Name" = 'Supervisor';
 
	UPDATE "SysAdminUnit"
	SET "Name" = 'Supervisor',
		"UserPassword" = 'jqtZ3uiTDazZFoZRF32m0OQpxrt4V45R7yYXgd1jl6xddsoOOIwja', 
		"Active" = '1' 
	WHERE "Id" = '{7F3B869F-34F3-4F20-AB4D-7480A5FDF647}';
 
	UPDATE "SysAdminUnit" 
	SET "Name" = 'Name '|| Rownum 
	WHERE "SysAdminUnitTypeValue" < 4;
 
	--DBMS_OUTPUT.ENABLE(200000);
 
	DECLARE
		CURSOR cur IS 
			SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
			FROM "TmpAnonimizationData";
		expression VARCHAR2(500);
	BEGIN
		FOR cur_rec in cur
		LOOP
			BEGIN
				execute immediate 'ALTER TABLE "' || cur_rec.TABLE_NAME || '" DISABLE ALL TRIGGERS';
				expression := 'UPDATE "' || cur_rec.TABLE_NAME || '" ';
				expression := expression || ' SET "' || cur_rec.COLUMN_NAME || '" = ';
				IF ((cur_rec.TABLE_NAME = 'MailboxSynchronizationSettings') AND (cur_rec.COLUMN_NAME = 'UserPassword')) THEN
					expression := expression || '''p0mzIa0Nfdo='' WHERE "' || cur_rec.COLUMN_NAME || '" IS NOT NULL';
				 ELSIF ((cur_rec.TABLE_NAME = 'SysMsgUserSettings') AND (cur_rec.COLUMN_NAME = 'ConnectionParams')) THEN
					expression := expression || 'REPLACE("ConnectionParams", ''"disableCallCentre":false'', ''"disableCallCentre":true'')';
				ELSIF ((cur_rec.DATA_TYPE = 'NVARCHAR2') OR (cur_rec.DATA_TYPE = 'VARCHAR2')) THEN
					expression := expression || '''Name'' || SUBSTR("Id", 2, 8) WHERE "' || cur_rec.COLUMN_NAME || '" IS NOT NULL';
					expression := expression || ' AND NOT (SUBSTR("' || cur_rec.COLUMN_NAME ||'",1,1) =''{'' ';
					expression := expression || ' AND LENGTH("' || cur_rec.COLUMN_NAME ||'") =38 ) ';
				ELSIF ( cur_rec.DATA_TYPE = 'NCLOB') THEN
					expression := expression || '''DataName'' WHERE "' || cur_rec.COLUMN_NAME || '" IS NOT NULL';
				ELSIF ((cur_rec.DATA_TYPE = 'DATE') OR (cur_rec.DATA_TYPE = 'TIMESTAMP')) THEN
					expression := expression || '(SELECT sysdate + (SELECT CEIL(150*dbms_random.value(1,10)) from dual) from dual)' ;
					expression := expression || ' WHERE "' || cur_rec.COLUMN_NAME || '" IS NOT NULL';
				ELSIF (cur_rec.DATA_TYPE = 'NUMBER') THEN
					expression := expression || '0 WHERE "' || cur_rec.COLUMN_NAME || '" IS NOT NULL';
				ELSE
					CONTINUE;
				END IF;
				--dbms_output.put_line(expression);
				execute immediate(expression);
				execute immediate 'ALTER TABLE "' || cur_rec.TABLE_NAME || '" ENABLE ALL TRIGGERS';
				COMMIT;
			END;
		END LOOP;
	END;
END;
/
 
CREATE OR REPLACE PROCEDURE tsp_AnonymizeAllData AS
	schemaName VARCHAR(100);
	ex_custom EXCEPTION;
	baseLookupSchemaUId VARCHAR2(38) := '{11AB4BCB-9B23-4B6D-9C86-520FAE925D75}';
	baseCodeLookupSchemaUId VARCHAR2(38) := '{2681062B-DF59-4E52-89ED-F9B7DC909AB2}';
BEGIN
	SELECT SYS_CONTEXT('userenv', 'current_schema') INTO schemaName from DUAL WHERE ROWNUM=1;
 
	BEGIN
		IF regexp_instr(schemaName,'_ANONYMOUS') = 0 THEN
			raise_application_error(-20001, 'Attempt to anonymize the data from the working database!!');
		END IF;
	EXCEPTION
		WHEN ex_custom THEN
		DBMS_OUTPUT.PUT_LINE(sqlerrm);
		DBMS_OUTPUT.PUT_LINE(schemaName);
	END;
 
	DELETE FROM "TmpAnonimizationData";
 
	INSERT INTO "TmpAnonimizationData" (TABLE_NAME,COLUMN_NAME,DATA_TYPE)
	SELECT TAB_COLS.TABLE_NAME, COLUMN_NAME, DATA_TYPE
	FROM "ALL_TAB_COLS" TAB_COLS
	INNER JOIN "ALL_TABLES" TABLES ON TAB_COLS.TABLE_NAME=TABLES.TABLE_NAME AND TABLES.OWNER = schemaName
	INNER JOIN (SELECT distinct "Name"
		FROM "SysSchema"
		WHERE "ManagerName" = 'EntitySchemaManager') SCHEMA ON SCHEMA."Name" = TABLES.TABLE_NAME
	WHERE TAB_COLS.OWNER = schemaName
		AND TABLES.TABLE_NAME NOT LIKE 'Vw%'
		AND TAB_COLS.DATA_TYPE IN ('NUMBER', 'NVARCHAR2', 'VARCHAR2', 'DATE', 'TIMESTAMP', 'RAW', 'NCLOB')
		AND TAB_COLS.COLUMN_ID IS NOT NULL
		AND TAB_COLS.DATA_LENGTH <> 38
		AND TAB_COLS.VIRTUAL_COLUMN <> 'YES'
		AND (TABLES.TABLE_NAME NOT LIKE 'Sys%'
			OR TABLES."TABLE_NAME" = 'SysMsgUserSettings')
		AND TABLES.TABLE_NAME NOT LIKE 'QRTZ%'
		AND TABLES.TABLE_NAME NOT IN ('OPPEmailInitialStatus')
		AND NOT TABLES.TABLE_NAME IN (
			SELECT "SysSchema"."Name"
			FROM "SysLookup"
			JOIN "SysSchema" ON "SysLookup"."SysEntitySchemaUId" = "SysSchema"."UId"
			WHERE "SysSchema"."ManagerName" = 'EntitySchemaManager'
				AND NOT EXISTS (
					SELECT 1 
					FROM "SysModule"
					INNER JOIN "SysModuleEntity" ON "SysModuleEntity"."Id" = "SysModule"."SysModuleEntityId"
					WHERE "SysModuleEntity"."SysEntitySchemaUId" = "SysLookup"."SysEntitySchemaUId"))
	AND "COLUMN_NAME" NOT IN('CreatedOn', 'ModifiedOn', 'Code','SchemaToSearchName', 'ContainerId', 'ModuleName', 'ProcessListeners');
 
	DELETE FROM "TmpAnonimizationData"
	WHERE COLUMN_NAME = 'Name'
		AND EXISTS (SELECT 1 FROM "SysSchema" 
			WHERE "SysSchema"."Name" = TABLE_NAME
			AND "ParentId" IN (
			SELECT "Id" FROM "SysSchema" 
			WHERE "UId" IN(baseLookupSchemaUId,baseCodeLookupSchemaUId)));
 
	tsp_DoDataAnonymization(schemaName);
 
END;
/

Инструкция:

Для работы со скриптом необходимо:

  1. Выполнить бэкап БД
  2. Выполнить импорт в новую схему с именем “имя старой схемы_ANONYMOUS”
  3. Выполнить скрипт tsp_AnonymizeAllData.sql создания процедур анонимизации БД.
  4. Запустить на выполнение процедуру
EXEC tsp_AnonymizeAllData

 

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Вопрос

В таблице SysModule есть такие поля:
- HasAnalytics
- HasAction
- HasRecent
- HelpContextId
Уточните, пожалуйста, для чего они используются и в каких случаях содержат значения равные true или просто заполнены (для не булевого  поля [HelpContextId])?

и для SysModuleEdit

  • UseModuleDetails 
  • SearchRowSchemaUId 

Ответ

Данные колонки предназначены для версии 5.х и в на данный момент они сейчас не используются, так как используется новый интерфейс.
 
Короткое описание:
- HasAnalytics - содержит аналитику
- HasAction - содержит процессы
- HasRecent - содержит группу Недавние
- HelpContextId - идентификатор контекстной справки
 
Хотелось бы отметить, что «SysModule» является системной таблицей и не рекомендуем ее использовать для своих целей и завязываться на базовых колонках, так как в дальнейшем может поменяться структура БД.

Также По порядку:

  1. SearchRowSchemaUId – задается Uid схемы сущности (см. «SysSchema»), которая используется при глобальном поиске (например, «OpportunitySearchRowSchema»). Т.е.  после ввода в командной строке схема, которая отображает информацию одной записи объекта (наглядный скриншот)

    2. UseModuleDetails – используется только для 5.х разделов. Выдержка из документации 5.х версий - [Добавить в карточку детали раздела] — установите признак, если карточка записи должна содержать все детали раздела, например, как в карточке контрагента, контакта и т.д. При установке признака, на деталь [Детали] карточки будут добавлены все детали, зарегистрированные для данного раздела.

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать
Публикация

Кейс

Очень часто при решении разного рода проблем, связанных с бизнес-логикой, приходится сталкиваться с задачей получения значений разного рода таблиц. Некоторые данные можно вывести в реестр, но не все.

Цель

Мгновенно получить необходимую выборку данных, имея перед глазами только сайт с открытой консолью браузера.

Выполнение

Пример скрипта ниже выведет в консоли браузера все идентификаторы статусов лида, а также их названия:

var esq = this.Ext.create("Terrasoft.EntitySchemaQuery"{
    rootSchemaName: "LeadStatus"
});
esq.addColumn("Id");
esq.addColumn("Name");
esq.getEntityCollection(function(result){ 
    if (result.success) {
        result.collection.each(function(item) {
            console.log(item.get("Id")+ ' -' + item.get("Name"));
        });
    }
}this);

LeadStatus - название объекта (таблицы), запрос на выборку из которой предполагается выполнить.
esq.addColumn("Id")esq.addColumn("Name") - добавление определенных колонок в запрос.
console.log(item.get("Id")+ ' - ' + item.get("Name")) - вывод в консоль браузера результирующей информации и ее форматирование

Пример запроса с параметром фильтрации:

var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
    rootSchemaName: "Product"
}); 
esq.addColumn("TradeMark"); 
esq.addColumn("Usrrazdkat02");
var esqFilter = esq.createColumnFilterWithParameter(this.Terrasoft.ComparisonType.EQUAL, "Name", "133.024");
esq.filters.add(esqFilter);
esq.getEntityCollection(function(result) { 
    if (result.success) {
        result.collection.each(function(item) {
            console.log(item.get("TradeMark")+ ' - ' + item.get("Usrrazdkat02"));
        });
    }
}, this);

 

Поделиться

0 комментариев
Войдите или зарегистрируйтесь, чтобы комментировать