
----------------------------------------------------
--
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.Common.GetWrappedTableName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.Common.GetWrappedTableName]
GO
CREATE FUNCTION [dbo].[fn.Upgrade.Common.GetWrappedTableName] (@table_name nvarchar(255))
RETURNS nvarchar(max)
AS
BEGIN
	declare @result nvarchar(max)
	set @result = '[dbo].' + '[' + @table_name + ']'
	return @result
END
GO
--
----------------------------------------------------






----------------------------------------------------
--
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.Common.GetRenamedTableName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.Common.GetRenamedTableName]
GO
CREATE FUNCTION [dbo].[fn.Upgrade.Common.GetRenamedTableName] (@table_name nvarchar(255), @table_prefix nvarchar(255))
RETURNS nvarchar(max)
AS
BEGIN
	declare @result nvarchar(max)
	set @result = @table_prefix + '.' + @table_name
	return @result
END
GO
--
----------------------------------------------------






----------------------------------------------------
--
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.Common.GetCurrentTableName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.Common.GetCurrentTableName]
GO
CREATE FUNCTION [dbo].[fn.Upgrade.Common.GetCurrentTableName] (@table_name nvarchar(255))
RETURNS nvarchar(max)
AS
BEGIN
	declare @result nvarchar(max)
	set @result = [dbo].[fn.Upgrade.Common.GetRenamedTableName] (@table_name, 'C')
	return @result
END
GO
--
----------------------------------------------------





----------------------------------------------------
--
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Upgrade.Common.RenameTable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Upgrade.Common.RenameTable]
GO
CREATE PROCEDURE [dbo].[usp.Upgrade.Common.RenameTable]
	@newtable_name nvarchar(255),
	@oldtable_name nvarchar(255)
AS
BEGIN
	SET NOCOUNT ON;		

	declare @current_table nvarchar(255)
	set @current_table = [dbo].[fn.Upgrade.Common.GetWrappedTableName] (@oldtable_name)

	exec sp_rename @current_table, @newtable_name
END
GO
--
----------------------------------------------------


----------------------------------------------------
--
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Upgrade.Common.DuplicateTable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Upgrade.Common.DuplicateTable]
GO
CREATE PROCEDURE [dbo].[usp.Upgrade.Common.DuplicateTable]
	@duplicated_table nvarchar(255),
	@table_name nvarchar(255)
AS
BEGIN
	SET NOCOUNT ON;				
	
	declare @new_table nvarchar(255)
	set @new_table = [dbo].[fn.Upgrade.Common.GetWrappedTableName] (@duplicated_table)
	
	declare @current_table nvarchar(255)
	set @current_table = [dbo].[fn.Upgrade.Common.GetWrappedTableName] (@table_name)
	
	declare @statement nvarchar(max)
	set @statement = 'SELECT * INTO ' + @new_table + ' FROM ' + @current_table + ' where 1 = 0'
	print @statement
	exec sp_executesql @statement

END
GO
--
----------------------------------------------------



----------------------------------------------------
--
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Upgrade.Common.DropColumn]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.Upgrade.Common.DropColumn]
GO
CREATE PROCEDURE [dbo].[usp.Upgrade.Common.DropColumn]
	@table_name nvarchar(255),
	@column_name nvarchar(255)
	
AS
BEGIN
	SET NOCOUNT ON;				

	if exists (select * from syscolumns where id = OBJECT_ID(@table_name) and [name] = @column_name)
	begin
	
		DECLARE @default sysname
		SELECT @default = object_name(default_object_id)
		FROM sys.columns
		WHERE object_id = object_id(@table_name)
		AND name = @column_name

		EXEC ('ALTER TABLE ' + @table_name + ' DROP constraint [' + @default + ']')		
		EXEC ('alter table ' + @table_name + ' drop column [' + @column_name + ']')
		
	end	

END
GO
--
----------------------------------------------------



----------------------------------------------------
--
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Upgrade.Common.JoinCommonTableColumnsToString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Upgrade.Common.JoinCommonTableColumnsToString]
GO
CREATE FUNCTION [dbo].[fn.Upgrade.Common.JoinCommonTableColumnsToString] (@table1_name nvarchar(255), @table2_name nvarchar(255))
RETURNS nvarchar(max)
AS
BEGIN
	
	DECLARE @table_cols as TABLE ( col_name nvarchar(255))
	INSERT INTO @table_cols
	SELECT name FROM sys.columns WHERE object_id=OBJECT_ID([dbo].[fn.Upgrade.Common.GetWrappedTableName](@table1_name))
	
	DELETE FROM @table_cols
	WHERE col_name NOT IN (SELECT name FROM sys.columns WHERE object_id=OBJECT_ID([dbo].[fn.Upgrade.Common.GetWrappedTableName](@table2_name)) )

    DECLARE @c INT, @p NVARCHAR(max), @cNext INT, @pNext NVARCHAR(40) 
    DECLARE c CURSOR FOR SELECT col_name FROM @table_cols 

    OPEN c ;
    FETCH NEXT FROM c INTO @pNext ;
    SET @c = @cNext ;
    WHILE @@FETCH_STATUS = 0 BEGIN 
         IF @cNext > @c BEGIN
              SELECT @p = @PNext, @c = @cNext ;
         END ELSE 
              SET @p = COALESCE(@p + N'], [', SPACE(0)) + @pNext;
         FETCH NEXT FROM c INTO @pNext 
    END 

    CLOSE c ;        
	DEALLOCATE c ;
		
    return '[' + @p + ']';
END
GO
--
----------------------------------------------------



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.RemoveColumnWithDefaultConstraints]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp.RemoveColumnWithDefaultConstraints]
GO
CREATE PROCEDURE [dbo].[usp.RemoveColumnWithDefaultConstraints]
	@tableName nvarchar(max), 
    @columnName nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ConstraintName nvarchar(200)
    SELECT @ConstraintName = Name 
    FROM SYS.DEFAULT_CONSTRAINTS 
    WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) 
        AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = (@columnName) 
        AND object_id = OBJECT_ID(@tableName))
    IF @ConstraintName IS NOT NULL
        EXEC('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT [' + @ConstraintName + ']')

        
    IF EXISTS(SELECT * FROM sys.columns WHERE Name = @columnName  
        AND Object_ID = Object_ID(@tableName))
        EXEC('ALTER TABLE ' + @tableName + ' DROP COLUMN ' + @columnName)       
END
GO