Rename Check Constraints

Querying the sys.check_constraints catalog view will return all check constraints in a database. When the parent_column_id value equals 0, then the constraint is at the table level. A non-zero value means that the constraint references one column. Below are queries to return all column level constraints, and a second query to generate SQL to rename any constraints that don’t fit the naming convention:
‘CK_TableName_ColumnName’

— Check Constraints – Return all column checks

select cc.object_id, cc.name, object_name(cc.parent_object_id) as TableName,
cc.definition, cc.parent_column_id, c.name as ColumnName
from sys.check_constraints as cc
join sys.columns as c
on c.object_id = cc.parent_object_id
and c.column_id = cc.parent_column_id

go

— Rename Check Constraints

select TableName, ColumnName,
‘exec sp_rename ”’ + CurrentConstraintName + ”’, ”’ + NewConstraintName + ””
as RenameSql
from (
select cc.name as CurrentConstraintName, object_name(cc.parent_object_id) as TableName,
c.name as ColumnName,
‘CK_’ + object_name(cc.parent_object_id) + ‘_’ + c.name as NewConstraintName
from sys.check_constraints as cc
join sys.columns as c
on c.object_id = cc.parent_object_id
and c.column_id = cc.parent_column_id
) as t1
where t1.CurrentConstraintName t1.NewConstraintName

go

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: