Скрипт обезличивания/анонимизации базы

Для 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