In this article we are going to see how to change an ADT that is linked to columns with Unique Constraint(s) or Check Constraint(s).

You can define multiple Unique Constraints on a given table and only one Primary Key constraint. You can reference a unique constraint by a Foreign key constraint. Run the script 'Create set of tables with PK, FK, UQ, CC and ADT.sql' to create tables and constraints used for testing. Here is how you can check tables, columns and their data type affected by

change:

SELECT

TABLE_NAME as TableName

,COLUMN_NAME as ColumnName

,DATA_TYPE as DataType

FROM INFORMATION_SCHEMA.COLUMNS

WHERE Table_Name in ( 'tblTestEmployee', 'tblContractor', 'tblPayroll',

'tblBonus', 'tblDepartment')

and Column_Name in ('DepartmentId', 'ContractId', 'EmployeeId', 'BonusId' )

Here is how to create a new ADT that will replace the existing ADT.

if exists( select * from systypes where [name] = 'adt_BigIntId')

Print 'adt_BigIntId exists'

else

exec sp_addtype

@typename = 'adt_BigIntId'

,@phystype = 'Bigint'

,@nulltype = 'NOT NULL'

,@owner = 'dbo'

Here is how to execute the stored procedure:

declare @AdtName sysname

set @AdtName = 'adt_IntId'

declare @NewAdtName sysname

set @NewAdtName = 'adt_BigIntId'

exec dbo.up_AlterADT @AdtName, @NewAdtName

Here is how to check the results:

SELECT

TABLE_NAME as TableName

,COLUMN_NAME as ColumnName

,DATA_TYPE as DataType

FROM INFORMATION_SCHEMA.COLUMNS

WHERE Table_Name in ( 'tblTestEmployee', 'tblContractor', 'tblPayroll',

'tblBonus', 'tblDepartment')

and Column_Name in ('DepartmentId', 'ContractId', 'EmployeeId', 'BonusId' )

And another check - rollback all changes and check the results.

declare @AdtName sysname

set @AdtName = 'adt_BigIntId'

declare @NewAdtName sysname

set @NewAdtName = 'adt_IntId'

exec dbo.up_AlterADT @AdtName, @NewAdtName

A table may have multiple ADTs linked to columns. In our test we have two ADTs linked to test tables: adt_IntId and adt_DateTimeId. In the following steps we will run our stored procedure dbo.up_AlterADT to replace adt_DateTimeId by adt_SmallDateTimeId. Here is how you can check tables, columns and their data type affected by change:

SELECT

TABLE_NAME as TableName

,COLUMN_NAME as ColumnName

,DATA_TYPE as DataType

FROM INFORMATION_SCHEMA.COLUMNS

WHERE Table_Name in ( 'tblSaleDate', 'tblDepartment', 'tblTestEmployee',

'tblContractor', 'tblPayroll', 'tblBonus')

and Column_Name in ('CreatedDate' )

Here is how to create a new ADT that will replace the existing ADT.

if exists( select * from systypes where [name] = 'adt_DateTimeId')

exec sp_droptype 'adt_DateTimeId'

exec sp_addtype

@typename = 'adt_DateTimeId'

,@phystype = 'datetime'

,@nulltype = 'NOT NULL'

,@owner = 'dbo'

Here is how to execute stored procedure:

declare @AdtName sysname

set @AdtName = 'adt_SmallDateTimeId'

declare @NewAdtName sysname

set @NewAdtName = 'adt_DateTimeId'

exec dbo.up_AlterADT @AdtName, @NewAdtName

Here is how to check the results:

SELECT

TABLE_NAME as TableName

,COLUMN_NAME as ColumnName

,DATA_TYPE as DataType

FROM INFORMATION_SCHEMA.COLUMNS

WHERE Table_Name in ( 'tblSaleDate', 'tblDepartment', 'tblTestEmployee',

'tblContractor', 'tblPayroll', 'tblBonus')

and Column_Name in ('CreatedDate' )

The stored procedure dbo.up_AlterADT consists of nine parts:

1. Declare variables, table variables to store intermediate results.

   In this part we need to declare table variables to store the generated create and drop constraint statements. We also declare variables and counters needed to manage logic in the stored procedure. These table variables are used to avoid the use of a cursor.

2. Identify tables and columns that have Check Constraints and ADTs linked to these columns. This is done using an excerpt from: an excellent script 'Find Missing Constraints' by Luke Malyurek, because we can not get all needed information from INFORMATION_SCHEMA views.
3. We need to identify tables and the types of constraints affected by the data type change. To get tables and constraints type that are affected by the change of the ADT, I used these views:
   • INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   • INFORMATION_SCHEMA.TABLE_CONSTRAINTS
4. Identify tables, columns and type of constraints affected by the data type change. This is done using INFORMATION_SCHEMA views. Special logic is used for check constraints.
5. Generate create and drop table constraints SQL statements for all constraints that are tied to columns that have an ADT linked to them and save the source code. This is done by using a user defined table function dbo.ufn_helpconstraint. I hope you will notice the flexibility and benefits of function dbo.ufn_helpconstraint that contributes to stored procedure dbo.up_AlterADT. The procedure handles cases when the constraint is based on more than one column.
6. Drop all table constraints identified in the previous step. All table constraints are dropped using SQL statements generated in previous step.
7. Link the new ADT to selected columns. Link the new ADT to columns identified in part 4.
8. Recreate all the dropped constraints using SQL statements generated in part 5
9. This is the test section. This section contains examples of how to use this stored procedure.

Credits:

my wife, my kids, my teacher Ilya Petrenko

my coworkers from Viatech Inc.


sqlservercentral Forums