IF OBJECT_ID('dbo.ChangePrimaryKeyGuidToIntProcedure') IS NOT NULL 
	DROP PROCEDURE [dbo].[ChangePrimaryKeyGuidToIntProcedure]
GO

CREATE PROCEDURE dbo.ChangePrimaryKeyGuidToIntProcedure
		@BaseEntityName varchar(128), -- имя базовой таблицы c PK тип которого нужно поменять
		@EntitySchemaName varchar(128), -- схема базовой таблицы
		@WithExecute bit, -- если 1, то запросы будут выполнены, иначе просто выведется весь скрипт для изменения
		@WithRollback bit, 
		@OrderByColumnName varchar(128), -- колонка, по которой сортировать проставления инта (например у T_VISIT по дате посещения)
		@ColumnsWithoutForeignKeys varchar(2048), -- строка в виде 'AKUZ.T_MEDICAL_RECORD.Ehr123|AKUZ.T_AMBULANCE_RECORD.Ehr123' с перечислением колонок, которые смотрят на изменяемые ID без фк
		@NewColumnName varchar(128) -- новое наименование колонки базовой сущности, в которой останутся старые Guid - например ResourceIDGuid, если не указано, то колонка и значения будут удалены. В наследуемых сущностях колонка будет удаляться всегда 
AS BEGIN
	IF @WithExecute = 1 
		BEGIN TRANSACTION 
	
	SET NOCOUNT ON;
	
------------------------------------SQLTypes---------------------------------------------
--0 Удаление FK у наследуемых сущностей
--2 Добавление интовых колонок в базовые сущности
--4 Удаление FK у ссылающихся колонок
--6 Заполнение новой интовой колонки у главной таблицы без наследников
--8 Заполнение новых интовых колонок у наследуемых таблиц
--10 Сохранение всех старых и новых идентификаторов в dbo.OBJECT_REPLACE_GUID_INT
--11 Добавление новых колонок у ссылающихся 
--12 Добавление новых колонок у ссылающихся c дефолтом (для оптимизации, т.к. потом NOT NULL дорого)
--13 Создание индексов для оптимизации заполнения ссылающихся колонок
--14 Обновление колонок у ссылащающихся таблиц
--15 Удаление кастомных индексов созданных для оптимизации заполнения ссылающихся
--16 Добавление NOT NULL у новых ссылающихся колонок
--18 Удаление дефолтных ограничений у ссылающихся колонок
--20 Удаление CHECK И UNIQUE ограничений у ссылающихся
--22 Удаление индексов, в которых участвую ссылающиейся колонки
--24 Удаление UQ полей для ссылающихся 
--26 Удаление ссылающихся колонок
--28 Переименование ссылающихся колонок
--30 Создание UQ на ссылающихся колонках 
--32 Создание ограничений на ссылающихся 
--34 Создание индексов на ссылающихся таблицах
--36 Удаление индексов на главных таблах
--38 Удаление ограничений на UQ На главных
--40 Удаление UQ на главных 
--42 Удаление ограничения на PK на главных таблах
--44 Удаление колонок PK на главных таблах (либо только у наследников, а у базовой переименовываем в 46)
--46 ? Переименование колонки у базовой таблы
--48 Ренейм новых колонок у главных (PK_NAME_INT -> PK_NAME)
--50 Проставление всем колонкам NOT NULL
--52 Создание ограничения на PK
--54 Создание UQ
--56 Создание ограничений c UQ
--58 Создание индексов на главных таблицах с участием PK И UQ
--59 ? Создание уникального индекса на поле, в котором сохранились старые значения из пк
--60 Создание FK у ссылающихся таблиц
--62 Создание FK для наследования у главных таблиц
--64 Заполнение dbo.T_ENTITY_IDS
-----------------------------------------------------------------------------------------
	DECLARE @QuerySeparator VARCHAR(16) 

	SELECT @QuerySeparator =
		CASE WHEN @WithExecute = 1 
			--THEN CHAR(13) + CHAR(13) 
			THEN ''
			ELSE CHAR(13) + 'GO' + CHAR(13) + CHAR(13) END


	IF OBJECT_ID('dbo.UPDATABLE_TABLE_INDEX_COLUMNS') IS NOT NULL 
		DROP TABLE dbo.UPDATABLE_TABLE_INDEX_COLUMNS

	IF OBJECT_ID('dbo.UPDATABLE_TABLE_UQ_UNIQUE_CONSTRAINTS') IS NOT NULL 
		DROP TABLE dbo.UPDATABLE_TABLE_UQ_UNIQUE_CONSTRAINTS

	IF OBJECT_ID('dbo.UPDATABLE_TABLE_UQ') IS NOT NULL 
		DROP TABLE dbo.UPDATABLE_TABLE_UQ

	IF OBJECT_ID('dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEX_COLUMNS') IS NOT NULL 
		DROP TABLE dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEX_COLUMNS

	IF OBJECT_ID('dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEXES') IS NOT NULL 
		DROP TABLE dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEXES

	IF OBJECT_ID('dbo.UPDATABLE_TABLE_INDEXES') IS NOT NULL 
		DROP TABLE dbo.UPDATABLE_TABLE_INDEXES

	IF OBJECT_ID('dbo.REFERENCE_ON_UPDATABLE_TABLE_CONSTRAINTS') IS NOT NULL 
		DROP TABLE dbo.REFERENCE_ON_UPDATABLE_TABLE_CONSTRAINTS

	IF OBJECT_ID('dbo.REFERENCES_ON_UPDATABLE_TABLES_UQ') IS NOT NULL 
		DROP TABLE dbo.REFERENCES_ON_UPDATABLE_TABLES_UQ

	IF OBJECT_ID('dbo.REFERENCES_ON_UPDATABLE_TABLES') IS NOT NULL 
		DROP TABLE dbo.REFERENCES_ON_UPDATABLE_TABLES

	IF OBJECT_ID('dbo.UPDATABLE_TABLES') IS NOT NULL 
		DROP TABLE dbo.UPDATABLE_TABLES

	IF OBJECT_ID('dbo.OBJECT_REPLACE_GUID_INT') IS NOT NULL 
		DROP TABLE dbo.OBJECT_REPLACE_GUID_INT

	IF OBJECT_ID('dbo.EXECUTE_SQL_TABLE') IS NOT NULL 
		DROP TABLE dbo.EXECUTE_SQL_TABLE

---------------------------------------------------------------------------------

	CREATE TABLE dbo.UPDATABLE_TABLES (
		TableObjectId INT PRIMARY KEY,
		SchemaName VARCHAR(128) NOT NULL,
		TableName VARCHAR(128) NOT NULL,
		PrimaryKeyName VARCHAR(128) NOT NULL,
		OldColumnName VARCHAR(128) NOT NULL,
		NewColumnName VARCHAR(128) NOT NULL,
		BaseTableObjectId INT NOT NULL,
		NestingLevel INT NOT NULL, 
		InheritanceFK VARCHAR(128) NULL
	)

	CREATE TABLE dbo.UPDATABLE_TABLE_INDEXES (
		Id INT Identity(1,1) PRIMARY KEY,
		TableObjectId INT NOT NULL FOREIGN KEY REFERENCES dbo.UPDATABLE_TABLES(TableObjectId),
		IndexName VARCHAR(128) NOT NULL	UNIQUE,
		IndexId VARCHAR(128) NOT NULL,
		IsClustered BIT NOT NULL,
		IsUnique BIT NOT NULL,
		HasFilter BIT NOT NULL,
		IndexFilter VARCHAR(MAX) null,
		IndexFillFactor INT NOT NULL
	)

	CREATE TABLE dbo.UPDATABLE_TABLE_UQ_UNIQUE_CONSTRAINTS(
		TableObjectId int not null,
		ConstraintName varchar(128) not null,
		ConstraintDefinition varchar(1024) not null,
		UniqueIndexId int not null
	)

	CREATE TABLE dbo.UPDATABLE_TABLE_INDEX_COLUMNS (
		Id INT identity(1,1) PRIMARY KEY,
		UpdatableTableIndexId INT NOT NULL FOREIGN KEY REFERENCES dbo.UPDATABLE_TABLE_INDEXES(ID),
		ColumnId INT NOT NULL,
		ColumnName VARCHAR(128) NOT NULL,
		IsIncluded BIT NOT NULL, 
		OrderByDefinition VARCHAR(4) NOT NULL,
		CONSTRAINT UQ_UPDATABLE_TABLE_INDEX_COLUMNS  UNIQUE (UpdatableTableIndexId, ColumnId, ColumnName)
	)

	CREATE TABLE dbo.UPDATABLE_TABLE_UQ (
		TableObjectId INT NOT NULL,
		ColumnName varchar(128) not null,
		ColumnId int not null,
		ColumnDefinition varchar(1024) not null
	) -- надо дропнуть все UQ, затем создать заново, т.к. они смотрят на PK

	CREATE TABLE dbo.REFERENCES_ON_UPDATABLE_TABLES (
		Id INT identity(1,1) PRIMARY KEY,
		UpdatableTableObjectId INT NOT NULL FOREIGN KEY REFERENCES dbo.UPDATABLE_TABLES(TableObjectId),
		SchemaName VARCHAR(128) NOT NULL,
		TableName VARCHAR(128) NOT NULL,
		TableObjectId INT NOT NULL,
		OldColumnName VARCHAR(128) NOT NULL,
		NewColumnName VARCHAR(256) NOT NULL,
		ColumnId INT NOT NULL,
		ForeignKeyName VARCHAR(128) NULL,
		IsNullable BIT NOT NULL,
		CONSTRAINT UQ_REFERENCES_ON_UPDATABLE_TABLES_TOID_CID UNIQUE (TableObjectId, ColumnId)
	)

	CREATE TABLE dbo.REFERENCES_ON_UPDATABLE_TABLES_UQ
	(
		ReferenceOnUpdatableTableId int not null FOREIGN KEY REFERENCES dbo.REFERENCES_ON_UPDATABLE_TABLES,
		TableObjectId int not null,
		ColumnName varchar(128) not null,
		ColumnId int not null,
		ColumnDefinition varchar(1024) not null
	)
 
	CREATE TABLE dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEXES (
		Id INT Identity(1,1) PRIMARY KEY,
		TableObjectId INT NOT NULL,
		IndexName VARCHAR(128) NOT NULL UNIQUE,
		IndexId VARCHAR(128) NOT NULL,
		IsClustered BIT NOT NULL,
		IsUnique BIT NOT NULL,
		HasFilter BIT NOT NULL,
		IndexFilter VARCHAR(MAX) null,
		IndexFillFactor INT NOT NULL
	)

	CREATE TABLE dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEX_COLUMNS (
		ReferencesOnUpdatableTableIndexId INT NOT NULL FOREIGN KEY REFERENCES dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEXES(ID),
		ColumnId INT NOT NULL,
		ColumnName VARCHAR(128) NOT NULL,
		IsIncluded BIT NOT NULL, 
		OrderByDefinition varchar(4) NOT NULL,
		CONSTRAINT UQ_REFERENCES_ON_UPDATABLE_TABLE_INDEX_COLUMNS  UNIQUE (ReferencesOnUpdatableTableIndexId, ColumnId, ColumnName)
	)

	CREATE TABLE dbo.REFERENCE_ON_UPDATABLE_TABLE_CONSTRAINTS(
		TableObjectId int not null,
		ConstraintName varchar(128) not null,
		UniqueIndexId int,
		IsCheck bit,
		ConstraintDefinition varchar(1024) not null
	)

	CREATE TABLE dbo.OBJECT_REPLACE_GUID_INT (
		GuidId uniqueidentifier NOT NULL,
		IntId INT NOT NULL,
		ObjectId int not null,
		CONSTRAINT IX_OBJECT_REPLACE_GUID_INT UNIQUE (GuidId, INTId, ObjectId)
	)

	CREATE TABLE dbo.EXECUTE_SQL_TABLE (
		Id INT identity(1,1) PRIMARY KEY,
		Execution VARCHAR(MAX),
		ExecutionType int not null,
	)


---------------------------------------------------------- СБОР ДАННЫХ ДЛЯ ГЕНЕРАЦИИ СКРИПТОВ ----------------------------------------------------------

-------------------------- Поиск наследуемых таблиц (у поля есть Fk и Pk) --------------------------
	;WITH Entities AS (	
		SELECT 
			s.Name as SchemaName,
			t.Name as TableName,
			t.object_id as TableObjectId,
			i.name as PrimaryKeyName,
			c.name as OldColumnName,
			CONCAT(c.name, '_INT') as NewColumnName,
			-1 as BaseTableObjectId,
			0 AS NestingLevel,
			CAST(NULL AS VARCHAR(128)) AS InheritanceFK
		FROM sys.tables t
		INNER JOIN sys.schemas s on s.schema_id = t.schema_id
		INNER JOIN sys.columns c on c.object_id = t.object_id
		INNER JOIN sys.index_columns ic on c.object_id = ic.object_id and c.column_id = ic.column_id
		INNER JOIN sys.indexes i on ic.index_id = i.index_id and i.object_id = t.object_id
		WHERE 
			t.name = @BaseEntityName 
			AND s.name = @EntitySchemaName 
			AND i.is_primary_key = 1

		UNION ALL

		SELECT 
			s.name as SchemaName,
			t.name as TableName,
			pc.object_id as TableObjectId,
			i.name as PrimaryKeyName,
			pc.name as OldColumnName,
			CONCAT(pc.Name, '_INT') as NewColumnName,
			e.TableObjectId as BaseTableObjectId,
			e.NestingLevel + 1 as NestingLevel,
			cast(fk.name as varchar(128)) AS InheritanceFK
		FROM sys.foreign_keys fk
		JOIN sys.foreign_key_columns fkc on fk.object_id = fkc.constraINT_object_id 
		JOIN Entities e on fk.referenced_object_id = object_id(CONCAT(e.SchemaName, '.', e.TableName))
		JOIN sys.columns pc on fk.parent_object_id = pc.object_id and fkc.parent_column_id = pc.column_id
		JOIN sys.columns fc on fk.referenced_object_id = fc.object_id and fkc.referenced_column_id = fc.column_id
		JOIN sys.index_columns ic on pc.column_id = ic.column_id and pc.object_id = ic.object_id
		JOIN sys.indexes i on ic.index_id = i.index_id and ic.object_id = i.object_id
		JOIN sys.tables t on pc.object_id = t.object_id
		JOIN sys.schemas s on t.schema_id = s.schema_id
		WHERE 
			i.is_primary_key = 1
	)
	INSERT INTO dbo.UPDATABLE_TABLES (
		SchemaName, 
		TableName, 
		TableObjectId, 
		PrimaryKeyName, 
		OldColumnName, 
		NewColumnName, 
		BaseTableObjectId, 
		NestingLevel,
		InheritanceFK
	)
	SELECT 
		SchemaName, 
		TableName, 
		TableObjectId, 
		PrimaryKeyName, 
		OldColumnName, 
		NewColumnName, 
		BaseTableObjectId, 
		NestingLevel,
		InheritanceFK
	FROM ENTITIES

--------------------------Поиск всех зависимых таблиц по FK --------------------------
	INSERT INTO dbo.REFERENCES_ON_UPDATABLE_TABLES (
		UpdatableTableObjectId,
		SchemaName,
		TableName,
		TableObjectId,
		OldColumnName,
		NewColumnName,
		ForeignKeyName,
		IsNullable,
		ColumnId
	)
		SELECT
			ut.TableObjectId as UpdatableTableObjectId,
			s.name as SchemaName,
			pt.name as TableName,
			pt.object_id as TableObjectId,
			c.name as OldColumnName,
			CONCAT(c.name, '_INT') as NewColumnName,
			fk.name as ForeignKeyName,
			c.is_nullable as IsNullable,
			c.column_id
		FROM UPDATABLE_TABLES ut
		JOIN sys.tables rt on rt.object_id = ut.TableObjectId
		JOIN sys.foreign_keys fk on fk.referenced_object_id = rt.object_id
		JOIN sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
		JOIN sys.columns c on fkc.parent_object_id = c.object_id and fkc.parent_column_id = c.column_id
		JOIN sys.tables pt on pt.object_id = c.object_id
		JOIN sys.schemas s on pt.schema_id = s.schema_id
		WHERE 
			NOT EXISTS ( -- необходимо исключить наследуемые таблицы
				SELECT 1
				FROM sys.index_columns ic 
				JOIN sys.indexes i on i.index_id = ic.index_id and i.object_id = c.object_id
				WHERE ic.column_id = c.column_id and ic.object_id = c.object_id
				AND i.is_primary_key = 1
			)
--------------------------Поиск всех зависимых таблиц по VCLib.ENTITIES/ENTITIES_ATTRIBUTES --------------------------
	;WITH entities AS (
		SELECT 
			e.EntityID as EntityID,
			e.[Schema] as SchemaName,
			ea.MappingTable as MappingTable,
			ea.MappingName as PkName,
			e.EntName as EntName,
			-1 as BaseEntityId, 
			CONCAT(e.[Schema], '.', e.EntName) as SystemType
		FROM VCLib.T_ENTITIES_ATTRIBUTES ea
		JOIN VCLib.T_ENTITIES e on e.EntityID = ea.EntityID
		WHERE  
			e.[Schema] = @EntitySchemaName
			and ea.MappingTable = @BaseEntityName 
			and ea.IsPrimaryKey = 1

		UNION ALL

		SELECT
			e.EntityID as EntityID,
			e.[Schema] as SchemaName,
			en.MappingTable as MappingTable,
			en.PkName as PkName,
			e.EntName,
			e.BaseEntity,
			CONCAT(e.[Schema], '.', e.EntName) as SystemType
		FROM entities en
		JOIN VCLib.T_ENTITIES e on e.BaseEntity = en.EntityID
	)
	INSERT INTO dbo.REFERENCES_ON_UPDATABLE_TABLES (
		UpdatableTableObjectId,
		SchemaName,
		TableName,
		TableObjectId,
		OldColumnName,
		NewColumnName,
		ForeignKeyName,
		IsNullable,
		ColumnId
	)
	SELECT 
		OBJECT_ID(CONCAT(e.SchemaName, '.', e.MappingTable)) as UpdatableTableObjectId,
		et.[Schema] as SchemaName,
		ea.MappingTable as TableName,
		OBJECT_ID(CONCAT(et.[Schema], '.', ea.MappingTable)) as ReferenceTableObjectId,
		c.name as OldColumnName,
		CONCAT(c.name, '_INT') as NewColumnName,
		null as ForeignKeyName,
		c.is_nullable as IsNullable,
		c.column_id as ColumnId
	FROM entities e
	JOIN VCLib.T_ENTITIES_ATTRIBUTES ea ON  e.SystemType = ea.SystemType 
	JOIN VCLIB.T_ENTITIES et on ea.EntityID = et.EntityID
		and ea.AttributeType = 1
		and ea.MappingType = 1
	JOIN sys.columns c on 
		c.object_id = OBJECT_ID(CONCAT(et.[Schema], '.', ea.MappingTable))
		and c.name like ea.MappingName
	WHERE NOT EXISTS (
			SELECT 1
			FROM dbo.REFERENCES_ON_UPDATABLE_TABLES routt
			WHERE routt.TableObjectId = c.object_id and c.name = routt.OldColumnName)

-------------------------- Добавление всех зависимых колонок без ФК по @ColumnsWithoutForeignKeys  --------------------------
	IF @ColumnsWithoutForeignKeys IS NOT NULL AND @ColumnsWithoutForeignKeys <> '' 
	BEGIN
		CREATE TABLE dbo.custom_references(
			ColumnName varchar(128),
			TableObjectId int not null)
	
		INSERT INTO dbo.custom_references(ColumnName, TableObjectId)
		SELECT 
			SUBSTRING(x.split, CHARINDEX('.', x.split, CHARINDEX('.', x.split) + 1) + 1, LEN(x.split)) as ColumnName,
			OBJECT_ID(
				CONCAT(
					SUBSTRING(x.split, 1, CHARINDEX('.', x.split) - 1),
					'.',
					SUBSTRING(x.split, CHARINDEX('.', x.split) + 1, CHARINDEX('.', x.split, CHARINDEX('.', x.split) + 1) - CHARINDEX('.', x.split) - 1)
				)
			) AS TableObjectId
		FROM dbo.fn_Split(@ColumnsWithoutForeignKeys, '|') x

		DECLARE @MainUpdatableTableObjectId int 
		SELECT
			@MainUpdatableTableObjectId = (
				SELECT 
					TOP 1 TableObjectId 
				FROM dbo.UPDATABLE_TABLES ut 
				where ut.BaseTableObjectId = -1)


		INSERT INTO dbo.REFERENCES_ON_UPDATABLE_TABLES (
			UpdatableTableObjectId,
			SchemaName,
			TableName,
			TableObjectId,
			OldColumnName,
			NewColumnName,
			IsNullable,
			ColumnId,
			ForeignKeyName
		)
			SELECT @MainUpdatableTableObjectId AS UpdatableTableObjectId,
				s.name as SchemaName,
				t.name as TableName,
				t.object_id as TableObjectId,
				c.name as OldColumnName,
				CONCAT(c.name, '_INT') as NewColumnName,
				c.is_nullable as IsNullable,
				c.column_id as ColumnId,
				null as ForeignKeyName
			FROM dbo.custom_references crr
			JOIN sys.tables t on t.object_id = crr.TableObjectId
			JOIN sys.columns c on crr.TableObjectId = c.object_id and c.name = crr.ColumnName
			JOIN sys.schemas s on t.schema_id = s.schema_id
			WHERE NOT EXISTS (
				SELECT 1
				FROM dbo.REFERENCES_ON_UPDATABLE_TABLES routt
				WHERE routt.TableObjectId = crr.TableObjectId and crr.ColumnName = routt.OldColumnName)

		DROP TABLE dbo.custom_references
	END


-------------------------- Поиск всех UQ у главных (переделываем definition на случай, если использовался IsNull) --------------------------
	INSERT INTO dbo.UPDATABLE_TABLE_UQ (TableObjectId, ColumnId, ColumnName, ColumnDefinition)
		SELECT	
			ut.TableObjectId,
			cc.column_id,
			cc.name,
			'(case when ' + QUOTENAME(REPLACE(cc.name, '_UQ', '')) + ' IS NOT NULL then NULL else ' + QUOTENAME(ut.OldColumnName) + ' end)'
		FROM sys.computed_columns cc
		JOIN dbo.UPDATABLE_TABLES ut on cc.object_id = ut.TableObjectId and cc.name like '%_UQ'
	

-------------------------- Поиск всех UQ у референсных (переделываем скрипт на UQ на всякий случай) --------------------------
	INSERT INTO dbo.REFERENCES_ON_UPDATABLE_TABLES_UQ (ReferenceOnUpdatableTableId, TableObjectId, ColumnId, ColumnName, ColumnDefinition)
		SELECT 
			rout.Id,
			rout.TableObjectId,
			cc.column_id,
			cc.name,
			'(case when ' + QUOTENAME(rout.OldColumnName) + ' IS NOT NULL then NULL else ' + QUOTENAME(pk.name) + ' end)'
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout 
		JOIN sys.computed_columns cc on rout.TableObjectId = cc.object_id
		JOIN sys.index_columns ic on ic.object_id = rout.TableObjectId 
		JOIN sys.indexes i on ic.index_id = i.index_id and i.object_id = ic.object_id
		JOIN sys.columns pk on pk.column_id = ic.column_id and pk.object_id = ic.object_id
		where i.is_primary_key = 1
		 and cc.name = CONCAT(rout.OldColumnName, '_UQ')


-------------------------- Поиск UNIQUE CONSTRAINT на UQ у главных --------------------------
	INSERT INTO dbo.UPDATABLE_TABLE_UQ_UNIQUE_CONSTRAINTS (TableObjectId, ConstraintName, UniqueIndexId, ConstraintDefinition) 
		SELECT 
			kc.parent_object_id as TableObjectId,
			kc.name as ConstraintName,
			kc.unique_index_id as UniqueIndexId,
			QUOTENAME(STUFF((SELECT ', ' + c.COLUMN_NAME 
							FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
							WHERE  c.CONSTRAINT_NAME = kc.name
							FOR XML PATH('')), 1, 1, ''), ')'
					) as ColumnsInDefinition
			--*
		FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
		JOIN sys.key_constraints kc on ccu.CONSTRAINT_NAME = kc.name and kc.type = 'UQ'
		JOIN dbo.UPDATABLE_TABLE_UQ uq on uq.ColumnName = ccu.COLUMN_NAME and uq.TableObjectId = kc.parent_object_id
	 

-------------------------- Поиск всех ограничений кроме FK у зависимых колонок --------------------------

-- Unique ограничения на ссылающиеся колонки
	INSERT INTO dbo.REFERENCE_ON_UPDATABLE_TABLE_CONSTRAINTS (TableObjectId, ConstraintName, IsCheck, UniqueIndexId, ConstraintDefinition)
		SELECT DISTINCT
			rout.TableObjectId as TableObjectId,
			kc.name as ConstraintName,
			0 as IsCheck,
			kc.unique_index_id as UniqueIndexId,
			QUOTENAME(STUFF((SELECT ',' + c.COLUMN_NAME 
							FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
							WHERE  c.CONSTRAINT_NAME = kc.name
							FOR XML PATH('')), 1, 1, ''), ')'
					) as ColumnsInDefinition
		FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
		JOIN dbo.REFERENCES_ON_UPDATABLE_TABLES rout on 
			rout.OldColumnName = ccu.COLUMN_NAME 
			and rout.SchemaName = ccu.TABLE_SCHEMA 
			and rout.TableName = ccu.TABLE_NAME
		JOIN sys.key_constraints kc on 
			ccu.CONSTRAINT_NAME = kc.name and kc.type = 'UQ'

	
-- Unique ограничения на UQ
	INSERT INTO dbo.REFERENCE_ON_UPDATABLE_TABLE_CONSTRAINTS (TableObjectId, ConstraintName, IsCheck, UniqueIndexId, ConstraintDefinition)
		SELECT DISTINCT
			t.object_id as TableObjectId,
			kc.name as ConstraintName,
			0 as IsCheck,
			kc.unique_index_id as UniqueIndexId,
			QUOTENAME(STUFF((SELECT ',' + c.COLUMN_NAME 
							FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
							WHERE  c.CONSTRAINT_NAME = kc.name
							FOR XML PATH('')), 1, 1, ''), ')'
					) as ColumnsInDefinition
		FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
		JOIN sys.key_constraints kc on ccu.CONSTRAINT_NAME = kc.name and kc.type = 'UQ'
		JOIN sys.tables t on t.name = ccu.TABLE_NAME 
		JOIN sys.schemas s on s.name = ccu.TABLE_SCHEMA
		JOIN dbo.REFERENCES_ON_UPDATABLE_TABLES_UQ uq on uq.ColumnName = ccu.COLUMN_NAME and uq.TableObjectId = t.object_id
		WHERE NOT EXISTS (
			SELECT 1
			FROM dbo.REFERENCE_ON_UPDATABLE_TABLE_CONSTRAINTS rc
			WHERE rc.ConstraintName = kc.name and rc.TableObjectId = t.object_id)


--- Check
	INSERT INTO dbo.REFERENCE_ON_UPDATABLE_TABLE_CONSTRAINTS (TableObjectId, ConstraintName, IsCheck, UniqueIndexId, ConstraintDefinition) -- без вычислимых колонок
		SELECT DISTINCT
			cc.parent_object_id as ReferenceTableObjectId,
			cc.name as ConstraintName,
			1 as IsCheck,
			NULL as UniqueIndexId,
			cc.definition
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout 
		JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on 
			ccu.COLUMN_NAME = rout.NewColumnName
			and ccu.TABLE_NAME = rout.TableName
			and ccu.TABLE_SCHEMA = rout.SchemaName
		JOIN sys.check_constraints cc on ccu.CONSTRAINT_NAME = cc.name


-------------------------- Индексы у основных таблиц --------------------------
	INSERT INTO dbo.UPDATABLE_TABLE_INDEXES (
		TableObjectId,
		IndexName,
		IndexId,
		IsClustered,
		IsUnique,
		HasFilter,
		IndexFilter,
		IndexFillFactor
	)
		SELECT 
			i.object_id, 
			i.name as IndexName,
			i.index_id as IndexId,
			CASE WHEN i.type = 1 THEN 1 ELSE 0 END as IsClustered,
			i.is_unique,
			i.has_filter,
			i.filter_definition,
			i.fill_factor
		FROM dbo.UPDATABLE_TABLES ut
		JOIN sys.columns c on ut.OldColumnName = c.name and ut.TableObjectId = c.object_id
		JOIN sys.index_columns ic on ic.column_id = c.column_id and ic.object_id = c.object_id 
		JOIN sys.indexes i on i.index_id = ic.index_id and i.object_id = c.object_id
		WHERE i.is_primary_key = 0 


----------------------------- Поиск всех индексов у зависимых таблиц --------------------------
	INSERT INTO dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEXES (
		TableObjectId,
		IndexName,
		IndexId,
		IsClustered,
		IsUnique,
		HasFilter,
		IndexFilter,
		IndexFillFactor
	)
		SELECT DISTINCT
			i.object_id as ReferenceTableObjectId,
			i.name as IndexName,
			i.index_id as IndexId,
			CASE WHEN i.type = 1 THEN 1 ELSE 0 END as IsClustered,
			i.is_unique,
			i.has_filter,
			i.filter_definition,
			i.fill_factor
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout
		JOIN sys.columns c on rout.OldColumnName = c.name and rout.TableObjectId = c.object_id
		JOIN sys.index_columns ic on ic.column_id = c.column_id and ic.object_id = c.object_id
		JOIN sys.indexes i on i.index_id = ic.index_id and i.object_id = c.object_id
		WHERE i.is_primary_key = 0 


-------------------------- Поиск всех индексов с UQ у основных таблиц --------------------------
	INSERT INTO dbo.UPDATABLE_TABLE_INDEXES (
		TableObjectId,
		IndexName,
		IndexId,
		IsClustered,
		IsUnique,
		HasFilter,
		IndexFilter,
		IndexFillFactor
	)
		SELECT DISTINCT
			uq.TableObjectId as TableObjectId,
			i.name as IndexName,
			i.index_id as IndexId,
			CASE WHEN i.type = 1 THEN 1 ELSE 0 END as IsClustered,
			i.is_unique as IsUnique,
			i.has_filter as HasFilter,
			i.filter_definition as IndexFilter,
			i.fill_factor as IndexFillFactor
		FROM dbo.UPDATABLE_TABLE_UQ uq
		JOIN sys.columns c on c.object_id = uq.TableObjectId and c.column_id = uq.ColumnId
		JOIN sys.index_columns ic on c.object_id = ic.object_id and ic.column_id = c.column_id
		JOIN sys.indexes i on i.index_id = ic.index_id and i.object_id = ic.object_id
		WHERE 
			i.is_primary_key = 0
			AND NOT EXISTS ( -- исключаем индексы, которые уже созданы для ПК
				SELECT 1
				FROM dbo.UPDATABLE_TABLE_INDEXES uti
				WHERE 
					uq.TableObjectId = uti.TableObjectId
					AND i.index_id = uti.IndexId)
			AND NOT EXISTS ( -- исключаем индексы, которые создаются вместе с ограничением
				SELECT 1 
				FROM dbo.UPDATABLE_TABLE_UQ_UNIQUE_CONSTRAINTS utqc
				WHERE 
					utqc.TableObjectId = uq.TableObjectId 
					and utqc.UniqueIndexId = i.index_id)


-------------------------- Поиск всех индексов с UQ у ссылающихся таблиц --------------------------
	INSERT INTO dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEXES (
		TableObjectId,
		IndexName,
		IndexId,
		IsClustered,
		IsUnique,
		HasFilter,
		IndexFilter,
		IndexFillFactor
	)
		SELECT 
			i.object_id as ReferenceTableObjectId,
			i.name as IndexName,
			i.index_id as IndexId,
			CASE WHEN i.type = 1 THEN 1 ELSE 0 END as IsClustered,
			i.is_unique,
			i.has_filter,
			i.filter_definition,
			i.fill_factor
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES_UQ uq
		JOIN sys.columns c on uq.ColumnName = c.name and uq.TableObjectId = c.object_id
		JOIN sys.index_columns ic on ic.column_id = c.column_id and ic.object_id = c.object_id
		JOIN sys.indexes i on i.index_id = ic.index_id and i.object_id = c.object_id
		WHERE 
			i.is_primary_key = 0 
			AND NOT EXISTS (
				SELECT 1
				FROM dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEXES ri
				WHERE 
					ri.IndexId = i.index_id
					and i.object_id = ri.TableObjectId)

----------------------------- Поиск всех колонок для индексов у основных таблиц -----------------------------
	INSERT INTO dbo.UPDATABLE_TABLE_INDEX_COLUMNS (
		UpdatableTableIndexId,
		ColumnId,
		ColumnName,
		IsIncluded,
		OrderByDefinition
	)
	SELECT DISTINCT
		ui.Id as UpdatableTableIndexId,
		c.column_id as ColumnId,
		c.name as ColumnName,
		ic.is_included_column as IsIncluded,
		CASE WHEN ic.is_descending_key = 0 THEN 'ASC' ELSE 'DESC' END OrderByDefinition
	FROM dbo.UPDATABLE_TABLE_INDEXES ui
	JOIN dbo.UPDATABLE_TABLES ut on ui.TableObjectId = ut.TableObjectId
	JOIN sys.index_columns ic on ic.index_id = ui.IndexId and ic.object_id = ut.TableObjectId
	JOIN sys.columns c on ic.column_id = c.column_id and c.object_id = ic.object_id


----------------------------- Поиск всех колонок для индексов у референсных таблиц -----------------------------
	INSERT INTO dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEX_COLUMNS (
		ReferencesOnUpdatableTableIndexId,
		ColumnId,
		ColumnName,
		IsIncluded,
		OrderByDefinition
	)
	SELECT 
		DISTINCT
		rti.Id as ReferencesOnUpdatableTableIndexId,
		c.column_id as ColumnId,
		c.name as ColumnName,
		ic.is_included_column as IsIncluded,
		CASE WHEN ic.is_descending_key = 0 THEN 'ASC' ELSE 'DESC' END as OrderByDefinition
	FROM dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEXES rti
	JOIN dbo.REFERENCES_ON_UPDATABLE_TABLES rt ON rt.TableObjectId = rti.TableObjectId
	JOIN sys.index_columns ic on ic.index_id = rti.IndexId and ic.object_id = rt.TableObjectId
	JOIN sys.columns c on ic.column_id = c.column_id and c.object_id = ic.object_id


---------------------------------------------------------- ГЕНЕРАЦИЯ СКРИПТОВ ----------------------------------------------------------

----------------------------- Удаление FK у наследуемых сущностей -----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' DROP CONSTRAINT ' + QUOTENAME(ut.InheritanceFK) + @QuerySeparator,
			0
		FROM dbo.UPDATABLE_TABLES ut
		WHERE ut.InheritanceFK is not null


----------------------------- Добавление интовых колонок в базовые сущности -----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			
			'ALTER TABLE ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' ADD ' + QUOTENAME(ut.NewColumnName) + ' INT NULL' + @QuerySeparator,
			2
		FROM dbo.UPDATABLE_TABLES ut


----------------------------- Удаление FK ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			
			'ALTER TABLE ' + QUOTENAME(rout.SchemaName) + '.' + QUOTENAME(rout.TableName) + ' DROP CONSTRAINT ' + QUOTENAME(rout.ForeignKeyName) + @QuerySeparator,
			4
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout
		WHERE rout.ForeignKeyName is not NULL


----------------------------- Заполнение новой колонки у главной таблицы -----------------------------
	IF @OrderByColumnName IS NULL OR @OrderByColumnName = '' 
		BEGIN
			SELECT 
				@OrderByColumnName = ut.OldColumnName
			FROM dbo.UPDATABLE_TABLES ut
			WHERE ut.BaseTableObjectId = -1
		END

	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 	
			
			'; WITH NumberedRows AS (' + CHAR(13) + 
			'	SELECT ' + CHAR(13) + 
			'		' + QUOTENAME(ut.NewColumnName) + ',' + CHAR(13) + 
			--'		ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(ut.OldColumnName) + ') AS RowNum' + CHAR(13) + 
			'		ROW_NUMBER() OVER (ORDER BY ' + @OrderByColumnName + ') AS RowNum' + CHAR(13) + 
			'	FROM ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + CHAR(13) + 
			')' + CHAR(13) + 
			'UPDATE	NumberedRows' + CHAR(13) + 
			'SET ' + QUOTENAME(ut.NewColumnName) + ' = RowNum' + @QuerySeparator,
			6
		FROM dbo.UPDATABLE_TABLES ut
		WHERE ut.BaseTableObjectId = -1
		

----------------------------- Заполнение новых колонок у наследуемых таблиц -----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			
			'UPDATE t' + CHAR(13) +
			'  SET t.' + ut.NewColumnName + ' = mt.' + mt.NewColumnName + CHAR(13) + 
			'FROM ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' t' + CHAR(13) +
			'JOIN ' + QUOTENAME(mt.SchemaName) + '.' + QUOTENAME(mt.TableName) + ' mt on t.' + ut.OldColumnName + ' = mt.' + mt.OldColumnName + @QuerySeparator,
			8
		FROM dbo.UPDATABLE_TABLES ut
		JOIN dbo.UPDATABLE_TABLES mt on mt.BaseTableObjectId = -1
		WHERE ut.BaseTableObjectId <> -1


----------------------------- Заполнение всех идентификаторов из обновляемых таблиц -----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			
			'INSERT INTO dbo.OBJECT_REPLACE_GUID_INT (GuidId, IntId, ObjectId)' + CHAR(13) +
			'SELECT ' + CHAR(13) +
			'  ' + ut.OldColumnName + ',' + char(13) +
			'  ' + ut.NewColumnName + ',' + char(13) +
			'  ' + CAST(ut.TableObjectId AS VARCHAR) + char(13) +
			'FROM ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + @QuerySeparator, 
			10
		FROM dbo.UPDATABLE_TABLES ut
		WHERE ut.BaseTableObjectId = -1

----------------------------- Добавление колонок у ссылающихся таблиц у которых поле NULL-----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 			
			'ALTER TABLE ' + QUOTENAME(rout.SchemaName) + '.' + QUOTENAME(rout.TableName) + ' ADD ' + QUOTENAME(rout.NewColumnName) + ' INT NULL' + @QuerySeparator,
			11
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout 
		WHERE rout.IsNullable = 1
		ORDER BY rout.UpdatableTableObjectId


----------------------------- Добавление колонок у ссылающихся таблиц у которых поле NOT NULL c дефолтом-----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 			
			'ALTER TABLE ' + QUOTENAME(rout.SchemaName) + '.' + QUOTENAME(rout.TableName) + ' ADD ' + QUOTENAME(rout.NewColumnName) + ' INT CONSTRAINT ' + QUOTENAME(CONCAT('DFLT_', rout.TableName, '_', rout.NewColumnName)) + ' DEFAULT (-1) NOT NULL' + @QuerySeparator,
			12
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout 
		WHERE rout.IsNullable = 0
		ORDER BY rout.UpdatableTableObjectId


----------------------------- Создание индексов для оптимизации заполнения ссылающихся колонок -----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'CREATE NONCLUSTERED INDEX ' + QUOTENAME(CONCAT('CUSTOM_IX_', rout.TableName, '_', rout.OldColumnName)) + ' ON ' 
			+ QUOTENAME(rout.SchemaName) + '.' + QUOTENAME(rout.TableName) + '(' + QUOTENAME(rout.OldColumnName) + ')' + @QuerySeparator,
			13
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout


----------------------------- Обновление колонок у ссылающихся таблиц-----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'UPDATE u' + CHAR(13) + 
			' SET u.' + QUOTENAME(rout.NewColumnName) + ' = mt.' + QUOTENAME(ut.NewColumnName) + CHAR(13) +
			'FROM ' + QUOTENAME(rout.SchemaName) + '.' + QUOTENAME(rout.TableName) + ' u' + CHAR(13) +
			'JOIN ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' mt ON mt.' + QUOTENAME(ut.OldColumnName) + ' = u.' + QUOTENAME(rout.OldColumnName) + @QuerySeparator,
			14
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout
		JOIN dbo.UPDATABLE_TABLES ut on ut.TableObjectId = rout.UpdatableTableObjectId


----------------------------- Удаление кастомных индексов, созданных для оптимизации -----------------------------

	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'DROP INDEX ' + QUOTENAME(CONCAT('CUSTOM_IX_', rout.TableName, '_', rout.OldColumnName)) + ' ON ' 
			+ QUOTENAME(rout.SchemaName) + '.' + QUOTENAME(rout.TableName) + @QuerySeparator,
			15
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout


----------------------------- Удаление дефолтного ограничения на NOT NULL ссылающихся колонках -----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + QUOTENAME(rout.SchemaName) + '.' + QUOTENAME(rout.TableName) + CHAR(13) +
			'DROP CONSTRAINT ' + QUOTENAME(CONCAT('DFLT_', rout.TableName, '_', rout.NewColumnName)) + @QuerySeparator,
			16
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout
		WHERE rout.IsNullable = 0
		ORDER BY rout.UpdatableTableObjectId


----------------------------- Удаление дефолтных ограничений у ссылающихся ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + QUOTENAME(rout.SchemaName) + '.' + QUOTENAME(rout.TableName) + ' DROP CONSTRAINT ' + QUOTENAME(dc.name) + @QuerySeparator, 
			18
		FROM sys.default_constraints dc
		JOIN dbo.REFERENCES_ON_UPDATABLE_TABLES rout on rout.TableObjectId = dc.parent_object_id and rout.ColumnId = dc.parent_column_id


----------------------------- Удаление CHECK и UNIQUE ограничений у ссылающихся ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE(Execution, ExecutionType)
		SELECT DISTINCT
			'ALTER TABLE ' + QUOTENAME(s.Name) + '.' + QUOTENAME(t.Name) + ' DROP CONSTRAINT ' + QUOTENAME(routc.ConstraintName) + @QuerySeparator,
			20
		FROM dbo.REFERENCE_ON_UPDATABLE_TABLE_CONSTRAINTS routc
		JOIN sys.tables t on routc.TableObjectId = t.object_id
		JOIN sys.schemas s on  s.schema_id = t.schema_id


----------------------------- Удаление индексов на ссылающихся таблицах ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT DISTINCT
			'DROP INDEX ' + QUOTENAME(routi.IndexName) + ' ON ' + QUOTENAME(rout.SchemaName) + '.' + QUOTENAME(rout.TableName) + @QuerySeparator,
			22
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEXES routi
		JOIN dbo.REFERENCES_ON_UPDATABLE_TABLES rout on rout.TableObjectId = routi.TableObjectId
		WHERE 
			NOT EXISTS ( -- исключаем UNIQUE индексы, на которые созданы в следствии добавления CONSTRAINT 
				SELECT 1
				FROM dbo.REFERENCE_ON_UPDATABLE_TABLE_CONSTRAINTS routc
				WHERE ROUTC.UniqueIndexId = routi.IndexId and routi.TableObjectId = routc.TableObjectId
			)


----------------------------- Удаление UQ у ссылающихся ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + QUOTENAME(rout.SchemaName) + '.' + QUOTENAME(rout.TableName) + ' DROP COLUMN ' + QUOTENAME(uq.ColumnName) + @QuerySeparator,
			24
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES_UQ uq
		JOIN dbo.REFERENCES_ON_UPDATABLE_TABLES rout on rout.id = uq.ReferenceOnUpdatableTableId


----------------------------- Удаление ссылающихся колонок ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + QUOTENAME(rout.SchemaName) + '.' + QUOTENAME(rout.TableName) + ' DROP COLUMN ' + QUOTENAME(rout.OldColumnName) + @QuerySeparator,
			26
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout


----------------------------- Переименование ссылающихся колонок ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType) 
		SELECT 
			'EXEC sp_rename @objname=''' + rout.SchemaName + '.' + rout.TableName + '.' + rout.NewColumnName + ''', @newname=''' + rout.OldColumnName + ''', @objtype =''COLUMN''' + @QuerySeparator,
			28
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout


----------------------------- Создание UQ на ссылающихся таблах ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + CONCAT(QUOTENAME(rout.SchemaName), '.', QUOTENAME(rout.TableName)) + ' ADD ' + QUOTENAME(uq.ColumnName) + ' AS ' + uq.ColumnDefinition + @QuerySeparator, 
			30
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout
		JOIN dbo.REFERENCES_ON_UPDATABLE_TABLES_UQ uq ON rout.Id = uq.ReferenceOnUpdatableTableId


----------------------------- Создание ограничений на ссылающихся ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' ADD CONSTRAINT ' + QUOTENAME(routc.ConstraintName) + 
			CASE WHEN routc.IsCheck = 1 THEN ' CHECK ' ELSE ' UNIQUE ' END + routc.ConstraintDefinition + @QuerySeparator,
			32
		FROM dbo.REFERENCE_ON_UPDATABLE_TABLE_CONSTRAINTS routc
		JOIN sys.tables t on t.object_id = routc.TableObjectId
		JOIN sys.schemas s on s.schema_id = t.schema_id 


----------------------------- Создание индексов на ссылающихся таблицах ----------------------------
	DECLARE 
		@index_id int, 
		@included_columns varchar(max),
		@index_columns varchar(max)

	DECLARE curs CURSOR FOR 
		SELECT ind.Id
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEXES ind
		WHERE 
			NOT EXISTS ( -- исключаем UNIQUE индексы, на которые созданы в следствии добавления CONSTRAINT 
				SELECT 1
				FROM dbo.REFERENCE_ON_UPDATABLE_TABLE_CONSTRAINTS routc
				WHERE ROUTC.UniqueIndexId = ind.IndexId and ind.TableObjectId = routc.TableObjectId
			)

	OPEN CURS
	FETCH NEXT FROM curs INTO @index_id
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT
			@included_columns = '',
			@index_columns = ''
	
		SELECT
			@index_columns = @index_columns + 
				CASE 
					WHEN @index_columns = '' then QUOTENAME(routic.ColumnName)+ ' ' + routic.OrderByDefinition
					ELSE ', ' + QUOTENAME(routic.ColumnName) + ' ' + routic.OrderByDefinition
				END
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEX_COLUMNS routic
		WHERE routic.IsIncluded = 0 AND routic.ReferencesOnUpdatableTableIndexId = @index_id

		SELECT
			@included_columns = @included_columns + 
				CASE 
					WHEN @included_columns = '' then QUOTENAME(routic.ColumnName) 
					ELSE ', ' + QUOTENAME(routic.ColumnName)
				END
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEX_COLUMNS routic
		WHERE routic.IsIncluded = 1 AND routic.ReferencesOnUpdatableTableIndexId = @index_id
	
		INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
			SELECT DISTINCT
				
				'CREATE ' +
				CASE WHEN routic.IsUnique = 1 THEN 'UNIQUE ' ELSE '' END +
				CASE WHEN routic.IsClustered = 1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END +
				'INDEX ' + QUOTENAME(routic.IndexName) + ' ON ' +
				QUOTENAME(rout.SchemaName) + '.' + QUOTENAME(rout.TableName) + ' (' + @index_columns + ')' +
				CASE WHEN @included_columns <> '' THEN ' INCLUDE (' + @included_columns + ')' ELSE '' END +
				CASE WHEN routic.HasFilter = 1 THEN ' WHERE ' + routic.IndexFilter ELSE '' END + 
				CASE WHEN routic.IndexFillFactor > 0 THEN ' WITH (FILLFACTOR = ' + CAST(routic.IndexFillFactor as varchar) + ')' ELSE '' END + @QuerySeparator,
				34
			--select routic.IsUnique, routic.IsClustered, routic.IndexName, rout.SchemaName, Rout.TableName
			FROM dbo.REFERENCES_ON_UPDATABLE_TABLE_INDEXES routic 
			JOIN dbo.REFERENCES_ON_UPDATABLE_TABLES rout on routic.TableObjectId = rout.TableObjectId
			WHERE routic.Id = @index_id
		FETCH NEXT FROM curs INTO @index_id
	END
	CLOSE curs
	DEALLOCATE curs

----------------------------- Удаление индексов на главных ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'DROP INDEX ' + QUOTENAME(uti.IndexName) + ' ON ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + @QuerySeparator,
			36
		FROM dbo.UPDATABLE_TABLE_INDEXES uti
		JOIN dbo.UPDATABLE_TABLES ut on ut.TableObjectId = uti.TableObjectId


----------------------------- Удаление ограничений на UQ на главных ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT
			'ALTER TABLE ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' DROP CONSTRAINT ' + uq.ConstraintName + @QuerySeparator,
			38
		FROM dbo.UPDATABLE_TABLE_UQ_UNIQUE_CONSTRAINTS uq
		JOIN dbo.UPDATABLE_TABLES ut on ut.TableObjectId = uq.TableObjectId


----------------------------- Удаление UQ на главных----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE(Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' DROP COLUMN ' + uq.ColumnName + @QuerySeparator,
			40
		FROM dbo.UPDATABLE_TABLE_UQ UQ
		JOIN dbo.UPDATABLE_TABLES ut on ut.TableObjectId = uq.TableObjectId



----------------------------- Удаление ограничения PK на главных таблицах ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' DROP CONSTRAINT ' + ut.PrimaryKeyName + @QuerySeparator,
			42
		FROM dbo.UPDATABLE_TABLES ut
		ORDER BY ut.NestingLevel DESC



----------------------------- Удаление колонок PK на главных таблицах ----------------------------
	IF @NewColumnName IS NULL or @NewColumnName = '' 
	BEGIN
		INSERT INTO dbo.EXECUTE_SQL_TABLE(Execution, ExecutionType)
			SELECT 
			
				'ALTER TABLE ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' DROP COLUMN ' + QUOTENAME(ut.OldColumnName) + @QuerySeparator,
				44
			FROM dbo.UPDATABLE_TABLES ut
			ORDER BY ut.NestingLevel DESC
	END
	ELSE BEGIN
	-- Дропаем колонки у всех наследуемых, а у главной ее переименовываем 
		INSERT INTO dbo.EXECUTE_SQL_TABLE(Execution, ExecutionType)
			SELECT 
				'ALTER TABLE ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' DROP COLUMN ' + QUOTENAME(ut.OldColumnName) + @QuerySeparator,
				44
			FROM dbo.UPDATABLE_TABLES ut
			WHERE ut.BaseTableObjectId <> -1
			ORDER BY ut.NestingLevel DESC

		INSERT INTO dbo.EXECUTE_SQL_TABLE(Execution, ExecutionType)
			SELECT 
				'EXEC sp_rename @objname=''' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + '.' + ut.OldColumnName + ''', @newname=''' + @NewColumnName + ''', @objtype =''COLUMN''' + @QuerySeparator,
				46
			FROM dbo.UPDATABLE_TABLES ut
			WHERE ut.BaseTableObjectId = -1
	END

----------------------------- Ренейм новых колонок у главных таблиц----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE(Execution, ExecutionType)
		SELECT 
			'EXEC sp_rename @objname=''' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + '.' + ut.NewColumnName + ''', @newname=''' + ut.OldColumnName + ''', @objtype =''COLUMN''' + @QuerySeparator,
			48
		FROM dbo.UPDATABLE_TABLES ut


----------------------------- Изменение новых колонок на NOT NULL  ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + 
			' ALTER COLUMN ' + QUOTENAME(ut.OldColumnName) + ' INT NOT NULL' + @QuerySeparator,
			50
		FROM dbo.UPDATABLE_TABLES ut

		

----------------------------- Создание ограничений на PK ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' ADD CONSTRAINT ' + QUOTENAME(ut.PrimaryKeyName) + CHAR(13) +
			'PRIMARY KEY CLUSTERED (' + ut.OldColumnName + ')' + @QuerySeparator,
			52
		FROM dbo.UPDATABLE_TABLES ut


	----------------------------- Создание UQ ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' ADD ' + QUOTENAME(uq.ColumnName) + ' AS ' + uq.ColumnDefinition + @QuerySeparator,
			54
		FROM dbo.UPDATABLE_TABLE_UQ uq
		JOIN dbo.UPDATABLE_TABLES ut on ut.TableObjectId = uq.TableObjectId



----------------------------- Создание UQ CONSTRAINT ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' ADD CONSTRAINT ' +  QUOTENAME(uq.ConstraintName) + ' UNIQUE NONCLUSTERED ' + uq.ConstraintDefinition + @QuerySeparator,
			56
		FROM dbo.UPDATABLE_TABLE_UQ_UNIQUE_CONSTRAINTS uq
		JOIN dbo.UPDATABLE_TABLES ut on ut.TableObjectId = uq.TableObjectId

----------------------------- Создание индексов на главных таблицах с участием PK и UQ ----------------------------
	DECLARE curs CURSOR FOR 
		SELECT ind.Id
		FROM dbo.UPDATABLE_TABLE_INDEXES ind

	OPEN CURS
	FETCH NEXT FROM curs INTO @index_id
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT
			@included_columns = '',
			@index_columns = ''
	
		SELECT
			@index_columns = @index_columns + 
				CASE 
					WHEN @index_columns = '' then QUOTENAME(utic.ColumnName) + ' ' + utic.OrderByDefinition
					ELSE ', ' + QUOTENAME(utic.ColumnName) + ' ' + utic.OrderByDefinition
				END
		FROM dbo.UPDATABLE_TABLE_INDEX_COLUMNS utic
		WHERE utic.IsIncluded = 0 AND utic.UpdatableTableIndexId = @index_id

		SELECT
			@included_columns = @included_columns + 
				CASE 
					WHEN @included_columns = '' then QUOTENAME(utic.ColumnName) 
					ELSE ', ' + QUOTENAME(utic.ColumnName) 
				END
		FROM dbo.UPDATABLE_TABLE_INDEX_COLUMNS utic
		WHERE utic.IsIncluded = 1 AND utic.UpdatableTableIndexId = @index_id
	
		INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
			SELECT DISTINCT
				 + 'CREATE ' +
				CASE WHEN utic.IsUnique = 1 THEN 'UNIQUE ' ELSE '' END +
				CASE WHEN utic.IsClustered = 1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END +
				'INDEX ' + QUOTENAME(utic.IndexName) + ' ON ' +
				QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' (' + @index_columns + ')' +
				CASE WHEN @included_columns <> '' THEN ' INCLUDE (' + @included_columns + ')' ELSE '' END + 
				CASE WHEN utic.HasFilter = 1 THEN ' WHERE ' + utic.IndexFilter ELSE '' END + 
				CASE WHEN utic.IndexFillFactor > 0 THEN ' WITH (FILLFACTOR = ' + CAST(utic.IndexFillFactor as varchar) + ')' ELSE '' END + @QuerySeparator,
				58
			--select utic.IsUnique, utic.IsClustered, utic.IndexName, ut.SchemaName, ut.TableName
			FROM dbo.UPDATABLE_TABLE_INDEXES utic 
			JOIN dbo.UPDATABLE_TABLES ut on utic.TableObjectId = ut.TableObjectId
			WHERE utic.Id = @index_id

		FETCH NEXT FROM curs INTO @index_id
	END
	CLOSE curs
	DEALLOCATE curs

	IF @NewColumnName IS NOT NULL AND @NewColumnName <> '' 
	BEGIN
		INSERT INTO dbo.EXECUTE_SQL_TABLE(Execution, ExecutionType)
			SELECT 
				'CREATE UNIQUE NONCLUSTERED INDEX [UQ_' + ut.TableName + '_' + @NewColumnName + '] ON ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' (' + QUOTENAME(@NewColumnName) + ' ASC) ON [PRIMARY]' + @QuerySeparator,
				59
			FROM dbo.UPDATABLE_TABLES ut
			WHERE ut.BaseTableObjectId = -1
	END

----------------------------- Создание FK у ссылающихся таблиц ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE(Execution, ExecutionType)
		SELECT 
			
			'ALTER TABLE ' + QUOTENAME(rout.SchemaName) + '.' + QUOTENAME(rout.TableName) + ' ADD CONSTRAINT ' + QUOTENAME(rout.ForeignKeyName) + CHAR(13) +
			'FOREIGN KEY (' + QUOTENAME(rout.OldColumnName) + ') REFERENCES '
			+ QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' (' + QUOTENAME(ut.OldColumnName) + ')' + @QuerySeparator,
			60
		FROM dbo.REFERENCES_ON_UPDATABLE_TABLES rout
		JOIN dbo.UPDATABLE_TABLES ut on rout.UpdatableTableObjectId = ut.TableObjectId
		WHERE rout.ForeignKeyName is not NULL


----------------------------- Создание FK наследования у главных таблиц ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE (Execution, ExecutionType)
		SELECT 
			'ALTER TABLE ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + ' ADD CONSTRAINT ' + QUOTENAME(ut.InheritanceFK) + CHAR(13) 
			+ 'FOREIGN KEY (' + QUOTENAME(ut.OldColumnName) + ') REFERENCES '
			+ QUOTENAME(main.SchemaName) + '.' + QUOTENAME(main.TableName) + ' (' + QUOTENAME(main.OldColumnName) + ')' + @QuerySeparator, 
			62
		FROM dbo.UPDATABLE_TABLES ut
		JOIN dbo.UPDATABLE_TABLES main on ut.BaseTableObjectId = main.TableObjectId
		WHERE ut.NestingLevel > 0


----------------------------- Инсерт в [dbo].[T_ENTITY_IDS]  ----------------------------
	INSERT INTO dbo.EXECUTE_SQL_TABLE(Execution, ExecutionType)
		SELECT 
			'INSERT INTO [dbo].[T_ENTITY_IDS] (EntName, FieldName, Id) ' +
					' SELECT ' +
					'''' + ut.SchemaName + '.' + SUBSTRING(ut.TableName, 3, LEN(ut.TableName)) + ''',' +
					'''PrimaryKey'', ' +
					'CASE WHEN MAX(' + ut.OldColumnName + ') IS NULL THEN 1 ELSE MAX(' + ut.OldColumnName + ') + 1 END ' +
				'FROM ' + QUOTENAME(ut.SchemaName) + '.' + QUOTENAME(ut.TableName) + @QuerySeparator,
				64
		FROM dbo.UPDATABLE_TABLES ut
		WHERE ut.BaseTableObjectId = -1 -- в таблице хранятся только идентификаторы корневой таблы


---------------------------------------------------------- ВЫПОЛНЕНИЕ СКРИПТОВ ----------------------------------------------------------
	DECLARE @WithError bit = 0
	DECLARE @SQL VARCHAR(MAX)
	DECLARE curs CURSOR FOR
		SELECT 
			CONCAT(Execution, char(13), '--Id: ', Id, CHAR(13), '--ExecutionType: ', ExecutionType, CHAR(13), CHAR(13))
		FROM dbo.EXECUTE_SQL_TABLE
		order by Id

	OPEN curs
	FETCH NEXT FROM curs INTO @SQL
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		RAISERROR (@sql, 0, 1) WITH NOWAIT

		IF @WithExecute = 1 BEGIN
			BEGIN TRY
				EXEC (@sql)
			END TRY
			BEGIN CATCH
				DECLARE @ErrorMessage NVARCHAR(4000);
				DECLARE @ErrorSeverity INT;
				DECLARE @ErrorState INT;

				SELECT 
					@ErrorMessage = ERROR_MESSAGE(),
					@ErrorSeverity = ERROR_SEVERITY(),
					@ErrorState = ERROR_STATE(),
					@WithError = 1;
				RAISERROR (@ErrorMessage, -- Message text.
						   @ErrorSeverity, -- Severity.
						   @ErrorState -- State.
						   );
				BREAK
			END CATCH
		END 
			
		FETCH NEXT FROM curs INTO @SQL
	END
	CLOSE curs
	DEALLOCATE curs


	SET NOCOUNT OFF;

	IF @WithError = 1
		BEGIN
			RAISERROR ('ERROR. Rollback transaction called', 0, 1) WITH NOWAIT
			ROLLBACK TRANSACTION
		END
	ELSE IF @WithExecute = 1
	BEGIN
		IF @WithRollback = 1
			ROLLBACK TRANSACTION
		ELSE
			COMMIT TRANSACTION
	END
END
GO


EXEC dbo.ChangePrimaryKeyGuidToIntProcedure @BaseEntityName='T_RESOURCE', @EntitySchemaName='AKUZ', @WithExecute = 1, @WithRollback = 0, @OrderByColumnName = '', @ColumnsWithoutForeignKeys='AKUZ.T_CARD_NOTE_SIGNATURE.ResourceID|AKUZ.T_DISPANSARY_OBSERVATION_EXCHANGE.PostID|AKUZ.T_REMOTE_DIRECTION.Initiator|AKUZ.T_ENTRY_HISTORY.Resource|AKUZ.T_ENTRY_LOG2.PerformerResource|AKUZ.T_ENTRY_LOG2.DirectorPost', @NewColumnName='ResourceIdGuid'
GO