if exists (select * from dbo.sysobjects
            where id = object_id(N'dbo.up_AlterADT') and
      OBJECTPROPERTY(id, N'IsProcedure') = 1)
     drop procedure dbo.up_AlterADT
GO

create procedure dbo.up_AlterADT(@AdtName sysname, @NewAdtName sysname)as

begin

set nocount on

------------------------------------------------------------------------------
-- Part 1. Declare variables, table variables to store intermiadiate results
------------------------------------------------------------------------------
declare @TableImpacted table (RowNumber int identity(1,1), ConstraintType 
sysname, TableName sysname)

declare @TableColumnImpacted table (RowNumber int identity(1,1), 
ConstraintType sysname,
                                    TableName sysname, ColumnName sysname)

-- table variables to store generated drop constraint statements
declare @DropPKConstraint table (
          RowNumber int identity(1,1), TableName sysname, 
ManageConstraintSqlStmt varchar(300))

declare @DropFKConstraint table (
          RowNumber int identity(1,1), TableName sysname, 
ManageConstraintSqlStmt varchar(300))

declare @DropUQConstraint table (
          RowNumber int identity(1,1), TableName sysname, 
ManageConstraintSqlStmt varchar(300))

declare @DropCheckConstraint table (
          RowNumber int identity(1,1), TableName sysname, 
ManageConstraintSqlStmt varchar(300))


-- table variables to store generated create constraint statements
declare @CreatePKConstraint table (
          RowNumber int identity(1,1), TableName sysname, 
ManageConstraintSqlStmt varchar(300))

declare @CreateFKConstraint table (
          RowNumber int identity(1,1), TableName sysname, 
ManageConstraintSqlStmt varchar(300))

declare @CreateUQConstraint table (
          RowNumber int identity(1,1), TableName sysname, 
ManageConstraintSqlStmt varchar(300))

declare @CreateCheckConstraint table (
          RowNumber int identity(1,1), TableName sysname, 
ManageConstraintSqlStmt varchar(300))


declare @CheckBase table (constid int, [id] int, colid int)
declare @CheckConstraintListed table (TableName sysname null, ColumnName 
sysname null)


declare @TableName               		nvarchar(776)
declare @ConstraintType          		char(2)
declare @CurrentConstraint       		varchar(300)
declare @NumberOfRowsAffected    		smallint


declare @CurrentRow              		smallint
declare @CurrentSQLStatement     		nvarchar(1000)
declare @CurrentConstraintType   		sysname
declare @CurrentTableName        		sysname

declare @NumberOfPKConstraintToProcess    	smallint
declare @NumberOfFKConstraintToProcess    	smallint
declare @NumberOfUQConstraintToProcess    	smallint
declare @NumberOfCheckConstraintToProcess 	smallint
declare @NumberOfColumnsAffected          	smallint

set @NumberOfPKConstraintToProcess    = 0
set @NumberOfFKConstraintToProcess    = 0
set @NumberOfUQConstraintToProcess    = 0
set @NumberOfCheckConstraintToProcess = 0
set @NumberOfColumnsAffected          = 0

set @NumberOfRowsAffected = 0
set @CurrentRow = 1


----------------------------------------------------------------
-- Part 2. Identify tables and columns that have Check Constraints and have
--         ADT linked to these columns
----------------------------------------------------------------

insert into @CheckBase(constid, [id], colid)
select sysc.constid, sysc.id, sysc.colid
from  sysobjects syso inner join  sysconstraints sysc on
syso.id = sysc.constid and syso.type = 'c'
where sysc.constid is not null
--select * from @CheckBase

insert into @CheckConstraintListed(TableName, ColumnName)
select syso.[name], syscol.[name]
from @CheckBase tmp inner join sysobjects syso on tmp.id = syso.id
inner join syscolumns syscol on tmp.id = syscol.id and tmp.colid = 
syscol.colid
--select * from @CheckConstraintListed


-------------------------------------------------------------------------------
-- Part 3. Identify tables and type of constraints affected by data type 
change.
-------------------------------------------------------------------------------
insert into @TableImpacted(ConstraintType, TableName)
select distinct TC.Constraint_Type, CU.Table_Name
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU inner join
     INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
     on
         CU.Constraint_Catalog = TC.Constraint_Catalog
     and CU.Constraint_Schema  = TC.Constraint_Schema
     and CU.Constraint_Name    = TC.Constraint_Name
     and CU.Table_Catalog      = TC.Table_Catalog
     and CU.Table_Schema       = TC.Table_Schema
     and CU.Table_Name         = TC.Table_Name
     and CU.Constraint_Name    = TC.Constraint_Name
     inner join INFORMATION_SCHEMA.Columns C
     on CU.Column_Name         = C.Column_Name
     and CU.Table_Catalog      = C.Table_Catalog
     and CU.Table_Schema       = C.Table_Schema
     and CU.Table_Name         = C.Table_Name
     and Domain_Name           = @AdtName
union all
select distinct 'CHECK', CCL.TableName
from @CheckConstraintListed CCL
     inner join INFORMATION_SCHEMA.Columns C
     on
         CCL.TableName = C.Table_Name
     and CCL.ColumnName = C.Column_Name
     and C.Domain_Name  = @AdtName

set @NumberOfRowsAffected = @@RowCount

-- select '@TableImpacted', A.* from @TableImpacted A



--------------------------------------------------------------------------------------------
-- Part 4. Identify tables, columns and type of constraints affected by data 
type change.
--------------------------------------------------------------------------------------------

insert into @TableColumnImpacted(ConstraintType, TableName, ColumnName)
select TC.Constraint_Type, CU.Table_Name, CU.Column_Name
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU inner join
     INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
     on
         CU.Constraint_Catalog = TC.Constraint_Catalog
     and CU.Constraint_Schema  = TC.Constraint_Schema
     and CU.Constraint_Name    = TC.Constraint_Name
     and CU.Table_Catalog      = TC.Table_Catalog
     and CU.Table_Schema       = TC.Table_Schema
     and CU.Table_Name         = TC.Table_Name
     and CU.Constraint_Name    = TC.Constraint_Name
     inner join INFORMATION_SCHEMA.Columns C
     on CU.Column_Name         = C.Column_Name
     and CU.Table_Catalog      = C.Table_Catalog
     and CU.Table_Schema       = C.Table_Schema
     and CU.Table_Name         = C.Table_Name
     and Domain_Name           = @AdtName
union all
select distinct 'CHECK', CCL.TableName, C.Column_Name as ColumnName
from @CheckConstraintListed CCL
     inner join INFORMATION_SCHEMA.Columns C
     on
         CCL.TableName = C.Table_Name
     and CCL.ColumnName = C.Column_Name
     and C.Domain_Name  = @AdtName
-- select '@TableColumnImpacted', A.* from @TableColumnImpacted A

set @NumberOfColumnsAffected = @@RowCount



--------------------------------------------------------------------------------------------
-- Part 5.  Generate create and drop table constraints sql statements for 
all constraints
--          that are tied with columns that have ADT link to them and save 
source code
--------------------------------------------------------------------------------------------

	while @CurrentRow <= @NumberOfRowsAffected
	    Begin
                  select @CurrentConstraintType = ConstraintType
                        ,@CurrentTableName = TableName
                   from @TableImpacted
                   where RowNumber = @CurrentRow
             if @CurrentConstraintType = 'Primary Key'
                begin
                insert into @CreatePKConstraint(TableName, 
ManageConstraintSqlStmt)
                select
                       @CurrentTableName
                      ,'alter table '      +
                       'dbo.'              +
                       @CurrentTableName   +
                       '  add constraint ' +
                       ConstraintName      +
                       ' '                 +
                       ConstraintType      +
                       ' ('                +
                       ConstraintKeys      +
                        ')'
                from dbo.ufn_helpconstraint(@CurrentTableName, 'PK')

                insert into @DropPKConstraint(TableName, 
ManageConstraintSqlStmt)
                select
                      @CurrentTableName
                      ,'alter table '       +
                       'dbo.'               +
                        @CurrentTableName   +
                       '  drop constraint ' +
                      ConstraintName
                 from dbo.ufn_helpconstraint(@CurrentTableName, 'PK')
                 end
             if @CurrentConstraintType = 'Foreign Key'
                begin
                insert into @CreateFKConstraint(TableName, 
ManageConstraintSqlStmt)
                select
                       @CurrentTableName
                      ,'alter table '        +
                       'dbo.'                +
                       @CurrentTableName     +
                       '  add constraint '   +
                       A.ConstraintName        +
                       ' '                   +
                       A.ConstraintType        +
                       ' (' + ConstraintKeys +
                       ')'                   +
                       ' '                   +
                       FkReferences
                from dbo.ufn_helpconstraint(@CurrentTableName, 'F')  as A 
inner join
			@TableColumnImpacted as B on B.TableName = @CurrentTableName and 
B.ConstraintType = 'FOREIGN KEY'
			and A.ConstraintType = 'FOREIGN KEY'
			and A.ConstraintKeys = B.ColumnName

                insert into @DropFKConstraint(TableName, 
ManageConstraintSqlStmt)
                select
                       @CurrentTableName
                      ,'alter table '       +
                       'dbo.'               +
                        @CurrentTableName          +
                       '  drop constraint ' +
                       A.ConstraintName
                 from dbo.ufn_helpconstraint(@CurrentTableName, 'F')  as A 
inner join
			@TableColumnImpacted as B on B.TableName = @CurrentTableName and 
B.ConstraintType = 'FOREIGN KEY'
			and a.ConstraintType = 'FOREIGN KEY'
			and A.ConstraintKeys = B.ColumnName
               end

             if @CurrentConstraintType = 'UNIQUE'
                begin
                insert into @CreateUQConstraint(TableName, 
ManageConstraintSqlStmt)
             	select distinct
                       @CurrentTableName
                      ,'alter table '        +
                       'dbo.'                +
                       @CurrentTableName     +
                       '  add constraint '   +
                       A.ConstraintName        +
                       ' '                   +
                       A.ConstraintType        +
                       ' (' + A.ConstraintKeys +
                       ')'
		      from dbo.ufn_helpconstraint(@CurrentTableName, 'UQ')  as A,
			@TableColumnImpacted as B
			where A.ConstraintKeys like '%' + B.ColumnName + '%'
			and B.TableName = @CurrentTableName

                insert into @DropUQConstraint(TableName, 
ManageConstraintSqlStmt)
                select distinct
                       @CurrentTableName
                      ,'alter table '       +
                       'dbo.'               +
                        @CurrentTableName          +
                       '  drop constraint ' +
                       A.ConstraintName
			from dbo.ufn_helpconstraint(@CurrentTableName, 'UQ')  as A,
			@TableColumnImpacted as B
			where A.ConstraintKeys like '%' + B.ColumnName + '%'
			and B.TableName = @CurrentTableName
                 end

              if @CurrentConstraintType = 'CHECK'
                begin

                insert into @CreateCheckConstraint(TableName, 
ManageConstraintSqlStmt)
                select
                       @CurrentTableName
                      ,'alter table '        +
                       'dbo.'                +
                       @CurrentTableName     +
                       '  add constraint '   +
                       ConstraintName        +
                       ' '                   +
                       'CHECK'               +
                       ' (' + ConstraintKeys +
                       ')'
                from dbo.ufn_helpconstraint(@CurrentTableName, 'C ') as A 
inner join
			@TableColumnImpacted as B on B.TableName = @CurrentTableName and 
B.ConstraintType = 'CHECK'
			and B.ConstraintType = 'CHECK'
			and ltrim(rtrim(replace(A.ConstraintType , 'CHECK on column', ' '))) = 
B.ColumnName

                insert into @DropCheckConstraint(TableName, 
ManageConstraintSqlStmt)
                select
                       @CurrentTableName
                      ,'alter table '       +
                       'dbo.'               +
                        @CurrentTableName          +
                       '  drop constraint ' +
                       ConstraintName
			from dbo.ufn_helpconstraint(@CurrentTableName, 'C ') as A inner join
			@TableColumnImpacted as B on B.TableName = @CurrentTableName and 
B.ConstraintType = 'CHECK'
			and B.ConstraintType = 'CHECK'
			and ltrim(rtrim(replace(A.ConstraintType , 'CHECK on column', ' '))) = 
B.ColumnName
                 end


		Select @CurrentRow = @CurrentRow + 1

	     end

--   select 1, 'CreateFKConstraint'    as CreateFKConstraint,    * from 
@CreateFKConstraint
--   select 2, 'DropFKConstraint'      as DropFKConstraint,      * from 
@DropFKConstraint
--   select 3, 'DropCheckConstraint'   as DropCheckConstraint,   * from 
@DropCheckConstraint
--   select 4, 'DropUQConstraint'      as DropUQConstraint,      * from 
@DropUQConstraint
--   select 5, 'CreateCheckConstraint' as CreateCheckConstraint, * from 
@CreateCheckConstraint
--   select 6, 'CreateCheckConstraint' as CreatePKConstraint,    * from 
@CreatePKConstraint
--   select 7, 'DropPKConstraint'      as DropPKConstraint,      * from 
@DropPKConstraint
--   select 8, 'CreateUQConstraint'    as CreateUQConstraint,      * from 
@CreateUQConstraint


select @NumberOfPKConstraintToProcess    = max(RowNumber) from 
@CreatePKConstraint
select @NumberOfFKConstraintToProcess    = max(RowNumber) from 
@CreateFKConstraint
select @NumberOfUQConstraintToProcess    = max(RowNumber) from 
@CreateUQConstraint
select @NumberOfCheckConstraintToProcess = max(RowNumber) from 
@CreateCheckConstraint

-- select @NumberOfUQConstraintToProcess




------------------------------------------------------------------------
-- Part 6. Drop all table constraints identified in previous step
------------------------------------------------------------------------
Select @CurrentRow = 1
	while @CurrentRow <= @NumberOfFKConstraintToProcess
	    Begin
		select @CurrentSQLStatement = ManageConstraintSqlStmt
		from @DropFKConstraint
		where RowNumber = @CurrentRow
--    		select '1 ' + @CurrentSQLStatement
		exec sp_executesql @CurrentSQLStatement
		Select @CurrentRow = @CurrentRow + 1
	     end

Select @CurrentRow = 1
	while @CurrentRow <= @NumberOfPKConstraintToProcess
	    Begin
		select @CurrentSQLStatement = ManageConstraintSqlStmt
		from @DropPKConstraint
		where RowNumber = @CurrentRow
--   		select @CurrentSQLStatement
		exec sp_executesql @CurrentSQLStatement
		Select @CurrentRow = @CurrentRow + 1
	     end
Select @CurrentRow = 1
	while @CurrentRow <= @NumberOfUQConstraintToProcess
	    Begin
		select @CurrentSQLStatement = ManageConstraintSqlStmt
		from @DropUQConstraint
		where RowNumber = @CurrentRow
--   		select @CurrentSQLStatement
		exec sp_executesql @CurrentSQLStatement
		Select @CurrentRow = @CurrentRow + 1
	     end
Select @CurrentRow = 1
	while @CurrentRow <= @NumberOfCheckConstraintToProcess
	    Begin
		select @CurrentSQLStatement = ManageConstraintSqlStmt
		from @DropCheckConstraint
		where RowNumber = @CurrentRow
--    		select @CurrentSQLStatement
		exec sp_executesql @CurrentSQLStatement
		Select @CurrentRow = @CurrentRow + 1
	     end

------------------------------------------------------------------------
-- Part 7. Link new ADT to selected columns
------------------------------------------------------------------------

set @CurrentRow = 1
	while @CurrentRow <= @NumberOfColumnsAffected
	    Begin

             select @CurrentSQLStatement =
                   'alter table '   +
                   'dbo.'           +
                   TableName        +
                   ' alter column ' +
                   ColumnName       +
                   ' '              +
                   @NewAdtName
             from @TableColumnImpacted
              where RowNumber = @CurrentRow
-- select @CurrentSQLStatement
             exec sp_executesql @CurrentSQLStatement
             Select @CurrentRow = @CurrentRow + 1
	     end

------------------------------------------------------------------------
-- Part 8. Recreate all dropped constraints
------------------------------------------------------------------------
Select @CurrentRow = 1
	while @CurrentRow <= @NumberOfCheckConstraintToProcess
	    Begin
		select @CurrentSQLStatement = ManageConstraintSqlStmt
		from @CreateCheckConstraint
		where RowNumber = @CurrentRow
-- 		select @CurrentSQLStatement
		exec sp_executesql @CurrentSQLStatement
		Select @CurrentRow = @CurrentRow + 1
            end

Select @CurrentRow = 1
	while @CurrentRow <= @NumberOfPKConstraintToProcess
	    Begin
		select @CurrentSQLStatement = ManageConstraintSqlStmt
		from @CreatePKConstraint
		where RowNumber = @CurrentRow
-- 		select @CurrentSQLStatement
		exec sp_executesql @CurrentSQLStatement
		Select @CurrentRow = @CurrentRow + 1
	     end
Select @CurrentRow = 1
	while @CurrentRow <= @NumberOfUQConstraintToProcess
	    Begin
		select @CurrentSQLStatement = ManageConstraintSqlStmt
		from @CreateUQConstraint
		where RowNumber = @CurrentRow
-- 		select @CurrentSQLStatement
		exec sp_executesql @CurrentSQLStatement
		Select @CurrentRow = @CurrentRow + 1
            end

Select @CurrentRow = 1
	while @CurrentRow <= @NumberOfFKConstraintToProcess
	    Begin
		select @CurrentSQLStatement = ManageConstraintSqlStmt
		from @CreateFKConstraint
		where RowNumber = @CurrentRow
-- 		select @CurrentSQLStatement
		exec sp_executesql @CurrentSQLStatement
		Select @CurrentRow = @CurrentRow + 1
	     end

-- select '1 after ', A.* from @DropFKConstraint A
-- select '2 after ', A.* from @DropPKConstraint A
set nocount off

end


--------------------------------
-- Part 9. Test section
--------------------------------
-- here is an example of how to execute the procedure
-- declare @tAdtName sysname
-- set @tAdtName        = 'adt_IntId'
-- declare @tNewAdtName sysname
-- set @tNewAdtName        = 'adt_BigIntId'
-- exec dbo.up_AlterADT @tAdtName, @tNewAdtName

-- here are queries difined in the article
-- select *
-- from INFORMATION_SCHEMA.DOMAINS
-- where Domain_Name = 'adt_BigIntId'

-- select *
-- from INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
-- where Table_Name in ('tblDepartment', 'tblTestEmployee')
--
-- select *
-- from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
-- where Table_Name in ('tblDepartment', 'tblTestEmployee')

-- SELECT
--  TABLE_NAME               as TableName
-- ,COLUMN_NAME              as ColumnName
-- ,DATA_TYPE                as DataType
-- FROM
-- INFORMATION_SCHEMA.COLUMNS
-- WHERE
-- Table_Name in ( 'tblTestEmployee', 'tblDepartment', 'tblContractor', 
'tblPayroll', 'tblBonus')
-- and Column_Name = 'DepartmentId'