INSERT INTO dbo._SystemSettings (
    _UserId,
    _ObjectKey,
    _SettingsKey,
    _Version,
    _SettingsPresentation,
    _SettingsData,
    _DataSeparationUse16249,
    _DataSeparationUse16250,
    _Fld2293,
    _Fld774) 
VALUES(@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10) 
                

-- Удаление всех представлений
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

-- Удаление всех таблиц
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
                

-- !!! Эти параметры настраиваются перед запуском !!!
-- Имя оригинальной базы данных (реплики)
DECLARE @originalDatabaseName sysname = 'BSL-ORIG';
-- Имя адаптированной базы данных, где нужно создать объекты
DECLARE @adaptiveDatabaseName sysname = 'BSL-COPY';

DECLARE @tableName sysname;
DECLARE tables_cursor CURSOR  
FOR SELECT
	[name]
FROM SYSOBJECTS
WHERE xtype = 'U'
	-- Список обрабатываемых таблиц
	AND [name] IN (
		'V8Users',
		'DBSchema',
		'SchemaStorage',
		'Params',
		'Files',
		'_FrmDtSettings',
		'DepotFiles',
		'IBVersion',
		'Config',
		'ConfigCAS',
		'ConfigCASSave',
		'ConfigSave',
		'_UsersWorkHistory',
		'_SystemSettings',
		'_DynListSettings',
		'_CommonSettings',
		'_ExtensionsInfo',
		'_ExtensionsInfoNGS',
		'_ExtensionsRestruct',
		'_ExtensionsRestructNGS',
		'_YearOffset'
	);
OPEN tables_cursor;

FETCH NEXT FROM tables_cursor INTO @tableName;

WHILE @@FETCH_STATUS = 0  
BEGIN
	DECLARE @tableNameFull SYSNAME
	SELECT @tableNameFull = 'dbo.' + @tableName

	DECLARE 
		  @object_name SYSNAME
		, @object_id INT

	SELECT 
		  @object_name = '[' + s.name + '].[' + o.name + ']'
		, @object_id = o.[object_id]
	FROM sys.objects o WITH (NOWAIT)
	JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
	WHERE s.name + '.' + o.name = @tableNameFull
		AND o.[type] = 'U'
		AND o.is_ms_shipped = 0;

	DECLARE @SQL NVARCHAR(MAX) = '';	
	DECLARE @SQLTRANSFER NVARCHAR(MAX) = '';	
	

	WITH index_column AS 
	(
		SELECT 
			  ic.[object_id]
			, ic.index_id
			, ic.is_descending_key
			, ic.is_included_column
			, c.name
		FROM sys.index_columns ic WITH (NOWAIT)
		JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
		WHERE ic.[object_id] = @object_id
	),
	fk_columns AS 
	(
		 SELECT 
			  k.constraint_object_id
			, cname = c.name
			, rcname = rc.name
		FROM sys.foreign_key_columns k WITH (NOWAIT)
		JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id 
		JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
		WHERE k.parent_object_id = @object_id
	)
	SELECT @SQL = 'USE [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '];

	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name=''' + CAST(@tableNameFull as nvarchar(max)) + ''' AND xtype=''U'')
	BEGIN
		DROP TABLE [dbo].[' + CAST(@tablename as nvarchar(max)) + '];
	END
		CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
		SELECT CHAR(9) + ', [' + c.name + '] ' + 
			CASE WHEN c.is_computed = 1
				THEN 'AS ' + cc.[definition] 
				ELSE UPPER(tp.name) + 
					CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
						   THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
						 WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
						   THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
						 WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') 
						   THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
						 WHEN tp.name = 'decimal' 
						   THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
						ELSE ''
					END +
					CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
					CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
					CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END + 
					CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END 
			END + CHAR(13)
		FROM sys.columns c WITH (NOWAIT)
		JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
		LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
		LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
		LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
		WHERE c.[object_id] = @object_id
		ORDER BY c.column_id
		FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
		+ ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' + 
						(SELECT STUFF((
							 SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
							 FROM sys.index_columns ic WITH (NOWAIT)
							 JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
							 WHERE ic.is_included_column = 0
								 AND ic.[object_id] = k.parent_object_id 
								 AND ic.index_id = k.unique_index_id     
							 FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
				+ ')' + CHAR(13)
				FROM sys.key_constraints k WITH (NOWAIT)
				WHERE k.parent_object_id = @object_id 
					AND k.[type] = 'PK'), '') + ')'  + CHAR(13)
		+ ISNULL((SELECT (
			SELECT CHAR(13) +
				 'ALTER TABLE ' + @object_name + ' WITH' 
				+ CASE WHEN fk.is_not_trusted = 1 
					THEN ' NOCHECK' 
					ELSE ' CHECK' 
				  END + 
				  ' ADD CONSTRAINT [' + fk.name  + '] FOREIGN KEY(' 
				  + STUFF((
					SELECT ', [' + k.cname + ']'
					FROM fk_columns k
					WHERE k.constraint_object_id = fk.[object_id]
					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
				   + ')' +
				  ' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
				  + STUFF((
					SELECT ', [' + k.rcname + ']'
					FROM fk_columns k
					WHERE k.constraint_object_id = fk.[object_id]
					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
				   + ')'
				+ CASE 
					WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE' 
					WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
					WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT' 
					ELSE '' 
				  END
				+ CASE 
					WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
					WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
					WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'  
					ELSE '' 
				  END 
				+ CHAR(13) + 'ALTER TABLE ' + @object_name + ' CHECK CONSTRAINT [' + fk.name  + ']' + CHAR(13)
			FROM sys.foreign_keys fk WITH (NOWAIT)
			JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
			WHERE fk.parent_object_id = @object_id
			FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
		+ ISNULL(((SELECT
			 CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END 
					+ ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @object_name + ' (' +
					STUFF((
					SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
					FROM index_column c
					WHERE c.is_included_column = 0
						AND c.index_id = i.index_id
					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'  
					+ ISNULL(CHAR(13) + 'INCLUDE (' + 
						STUFF((
						SELECT ', [' + c.name + ']'
						FROM index_column c
						WHERE c.is_included_column = 1
							AND c.index_id = i.index_id
						FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '')  + CHAR(13)
			FROM sys.indexes i WITH (NOWAIT)
			WHERE i.[object_id] = @object_id
				AND i.is_primary_key = 0
				AND i.[type] = 2
			FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
		), '')



	SELECT
		@SQLTRANSFER = 
	N'USE [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '];

	DELETE FROM [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '].[dbo].[' + CAST(@tablename as nvarchar(max)) + '];

	INSERT INTO [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '].[dbo].[' + CAST(@tablename as nvarchar(max)) + '] 
	(' + 
	STUFF(
	(SELECT N',' + c.name
	FROM
		sys.columns AS c
		INNER JOIN sys.types tp ON tp.system_type_id = c.system_type_id AND tp.user_type_id = c.user_type_id
	WHERE 
		c.OBJECT_ID = OBJECT_ID(@tableNameFull)
		AND NOT tp.name = 'timestamp'
	ORDER BY
		column_id
	FOR XML PATH(''), TYPE).value('.',N'nvarchar(max)')
	,1,1,N'')
	+ N') 
	SELECT 
	' + 
	STUFF(
	(SELECT N',' + c.name
	FROM
		sys.columns AS c
		INNER JOIN sys.types tp ON tp.system_type_id = c.system_type_id AND tp.user_type_id = c.user_type_id
	WHERE 
		c.OBJECT_ID = OBJECT_ID(@tableNameFull)
		AND NOT tp.name = 'timestamp'
	ORDER BY
		column_id
	FOR XML PATH(''), TYPE).value('.',N'nvarchar(max)')
	,1,1,N'')
	+ N'
	FROM [' + CAST(@originalDatabaseName as nvarchar(max)) + '].[dbo].[' + CAST(@tablename as nvarchar(max)) + '];

	';

	-- Выполняем команды создания объектов в контексте адаптированной базы данных
	EXECUTE sp_executesql @SQL
	-- Затем переносим данные из исходной таблицы в созданную
	EXECUTE sp_executesql @SQLTRANSFER

	FETCH NEXT FROM tables_cursor INTO @tableName;
END
CLOSE tables_cursor;  
DEALLOCATE tables_cursor;
                

-- !!! Эти параметры настраиваются перед запуском !!!
-- Имя оригинальной базы данных (реплики)
DECLARE @originalDatabaseName sysname = 'BSL-ORIG';
-- Имя адаптированной базы данных, где нужно создать объекты
DECLARE @adaptiveDatabaseName sysname = 'BSL-COPY';

DECLARE @dropView nvarchar(max);
DECLARE @createView nvarchar(max);
DECLARE @createTriggerInsteadOfInsert nvarchar(max);
DECLARE @createTriggerInsteadOfUpdate nvarchar(max);
DECLARE @createTriggerInsteadOfDelete nvarchar(max);
DECLARE @dropTriggerInsteadOfInsert nvarchar(max);
DECLARE @dropTriggerInsteadOfUpdate nvarchar(max);
DECLARE @dropTriggerInsteadOfDelete nvarchar(max);

DECLARE cmds CURSOR FOR
SELECT
-- Удаление существующих представлений
N'USE [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '];
IF EXISTS(
	SELECT 1 FROM sys.objects 
	WHERE name = N''' + t.name + N''' AND type = N''V'') 
BEGIN 
	DROP VIEW [' + t.name + '] 
END' AS [DropViewIfExistCommand],

-- Создание представления
N'CREATE VIEW [' + t.name + N'] 
AS 
SELECT 
	' + 
STUFF(
(SELECT N',' + c.name
FROM
	sys.columns AS c
WHERE 
	c.OBJECT_ID = t.OBJECT_ID
ORDER BY
	column_id
FOR XML PATH(''), TYPE).value('.',N'nvarchar(max)')
,1,1,N'')
+ N' 
FROM [' + CAST(@originalDatabaseName as nvarchar(max)) + '].[dbo].[' + t.name + N'];
' AS [CreateViewCommand],

-- Создание триггера запрета вставки значений
N'CREATE TRIGGER [dbo].[read_only_' + t.name + N'_insert]
   ON  [dbo].[' + t.name + N']
   INSTEAD OF INSERT
AS 
BEGIN
	SET NOCOUNT ON;
	
    -- При добавлении ничего не выполняем
END
' AS [CreateTriggerBreakInsertCommand],

-- Удалить триггер запрета вставки значений, если существует
N'DROP TRIGGER IF EXISTS [read_only_' + t.name + N'_insert];' AS [DropTriggerIfExistBreakInsertCommand],

-- Создание триггера запрета обновления значений
N'CREATE TRIGGER [dbo].[read_only_' + t.name + N'_update]
   ON  [dbo].[' + t.name + N']
   INSTEAD OF UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

    -- При обновлении ничего не выполняем
END
' AS [CreateTriggerBreakUpdateCommand],

-- Удалить триггер запрета вставки значений, если существует
N'USE [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '];
DROP TRIGGER IF EXISTS [read_only_' + t.name + N'_update];' AS [DropTriggerIfExistBreakUpdateCommand],

-- Создание триггера запрета удаления значений
N'CREATE TRIGGER [dbo].[read_only_' + t.name + N'_delete]
   ON  [dbo].[' + t.name + N']
   INSTEAD OF DELETE
AS 
BEGIN
	SET NOCOUNT ON;

    -- При удалении ничего не выполняем
END
' AS [CreateTriggerBreakDeleteCommand],

-- Удалить триггер запрета вставки значений, если существует
N'DROP TRIGGER IF EXISTS [read_only_' + t.name + N'_delete];' AS [DropTriggerIfExistBreakDeleteCommand]

FROM sys.tables AS t
-- Исключаем список таблиц, которые были перенесены "как есть"
WHERE NOT t.[name] IN (
		'V8Users',
		'DBSchema',
		'SchemaStorage',
		'Params',
		'Files',
		'_FrmDtSettings',
		'DepotFiles',
		'IBVersion',
		'Config',
		'ConfigCAS',
		'ConfigCASSave',
		'ConfigSave',
		'_UsersWorkHistory',
		'_SystemSettings',
		'_DynListSettings',
		'_CommonSettings',
		'_ExtensionsInfo',
		'_ExtensionsInfoNGS',
		'_ExtensionsRestruct',
		'_ExtensionsRestructNGS',
		'_YearOffset'
);
OPEN cmds;

FETCH NEXT FROM cmds INTO @dropView, @createView, 
	@createTriggerInsteadOfInsert, @dropTriggerInsteadOfInsert,
	@createTriggerInsteadOfUpdate, @dropTriggerInsteadOfUpdate,
	@createTriggerInsteadOfDelete, @dropTriggerInsteadOfDelete;

WHILE @@FETCH_STATUS = 0  
BEGIN
	
	declare @currentCmd nvarchar(max);

	-- Удаление существующего представления
	SET @currentCmd =
'declare @textCmd nvarchar(max) = CAST('' ' + CAST(REPLACE(@dropView,'''','''''') as nvarchar(max)) + ' '' as nvarchar(max));
EXECUTE [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '].[sys].sp_executesql @textCmd'
	EXECUTE sp_executesql @currentCmd

	-- Создание представления
	SET @currentCmd =
'declare @textCmd nvarchar(max) = CAST('' ' + CAST(REPLACE(@createView,'''','''''') as nvarchar(max)) + ' '' as nvarchar(max));
EXECUTE [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '].[sys].sp_executesql @textCmd'
	EXECUTE sp_executesql @currentCmd

	-- Создание защиты от вставки новых значений
	SET @currentCmd =
'declare @textCmd nvarchar(max) = CAST('' ' + CAST(REPLACE(@dropTriggerInsteadOfInsert,'''','''''') as nvarchar(max)) + ' '' as nvarchar(max));
EXECUTE [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '].[sys].sp_executesql @textCmd'
	EXECUTE sp_executesql @currentCmd

	SET @currentCmd =
'declare @textCmd nvarchar(max) = CAST('' ' + CAST(REPLACE(@createTriggerInsteadOfInsert,'''','''''') as nvarchar(max)) + ' '' as nvarchar(max));
EXECUTE [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '].[sys].sp_executesql @textCmd'
	EXECUTE sp_executesql @currentCmd
	
	-- Создание защиты от изменения данных
	SET @currentCmd =
'declare @textCmd nvarchar(max) = CAST('' ' + CAST(REPLACE(@dropTriggerInsteadOfUpdate,'''','''''') as nvarchar(max)) + ' '' as nvarchar(max));
EXECUTE [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '].[sys].sp_executesql @textCmd'
	EXECUTE sp_executesql @currentCmd

	SET @currentCmd =
'declare @textCmd nvarchar(max) = CAST('' ' + CAST(REPLACE(@createTriggerInsteadOfUpdate,'''','''''') as nvarchar(max)) + ' '' as nvarchar(max));
EXECUTE [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '].[sys].sp_executesql @textCmd'
	EXECUTE sp_executesql @currentCmd

	-- Создание защиты от удаления данных
	SET @currentCmd =
'declare @textCmd nvarchar(max) = CAST('' ' + CAST(REPLACE(@dropTriggerInsteadOfDelete,'''','''''') as nvarchar(max)) + ' '' as nvarchar(max));
EXECUTE [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '].[sys].sp_executesql @textCmd'
	EXECUTE sp_executesql @currentCmd

	SET @currentCmd =
'declare @textCmd nvarchar(max) = CAST('' ' + CAST(REPLACE(@createTriggerInsteadOfDelete,'''','''''') as nvarchar(max)) + ' '' as nvarchar(max));
EXECUTE [' + CAST(@adaptiveDatabaseName as nvarchar(max)) + '].[sys].sp_executesql @textCmd'
	EXECUTE sp_executesql @currentCmd

	FETCH NEXT FROM cmds INTO @dropView, @createView, 
		@createTriggerInsteadOfInsert, @dropTriggerInsteadOfInsert,
		@createTriggerInsteadOfUpdate, @dropTriggerInsteadOfUpdate,
		@createTriggerInsteadOfDelete, @dropTriggerInsteadOfDelete;
END
CLOSE cmds;  
DEALLOCATE cmds;