SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping foreign keys from [SQL].[LoadedDatabases]'
GO
ALTER TABLE [SQL].[LoadedDatabases] DROP CONSTRAINT[FK_LoadedDatabases_LoadingInfos]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [SQL].[LoadedDatabases]'
GO
ALTER TABLE [SQL].[LoadedDatabases] DROP CONSTRAINT [DF_LoadedDatabases_ServerType]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping index [IX_LoadingInfo_LoadedDatabaseID_ObjectType] from [SQL].[LoadedObjects]'
GO
DROP INDEX [IX_LoadingInfo_LoadedDatabaseID_ObjectType] ON [SQL].[LoadedObjects]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping [SQL].[InsertDatabaseInfos]'
GO
DROP PROCEDURE [SQL].[InsertDatabaseInfos]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping types'
GO
DROP TYPE [SQL].[DatabaseInfoType]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating types'
GO
CREATE TYPE [SQL].[TagObjectIdentityType] AS TABLE
(
[LoadedServerId] [int] NULL,
[DatabaseId] [int] NULL,
[DatabaseObjectId] [int] NULL,
[ColumnName] [nvarchar] (max) NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
CREATE TYPE [SQL].[SQLObjectIdentityType] AS TABLE
(
[Id] [int] NOT NULL,
[SQLInstanceName] [nvarchar] (max) NULL,
[DatabaseName] [nvarchar] (max) NULL,
[ObjectID] [nvarchar] (max) NULL,
[ColumnName] [nvarchar] (max) NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
CREATE TYPE [SQL].[DatabaseInfoType] AS TABLE
(
[DatabaseName] [nvarchar] (max) NULL,
[DatabaseId] [int] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[LoadedDatabases]'
GO
ALTER TABLE [SQL].[LoadedDatabases] DROP
COLUMN [SQLInstanceName],
COLUMN [DatabaseName],
COLUMN [ServerType],
COLUMN [DatabaseIdTemp]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[InsertServerInfos]'
GO
CREATE PROCEDURE [SQL].[InsertServerInfos]
	@ServiceTypeId INT,
    @Name   NVARCHAR (512),
	@Domain NVARCHAR (512) = NULL
AS

BEGIN
	SET NOCOUNT ON
	
	DECLARE @LoadedServerId INT
        SET @LoadedServerId = 
        (
           SELECT top(1) [LoadedServerId] 
           FROM [SQL].[LoadedServers]
           WHERE [ServiceTypeId] = @ServiceTypeId AND [Name] = @Name AND (@ServiceTypeId=5 OR [Domain] = @Domain)
        )
		
		IF(@LoadedServerId IS NULL)
		BEGIN
			INSERT INTO [SQL].[LoadedServers] (
				[ServiceTypeId],
				[Name],
				[Domain])
			VALUES (@ServiceTypeId, @Name, @Domain)

			SET @LoadedServerId = 
			(
			   SELECT top(1) [LoadedServerId] 
			   FROM [SQL].[LoadedServers]
			   WHERE [ServiceTypeId] = @ServiceTypeId AND [Name] = @Name AND (@ServiceTypeId=5 OR [Domain] = @Domain)
			)
		END

		SELECT @LoadedServerId
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[Tags]'
GO
CREATE TABLE [SQL].[Tags]
(
[TagId] [int] NOT NULL IDENTITY(1, 1),
[LoadedServerId] [int] NULL,
[DatabaseId] [int] NULL,
[DatabaseObjectId] [int] NULL,
[TagData] [nvarchar] (max) NULL,
[TagTypeId] [int] NOT NULL,
[TagCategoryId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_Tags] on [SQL].[Tags]'
GO
ALTER TABLE [SQL].[Tags] ADD CONSTRAINT [PK_Tags] PRIMARY KEY CLUSTERED  ([TagId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [IX_Tags] on [SQL].[Tags]'
GO
CREATE NONCLUSTERED INDEX [IX_Tags] ON [SQL].[Tags] ([TagTypeId], [TagCategoryId], [LoadedServerId], [DatabaseId], [DatabaseObjectId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[LoadedObjects]'
GO
ALTER TABLE [SQL].[LoadedObjects] DROP
COLUMN [ObjectID],
COLUMN [ObjectName],
COLUMN [SchemaName],
COLUMN [CreateTime],
COLUMN [ObjectType]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
ALTER TABLE [SQL].[LoadedObjects] ALTER COLUMN [DatabaseObjectId] [int] NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [IX_LoadedObjects_LoadingInfoID] on [SQL].[LoadedObjects]'
GO
CREATE NONCLUSTERED INDEX [IX_LoadedObjects_LoadingInfoID] ON [SQL].[LoadedObjects] ([LoadingInfoID], [LoadedDatabaseID]) INCLUDE ([DatabaseObjectId], [LoadedObjectsID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[InsertViewInfos]'
GO
ALTER PROCEDURE [SQL].[InsertViewInfos]
	@LoadingInfoID int,
	@DatabaseID int,
	@LoadedDatabaseID int,
	@TableType int,
	@TableVariable [SQL].[TableObjectType] READONLY

AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newViewInfos (
		[LoadedObjectsID] INT NULL,
		[DatabaseObjectId] INT NULL,
		[ObjectID] INT NOT NULL,
		[ObjectName]  NVARCHAR(MAX) NOT NULL,
		[SchemaName]  NVARCHAR(MAX) NOT NULL,
		[CreateTime]  datetime NOT NULL,
		[ModifyTime] datetime NOT NULL,

		[IsReplicated] bit,
	) 

	INSERT INTO #newViewInfos (
		[LoadedObjectsID],
		[DatabaseObjectId],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],		
		[IsReplicated])
	SELECT NULL,
		   dbo.DatabaseObjectId,
		   ti.ObjectId,
		   ti.TableName,
		   ti.SchemaName,
		   ti.CreateTime,
		   ti.ModifyTime,
		   ti.IsReplicated
	FROM @TableVariable ti
	LEFT JOIN [SQL].[DatabaseObjects] dbo ON
		dbo.[DatabaseId] = @DatabaseID AND dbo.[ObjectID] = ti.ObjectId AND dbo.[ObjectType] = @TableType

	INSERT INTO [SQL].DatabaseObjects(
		[DatabaseId],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ObjectType])
	SELECT @DatabaseID,
		   ObjectID,
		   ObjectName,
		   SchemaName,
		   CreateTime,
		   @TableType
	FROM #newViewInfos
	WHERE DatabaseObjectId IS NULL

	UPDATE nvi
		SET nvi.DatabaseObjectId = dbo.DatabaseObjectId
	FROM #newViewInfos nvi
	JOIN [SQL].[DatabaseObjects] dbo ON
		dbo.[DatabaseId] = @DatabaseID AND dbo.[ObjectID] = nvi.ObjectId AND dbo.[ObjectType] = @TableType
	WHERE nvi.DatabaseObjectId IS NULL

	INSERT INTO [SQL].[LoadedObjects](			
		[LoadingInfoID],
		[LoadedDatabaseID],
		[DatabaseObjectId],
		[ModifyTime])
	SELECT @LoadingInfoID,
		   @LoadedDatabaseID,
		   nvi.DatabaseObjectId,
		   nvi.ModifyTime
	FROM #newViewInfos nvi

	UPDATE nvi
	   SET nvi.LoadedObjectsID = obj.LoadedObjectsID  
	FROM #newViewInfos nvi
	JOIN [SQL].[LoadedObjects] obj ON nvi.DatabaseObjectId = obj.DatabaseObjectId AND obj.LoadingInfoID = @LoadingInfoID AND obj.LoadedDatabaseID = @LoadedDatabaseID

	SELECT nvi.LoadedObjectsID,
		   nvi.ObjectID
	FROM #newViewInfos nvi
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[GetLoadedTableKeys]'
GO
ALTER PROCEDURE [SQL].[GetLoadedTableKeys]
	@SourceLoadedDatabaseID int,
	@TargetLoadedDatabaseID int
AS
BEGIN
	SELECT ld.LoadedDatabaseID,
		   ls.Name,
		   db.DatabaseName,
		   ltk.LoadedObjectId,
		   dbo.ObjectName,
		   dbo.SchemaName,
		   ltk.ColumnName,
		   ltk.DeleteAction,
		   ltk.UpdateAction,
		   ltk.KeyName,
		   ltk.KeyType,
		   tdbo.ObjectName,
		   tdbo.SchemaName,
		   ltk.TargetTableColumn,
		   lto.LoadedObjectsID,
		   dbo.ObjectType
	FROM [SQL].LoadedTableKeys ltk
	JOIN [SQL].LoadedObjects lo ON ltk.LoadedObjectId = lo.LoadedObjectsID
	JOIN [SQL].DatabaseObjects dbo ON dbo.DatabaseObjectId = lo.DatabaseObjectId
	JOIN [SQL].LoadedDatabases ld ON lo.LoadedDatabaseID = ld.LoadedDatabaseID
	JOIN [SQL].Databases db ON db.DatabaseId = ld.DatabaseId
	JOIN [SQL].LoadedServers ls ON ls.LoadedServerId = db.LoadedServerId
	LEFT JOIN [SQL].LoadedObjects lto ON ltk.TargetTableObjectId = lto.LoadedObjectsID
	LEFT JOIN [SQL].DatabaseObjects tdbo ON tdbo.DatabaseObjectId = lto.DatabaseObjectId
	WHERE ld.LoadedDatabaseID = @SourceLoadedDatabaseID OR ld.LoadedDatabaseID = @TargetLoadedDatabaseID
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[LoadedRoles]'
GO
ALTER TABLE [SQL].[LoadedRoles] DROP
COLUMN [RoleID],
COLUMN [RoleName],
COLUMN [Owner],
COLUMN [CreateDate]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
ALTER TABLE [SQL].[LoadedRoles] ALTER COLUMN [DatabaseRoleId] [int] NOT NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[GetLoadedRoles]'
GO
CREATE PROCEDURE [SQL].[GetLoadedRoles]
	@DbObjectTypeId int,
	@LoadingInfoId INT,
	@DatabaseIds DatabaseIDsType READONLY	
AS
BEGIN
	SELECT dr.RoleName,
		   dr.DatabaseRoleId,
		   db.DatabaseName AS DatabaseName,
		   ls.Name AS ServerName,
		   ls.Domain AS DomainName
	FROM [SQL].[LoadedRoles] lr
	JOIN [SQL].[DatabaseRoles] dr ON dr.DatabaseRoleId = lr.DatabaseRoleId
	JOIN [SQL].[LoadedDatabases] ldb ON ldb.LoadedDatabaseID = lr.LoadedDatabaseID
	JOIN @DatabaseIds rids ON rids.DatabaseID = ldb.DatabaseId
	JOIN [SQL].[Databases] db ON db.DatabaseId = ldb.DatabaseId
	JOIN [SQL].[LoadedServers] ls ON ls.LoadedServerId = db.LoadedServerId
	WHERE ldb.LoadingInfoID = @LoadingInfoId
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[TagCategory]'
GO
CREATE TABLE [SQL].[TagCategory]
(
[TagCategoryId] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (255) NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_TagCategory] on [SQL].[TagCategory]'
GO
ALTER TABLE [SQL].[TagCategory] ADD CONSTRAINT [PK_TagCategory] PRIMARY KEY CLUSTERED  ([TagCategoryId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[TagType]'
GO
CREATE TABLE [SQL].[TagType]
(
[TagTypeId] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (255) NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_TagType] on [SQL].[TagType]'
GO
ALTER TABLE [SQL].[TagType] ADD CONSTRAINT [PK_TagType] PRIMARY KEY CLUSTERED  ([TagTypeId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[InsertDatabaseInfos]'
GO
CREATE PROCEDURE [SQL].[InsertDatabaseInfos]	
	@LoadingInfoID int,
	@ServerId int,
	@TableVariable DatabaseInfoType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newDatabaseInfos (
		[LoadedDatabaseId] int NULL,
		[DatabaseId] int NULL,
		[DatabaseName] NVARCHAR(MAX),
		[MSDatabaseId] INT,
		[LoadedServerId] INT
	) 

	INSERT INTO #newDatabaseInfos (
			[LoadedDatabaseId],
			[DatabaseId],
			[DatabaseName],
			[MSDatabaseId],
			[LoadedServerId])
	SELECT NULL,
		   NULL,
		   di.DatabaseName,
		   di.DatabaseId,
		   @ServerId
	FROM @TableVariable di

	UPDATE nd
		SET nd.[DatabaseId] = db.[DatabaseId]
	FROM #newDatabaseInfos nd
	JOIN [SQL].[Databases] db 
		ON db.MSDatabaseId = nd.MSDatabaseId
		AND db.DatabaseName = nd.DatabaseName
		AND db.LoadedServerId = nd.LoadedServerId

	
	INSERT INTO [SQL].Databases(
		[DatabaseName],
		[MSDatabaseId],
		[LoadedServerId])
	SELECT [DatabaseName]
		  ,[MSDatabaseId]
		  ,[LoadedServerId]
	FROM #newDatabaseInfos
	WHERE [DatabaseId] IS NULL

	UPDATE nd
		SET nd.[DatabaseId] = db.[DatabaseId]
	FROM #newDatabaseInfos nd
	JOIN [SQL].[Databases] db 
		ON db.MSDatabaseId = nd.MSDatabaseId
		AND db.DatabaseName = nd.DatabaseName
		AND db.LoadedServerId = nd.LoadedServerId
	WHERE nd.[DatabaseId] IS NULL

	INSERT INTO [SQL].[LoadedDatabases](
			LoadingInfoID,
			DatabaseId)
	SELECT @LoadingInfoID,
		   ndi.DatabaseId
	FROM #newDatabaseInfos ndi

	UPDATE ndi
	   SET ndi.LoadedDatabaseId = db.LoadedDatabaseID	  
	FROM #newDatabaseInfos ndi
	JOIN [SQL].[LoadedDatabases] db ON ndi.DatabaseId = db.DatabaseId AND db.LoadingInfoID = @LoadingInfoID

	SELECT ndi.LoadedDatabaseId
		  ,ndi.MSDatabaseId
		  ,ndi.DatabaseId
	FROM #newDatabaseInfos ndi
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[InsertTags]'
GO
CREATE PROCEDURE [SQL].[InsertTags]
	@TagCategoryId INT,
	@TagTypeId INT,
	@Tags TagObjectIdentityType READONLY
AS

BEGIN
CREATE TABLE #newTags(
		[TagId] int NULL,
		[LoadedServerId] int,
		[DatabaseId] int,
		[DatabaseObjectId]  int,
		[ColumnName] NVARCHAR(MAX)
)

IF @TagTypeId = 1
BEGIN
		INSERT INTO #newTags(
			[TagId],
			[LoadedServerId],
			[DatabaseId],
			[DatabaseObjectId],
			[ColumnName])
		SELECT t.TagId, nt.*
		FROM @Tags nt
		LEFT JOIN [SQL].Tags t 
			ON t.LoadedServerId = nt.LoadedServerId
			AND TagTypeId = @TagTypeId
			AND TagCategoryId = @TagCategoryId
		WHERE t.DatabaseId IS NULL AND t.DatabaseObjectId IS NULL AND t.TagData IS NULL
END

IF @TagTypeId = 2
BEGIN
		INSERT INTO #newTags(
			[TagId],
			[LoadedServerId],
			[DatabaseId],
			[DatabaseObjectId],
			[ColumnName])
		SELECT t.TagId, nt.*
		FROM @Tags nt
		LEFT JOIN [SQL].Tags t 
			ON t.LoadedServerId = nt.LoadedServerId
			AND t.DatabaseId = nt.DatabaseId
			AND TagTypeId = @TagTypeId
			AND TagCategoryId = @TagCategoryId
		WHERE t.DatabaseObjectId IS NULL AND t.TagData IS NULL
END

IF @TagTypeId = 3
BEGIN
		INSERT INTO #newTags(
			[TagId],
			[LoadedServerId],
			[DatabaseId],
			[DatabaseObjectId],
			[ColumnName])
		SELECT t.TagId, nt.*
		FROM @Tags nt
		LEFT JOIN [SQL].Tags t 
			ON t.LoadedServerId = nt.LoadedServerId
			AND t.DatabaseId = nt.DatabaseId
			AND t.DatabaseObjectId = nt.DatabaseObjectId
			AND TagTypeId = @TagTypeId
			AND TagCategoryId = @TagCategoryId
		WHERE t.TagData IS NULL
END

IF @TagTypeId = 4
BEGIN
		INSERT INTO #newTags(
			[TagId],
			[LoadedServerId],
			[DatabaseId],
			[DatabaseObjectId],
			[ColumnName])
		SELECT t.TagId, nt.*
		FROM @Tags nt
		LEFT JOIN [SQL].Tags t 
			ON t.LoadedServerId = nt.LoadedServerId
			AND t.DatabaseId = nt.DatabaseId
			AND t.DatabaseObjectId = nt.DatabaseObjectId
			AND TagTypeId = @TagTypeId
			AND TagCategoryId = @TagCategoryId
			AND t.TagData = nt.ColumnName
END




INSERT INTO [SQL].Tags(
		[LoadedServerId],
		[DatabaseId],
		[DatabaseObjectId],
		[TagData],
		[TagTypeId],
		[TagCategoryId])
SELECT [LoadedServerId],
		[DatabaseId],
		[DatabaseObjectId],
		[ColumnName],
		@TagTypeId,
		@TagCategoryId
FROM #newTags nt
WHERE nt.TagId IS NULL

DROP TABLE #newTags

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[GetDatabaseObjectIds]'
GO
CREATE PROCEDURE [SQL].[GetDatabaseObjectIds]
	@ServerTypeId int,
	@TagTypeId int,
	@TableVariable [SQL].[SQLObjectIdentityType] READONLY
AS
BEGIN
	CREATE TABLE #objectIdentites(
			[Id] int,
			[SQLInstanceName] NVARCHAR(MAX),
			[LoadedServerId] int,
			[DatabaseId] int,
			[DatabaseObjectId]  int,
			[ColumnName]  nvarchar(max)
	)

	IF @TagTypeId = 1
	BEGIN
			INSERT INTO #objectIdentites(
				[Id],
				[LoadedServerId],
				[SQLInstanceName],
				[DatabaseId],
				[DatabaseObjectId],
				[ColumnName])
			SELECT ot.Id, 
			   ls.LoadedServerId,
			   ls.Name,
			   NULL,
			   NULL,
			   ot.ColumnName
			FROM [SQL].LoadedServers ls
				 JOIN @TableVariable ot ON ot.SQLInstanceName = ls.Name
			WHERE ls.ServiceTypeId = @ServerTypeId
	END

	IF @TagTypeId = 2
	BEGIN
			INSERT INTO #objectIdentites(
				[Id],
				[LoadedServerId],
				[SQLInstanceName],
				[DatabaseId],
				[DatabaseObjectId],
				[ColumnName])
			SELECT ot.Id, 
			   ls.LoadedServerId,
			   ls.Name,
			   db.DatabaseId,
			   NULL,
			   ot.ColumnName
			FROM [SQL].LoadedServers ls
				JOIN [SQL].Databases db ON db.LoadedServerId = ls.LoadedServerId
				JOIN @TableVariable ot ON ot.SQLInstanceName = ls.Name 
					AND ot.DatabaseName = db.DatabaseName
			WHERE ls.ServiceTypeId = @ServerTypeId
	END

	IF @TagTypeId = 3 OR @TagTypeId = 4
	BEGIN
			INSERT INTO #objectIdentites(
				[Id],
				[LoadedServerId],
				[SQLInstanceName],
				[DatabaseId],
				[DatabaseObjectId],
				[ColumnName])
			SELECT ot.Id, 
			   ls.LoadedServerId,
			   ls.Name,
			   db.DatabaseId,
			   dbo.DatabaseObjectId,
			   ot.ColumnName
			FROM [SQL].LoadedServers ls
				JOIN [SQL].Databases db ON db.LoadedServerId = ls.LoadedServerId
				JOIN [SQL].DatabaseObjects dbo ON dbo.DatabaseId = db.DatabaseId
				JOIN @TableVariable ot ON ot.SQLInstanceName = ls.Name 
					AND ot.DatabaseName = db.DatabaseName
					AND ot.ObjectID = dbo.ObjectID
			WHERE ls.ServiceTypeId = @ServerTypeId
	END

	SELECT * FROM #objectIdentites
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[GetLoadedColumnSettings]'
GO
ALTER PROCEDURE [SQL].[GetLoadedColumnSettings]
	@SourceLoadedDatabaseID int,
	@TargetLoadedDatabaseID int
AS
BEGIN
	SELECT ld.LoadedDatabaseID,
	   ls.Name,
	   db.DatabaseName,
	   lc.LoadedObjectsID,
	   dbo.ObjectName,
	   dbo.SchemaName,
	   lcs.Type,
	   lcs.ColumnName,
	   lcs.MaxLength,
	   lcs.IsNullable,
	   lcs.IsIdentity,
	   lcs.CollationName,
	   lcs.Precision,
	   lcs.KeyType,
	   dbo.ObjectType
	FROM [SQL].LoadedColumns lc
	JOIN [SQL].LoadedObjects lo ON lc.LoadedObjectsID = lo.LoadedObjectsID
	JOIN [SQL].DatabaseObjects dbo ON dbo.DatabaseObjectId = lo.DatabaseObjectId
	JOIN [SQL].LoadedDatabases ld ON lo.LoadedDatabaseID = ld.LoadedDatabaseID
	JOIN [SQL].Databases db ON db.DatabaseId = ld.DatabaseId
	JOIN [SQL].LoadedServers ls ON ls.LoadedServerId = db.LoadedServerId
	JOIN [SQL].[LoadedColumnSettings] lcs ON lc.ColumnSettingsID = lcs.ColumnSettingsID
	WHERE ld.LoadedDatabaseID = @SourceLoadedDatabaseID OR ld.LoadedDatabaseID = @TargetLoadedDatabaseID
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[InsertTableInfos]'
GO
ALTER PROCEDURE [SQL].[InsertTableInfos]
	@LoadingInfoID int,
	@DatabaseID int,
	@LoadedDatabaseID int,
	@TableType int,
	@TableVariable [SQL].[TableObjectType] READONLY

AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newTableInfos (
		[LoadedObjectsID] INT NULL,
		[DatabaseObjectId] INT NULL,
		[ObjectID] INT NOT NULL,
		[ObjectName]  NVARCHAR(MAX) NOT NULL,
		[SchemaName]  NVARCHAR(MAX) NOT NULL,
		[CreateTime]  datetime NOT NULL,
		[ModifyTime] datetime NOT NULL,
		[IsReplicated] bit,
		[RowNumber] int,
		[TotalSpaceKB] int,
		[DataSizeKB] int,
		[IndexSizeKB] int
	) 

	INSERT INTO #newTableInfos (
		[LoadedObjectsID],
		[DatabaseObjectId],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],		
		[IsReplicated],
		[RowNumber],
		[TotalSpaceKB],
		[DataSizeKB],
		[IndexSizeKB])
	SELECT NULL,
		   dbo.DatabaseObjectId,
		   ti.ObjectId,
		   ti.TableName,
		   ti.SchemaName,
		   ti.CreateTime,
		   ti.ModifyTime,
		   ti.IsReplicated,
		   ti.RowNumber,
		   ti.TotalSpaceKB,
		   ti.DataSizeKB,
		   ti.IndexSizeKB
	FROM @TableVariable ti
	LEFT JOIN [SQL].[DatabaseObjects] dbo ON
		dbo.[DatabaseId] = @DatabaseID AND dbo.[ObjectID] = ti.ObjectId AND dbo.[ObjectType] = @TableType

	INSERT INTO [SQL].DatabaseObjects(
		[DatabaseId],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ObjectType])
	SELECT @DatabaseID,
		   ObjectID,
		   ObjectName,
		   SchemaName,
		   CreateTime,	   
		   @TableType
	FROM #newTableInfos
	WHERE DatabaseObjectId IS NULL

	UPDATE nti
		SET nti.DatabaseObjectId = dbo.DatabaseObjectId
	FROM #newTableInfos nti
	JOIN [SQL].[DatabaseObjects] dbo ON
		dbo.[DatabaseId] = @DatabaseID AND dbo.[ObjectID] = nti.ObjectId AND dbo.[ObjectType] = @TableType
	WHERE nti.DatabaseObjectId IS NULL


	INSERT INTO [SQL].[LoadedObjects](			
		[LoadingInfoID],
		[LoadedDatabaseID],
		[DatabaseObjectId],
		[ModifyTime])
	SELECT @LoadingInfoID,
		   @LoadedDatabaseID,
		   nti.DatabaseObjectId,
		   nti.ModifyTime
	FROM #newTableInfos nti

	UPDATE nti
	   SET nti.LoadedObjectsID = obj.LoadedObjectsID  
	FROM #newTableInfos nti
	JOIN [SQL].[LoadedObjects] obj ON nti.DatabaseObjectId = obj.DatabaseObjectId AND obj.LoadingInfoID = @LoadingInfoID AND obj.LoadedDatabaseID = @LoadedDatabaseID

	INSERT INTO [SQL].[LoadedTables](			
		[LoadedObjectsID],
		[IsReplicated],
		[RowNumber],
		[TotalSpaceKB],
		[DataSizeKB],
		[IndexSizeKB])
	SELECT nti.LoadedObjectsID,
		   nti.IsReplicated,
		   nti.RowNumber,
		   nti.TotalSpaceKB,
		   nti.DataSizeKB,
		   nti.IndexSizeKB
	FROM #newTableInfos nti

	SELECT nti.LoadedObjectsID,
		   nti.ObjectID
	FROM #newTableInfos nti
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[InsertProgrammibilityInfos]'
GO
ALTER PROCEDURE [SQL].[InsertProgrammibilityInfos]
	@LoadingInfoID int,
	@DatabaseID int,
	@LoadedDatabaseID int,
	@TableType int,
	@ProgrammInfos [SQL].[ProgrammInfoType] READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newPrgrammInfos (
		[LoadedObjectsID] INT NULL,
		[DatabaseObjectId] INT NULL,
		[ObjectID] INT NOT NULL,
		[ObjectName]  NVARCHAR(MAX) NOT NULL,
		[SchemaName]  NVARCHAR(MAX) NOT NULL,
		[CreateTime]  datetime NOT NULL,
		[ModifyTime] datetime NOT NULL,		
		[ProgramType] int,
		[IsEncrypted] bit
	) 

	INSERT INTO #newPrgrammInfos (
		[LoadedObjectsID],
		[DatabaseObjectId],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ModifyTime],		
		[ProgramType],
		[IsEncrypted])
	SELECT NULL,
		   dbo.DatabaseObjectId,
		   pii.ObjectId,
		   pii.ObjectName,
		   pii.SchemaName,
		   pii.CreateTime,
		   pii.ModifyTime,
		   pii.ProgramType,
		   pii.IsEncrypted		  
	FROM @ProgrammInfos pii
	LEFT JOIN [SQL].[DatabaseObjects] dbo ON
		dbo.[DatabaseId] = @DatabaseID AND dbo.[ObjectID] = pii.ObjectId AND dbo.[ObjectType] = @TableType

	INSERT INTO [SQL].DatabaseObjects(
		[DatabaseId],
		[ObjectID],
		[ObjectName],
		[SchemaName],
		[CreateTime],
		[ObjectType])
	SELECT @DatabaseID,
		   ObjectID,
		   ObjectName,
		   SchemaName,
		   CreateTime,
		   @TableType
	FROM #newPrgrammInfos
	WHERE DatabaseObjectId IS NULL

	UPDATE npi
		SET npi.DatabaseObjectId = dbo.DatabaseObjectId
	FROM #newPrgrammInfos npi
	JOIN [SQL].[DatabaseObjects] dbo ON
		dbo.[DatabaseId] = @DatabaseID AND dbo.[ObjectID] = npi.ObjectId AND dbo.[ObjectType] = @TableType
	WHERE npi.DatabaseObjectId IS NULL

	INSERT INTO [SQL].[LoadedObjects](			
		[LoadingInfoID],
		[LoadedDatabaseID],
		[DatabaseObjectId],
		[ModifyTime])
	SELECT @LoadingInfoID,
		   @LoadedDatabaseID,
		   npi.DatabaseObjectId,
		   npi.ModifyTime
	FROM #newPrgrammInfos npi

	UPDATE npi
	   SET npi.LoadedObjectsID = obj.LoadedObjectsID  
	FROM #newPrgrammInfos npi
	JOIN [SQL].[LoadedObjects] obj ON npi.DatabaseObjectId = obj.DatabaseObjectId AND obj.LoadingInfoID = @LoadingInfoID AND obj.LoadedDatabaseID = @LoadedDatabaseID

	INSERT INTO [SQL].[LoadedProgrammabilities](			
		[LoadedObjectsID],
		[ProgramType],
		[IsEncrypted])
	SELECT npi.LoadedObjectsID,
			npi.ProgramType,
			npi.IsEncrypted
	FROM #newPrgrammInfos npi

	SELECT npi.LoadedObjectsID,
		   npi.ObjectID
	FROM #newPrgrammInfos npi
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[GetLoadedProgrammParameters]'
GO
ALTER PROCEDURE [SQL].[GetLoadedProgrammParameters]
	@SourceLoadedDatabaseID int,
	@TargetLoadedDatabaseID int

AS
BEGIN
	SELECT ld.LoadedDatabaseID,
		   ls.Name,
		   db.DatabaseName,
		   lp.LoadedObjectsID,
		   dbo.ObjectName,
		   dbo.SchemaName,
		   lps.TypeName,
		   lps.ParameterName,
		   lps.IsOutput,
		   lp.ParameterId,
		   lps.HasDefaultValue,
		   dbo.ObjectType
	FROM [SQL].LoadedProgrammParameters lp
	JOIN [SQL].LoadedObjects lo ON lp.LoadedObjectsID = lo.LoadedObjectsID
	JOIN [SQL].DatabaseObjects dbo ON dbo.DatabaseObjectId = lo.DatabaseObjectId
	JOIN [SQL].LoadedDatabases ld ON lo.LoadedDatabaseID = ld.LoadedDatabaseID
	JOIN [SQL].Databases db ON db.DatabaseId = ld.DatabaseId
	JOIN [SQL].LoadedServers ls ON ls.LoadedServerId = db.LoadedServerId
	JOIN [SQL].LoadedProgrammParamSettings lps ON lps.ParametersSettingsID = lp.ParametersSettingsID
	WHERE ld.LoadedDatabaseID = @SourceLoadedDatabaseID OR ld.LoadedDatabaseID = @TargetLoadedDatabaseID
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [SQL].[GetLoadedDatabaseObjects]'
GO
CREATE PROCEDURE [SQL].[GetLoadedDatabaseObjects]
	@DbObjectTypeId int,
	@LoadingInfoId INT,
	@DatabaseIds DatabaseIDsType READONLY	
AS
BEGIN
	SELECT dbo.ObjectName AS ObjectName,
		   dbo.SchemaName AS SchemaName,
		   dbo.DatabaseObjectId AS DatabaseObjectId,
		   db.DatabaseName AS DatabaseName,
		   ls.Name AS ServerName,
		   ls.Domain AS DomainName
	FROM [SQL].[LoadedObjects] lo
	JOIN [SQL].[LoadedDatabases] ldb ON lo.LoadedDatabaseID = ldb.LoadedDatabaseID
	JOIN [SQL].[Databases] db ON db.DatabaseId = ldb.DatabaseId
	JOIN [SQL].[LoadedServers] ls ON ls.LoadedServerId = db.LoadedServerId
 	JOIN @DatabaseIds dbids ON dbids.DatabaseID = ldb.DatabaseId
	JOIN [SQL].[DatabaseObjects] dbo ON dbo.DatabaseObjectId = lo.DatabaseObjectId
	WHERE dbo.ObjectType = @DbObjectTypeId  AND lo.LoadingInfoID = @LoadingInfoId
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [SQL].[InsertRoleInfos]'
GO
ALTER PROCEDURE [SQL].[InsertRoleInfos]
	@LoadedDatabaseID int,
	@DatabaseID int,
	@RoleInfos [SQL].[RoleInfoType] READONLY,
	@RoleSecurables [SQL].[RoleSecurablesInfoType] READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	CREATE TABLE #newRoleInfos (
		[LoadedRoleID] INT NULL,
		[DatabaseRoleId] INT NULL,
		[RoleID] int,
		[RoleName] nvarchar(MAX),
		[Owner] nvarchar(MAX),
		[CreateDate] datetime,
		[ModifyDate] datetime
	)
	
	CREATE TABLE #newRoleSecurablesSett (
		[LoadedRoleSecurablesSettingsID] INT NULL,
		[RoleID] int,
		[LoadedObjectID] int,
		[PermissionName] nvarchar(100),
		[PermissionState] nvarchar(50),
		[PermissionScope] nvarchar(100)
	)  

	INSERT INTO #newRoleInfos (
		[LoadedRoleID],
		[DatabaseRoleId],
		[RoleID],
		[RoleName],
		[Owner],
		[CreateDate],
		[ModifyDate])
	SELECT NULL,
		   NULL,
		   [RoleID],
		   [RoleName],
		   [Owner],
		   [CreateDate],
		   [ModifyDate]
	FROM @RoleInfos ri

	UPDATE nri
		SET nri.[DatabaseRoleId] = dbr.[DatabaseRoleId]
	FROM #newRoleInfos nri
	JOIN [SQL].[DatabaseRoles] dbr
		ON dbr.RoleID = nri.RoleID
		AND dbr.DatabaseId = @DatabaseID

	INSERT INTO [SQL].[DatabaseRoles](			
			[DatabaseId],
			[RoleID],
			[RoleName],
			[Owner],
			[CreateDate])
	SELECT @DatabaseID,
		  [RoleID],
		  [RoleName],
		  [Owner],
		  [CreateDate]  
	FROM #newRoleInfos nri
	WHERE nri.DatabaseRoleId IS NULL

	UPDATE nri
		SET nri.[DatabaseRoleId] = dbr.[DatabaseRoleId]
	FROM #newRoleInfos nri
	JOIN [SQL].[DatabaseRoles] dbr
		ON dbr.RoleID = nri.RoleID
		AND dbr.DatabaseId = @DatabaseID
	WHERE nri.DatabaseRoleId IS NULL

	INSERT INTO [SQL].[LoadedRoles](			
			[LoadedDatabaseID],
			[DatabaseRoleId],
			[ModifyDate])
	SELECT @LoadedDatabaseID,
		  [DatabaseRoleId],
		  [ModifyDate]
	FROM #newRoleInfos nri

	UPDATE nri
	   SET nri.LoadedRoleID = roles.LoadedRoleID  
	FROM #newRoleInfos nri
	JOIN [SQL].[LoadedRoles] roles ON nri.[DatabaseRoleId] = roles.[DatabaseRoleId] AND roles.LoadedDatabaseID = @LoadedDatabaseID

	INSERT INTO #newRoleSecurablesSett(
		[LoadedRoleSecurablesSettingsID],
		[RoleID],
		[LoadedObjectID],
		[PermissionName],
		[PermissionState],
		[PermissionScope] 
	)
	SELECT lrs.[LoadedRoleSecurablesSettingsID],
		   rs.RoleID,
		   rs.LoadedObjectID,
		   rs.PermissionName,
		   rs.PermissionState,
		   rs.PermissionScope		   
	FROM @RoleSecurables rs
	LEFT JOIN [SQL].[LoadedRoleSecurablesSettings] lrs ON rs.PermissionName = lrs.PermissionName
							  AND rs.PermissionScope = lrs.PermissionScope
							  AND rs.PermissionState = lrs.PermissionState

     INSERT INTO [SQL].[LoadedRoleSecurablesSettings](
		[PermissionName],
		[PermissionState],
		[PermissionScope],
		[Hash]
	 )
	 SELECT [PermissionName],
			[PermissionState],
			[PermissionScope],
			CHECKSUM([PermissionName],
					[PermissionState],
					[PermissionScope])
	 FROM #newRoleSecurablesSett nrs
	 WHERE nrs.LoadedRoleSecurablesSettingsID IS NULL
	 GROUP BY [PermissionName],[PermissionState],[PermissionScope]

	UPDATE nrs
		SET nrs.LoadedRoleSecurablesSettingsID = lrs.LoadedRoleSecurablesSettingsID
	FROM #newRoleSecurablesSett nrs
	JOIN [SQL].[LoadedRoleSecurablesSettings] lrs ON nrs.PermissionName = lrs.PermissionName AND nrs.PermissionScope = lrs.PermissionScope AND nrs.PermissionState = lrs.PermissionState
	WHERE nrs.LoadedRoleSecurablesSettingsID IS NULL 

	INSERT INTO [SQL].[LoadedRoleSecurables](			
			[LoadedRoleID],
			[LoadedObjectID],
			[LoadedRoleSecurablesSettingsID])
	SELECT nri.[LoadedRoleID],	
		   nrs.[LoadedObjectID],
		   nrs.[LoadedRoleSecurablesSettingsID]
	FROM #newRoleSecurablesSett nrs
	JOIN #newRoleInfos nri ON nrs.RoleID = nri.RoleID

	SELECT nri.LoadedRoleID,
		   nri.RoleName
	FROM #newRoleInfos nri

	DROP TABLE #newRoleSecurablesSett
	DROP TABLE #newRoleInfos
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetNumberOfPrincipals]'
GO
ALTER PROCEDURE [dbo].[GetNumberOfPrincipals]
	@LoadingInfoID int
AS
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	
	SELECT 	@Loc_LoadingInfoID = @LoadingInfoID

	SELECT COUNT(p.PrincipalID)
	FROM Principals as p
	JOIN LoadedPrincipals as lp ON p.PrincipalID = lp.PrincipalID AND lp.LoadingInfoID = @Loc_LoadingInfoID
	WHERE p.ExternalUser = 0 AND p.PrincipalTypeID = 2
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [SQL].[LoadedDatabases]'
GO
ALTER TABLE [SQL].[LoadedDatabases] ADD CONSTRAINT [FK_LoadedDatabases_Databases] FOREIGN KEY ([DatabaseId]) REFERENCES [SQL].[Databases] ([DatabaseId])
ALTER TABLE [SQL].[LoadedDatabases] ADD CONSTRAINT [FK_LoadedDatabases_LoadingInfoID] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [SQL].[LoadedObjects]'
GO
ALTER TABLE [SQL].[LoadedObjects] ADD CONSTRAINT [FK_LoadedObjects_DatabaseObjects] FOREIGN KEY ([DatabaseObjectId]) REFERENCES [SQL].[DatabaseObjects] ([DatabaseObjectId]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [SQL].[LoadedRoles]'
GO
ALTER TABLE [SQL].[LoadedRoles] ADD CONSTRAINT [FK_LoadedRoles_DatabaseRoles] FOREIGN KEY ([DatabaseRoleId]) REFERENCES [SQL].[DatabaseRoles] ([DatabaseRoleId]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [SQL].[Tags]'
GO
ALTER TABLE [SQL].[Tags] ADD CONSTRAINT [FK_Tags_TagCategory] FOREIGN KEY ([TagCategoryId]) REFERENCES [SQL].[TagCategory] ([TagCategoryId]) ON DELETE CASCADE
ALTER TABLE [SQL].[Tags] ADD CONSTRAINT [FK_Tags_LoadedServers] FOREIGN KEY ([LoadedServerId]) REFERENCES [SQL].[LoadedServers] ([LoadedServerId])
ALTER TABLE [SQL].[Tags] ADD CONSTRAINT [FK_Tags_Databases] FOREIGN KEY ([DatabaseId]) REFERENCES [SQL].[Databases] ([DatabaseId]) ON DELETE CASCADE
ALTER TABLE [SQL].[Tags] ADD CONSTRAINT [FK_Tags_DatabaseObjects] FOREIGN KEY ([DatabaseObjectId]) REFERENCES [SQL].[DatabaseObjects] ([DatabaseObjectId])
ALTER TABLE [SQL].[Tags] ADD CONSTRAINT [FK_Tags_TagType] FOREIGN KEY ([TagTypeId]) REFERENCES [SQL].[TagType] ([TagTypeId]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
UPDATE Options SET OptionValue = '7.4.2' WHERE OptionID = 14
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

