Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Reverse Engineering Alias Data Types in SQL Server 2000 - Part IV

By Yakov Shlafman,

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.
  10. Credits:
    my wife, my kids, my teacher Ilya Petrenko
    my coworkers from Viatech Inc.
    sqlservercentral Forums

Resources:

Create set of tables with PK, FK, UQ, CC and ADT.txt | up_AlterAdt.txt
Total article views: 1625 | Views in the last 30 days: 7
 
Related Articles
SCRIPT

Fix Column Check Constraint Naming

This stored procedure can be used for applying a custom column check constraint naming convention

FORUM

Check Constraint with Case Statement

Check Constraint with Case Statement

SCRIPT

Conversion of rule objects to column check constraints

This script can convert the usage of bound rule objects in tables to column check constraints

FORUM

Creating a Check Constraint

Check Constraint

FORUM

Is CHECK constraint same as TRIGGER?

Is CHECK constraint = After TRIGGER?

Tags
alias data types    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones