A way of parsing the definition data in catalog view sys.check_constraint

Listed below is an interesting piece of T-SQL code to parse the definition data for “check” type of constraints in a database (SQL Server 2005). The main goal is to enlist all the individual values (numeric or character alike) defined as a check constraint which are stored in one condensed string of characters, such as “EnvID = 15 OR EnvID = 20 OR EnvID=45” inside a meta data table. However, this code is not intended for those check constraints containing comparing operators such as “>”, “<“, “>=”, “<=”, and logical operators such as “AND”, “NOT”, etc.

USEdatabasename
GO

declare @tabname varchar(100)
declare @colname varchar(100)
declare @data varchar(50)
declare @wkstr varchar(1000)
declare @replace1 varchar(20)
declare @replace2 varchar(20)
declare @pointer int

ifobject_id(‘CheckConstraintsValues’)<> 0
drop table CheckConstraintsValues
else
createtable CheckConstraintsValues (
       tabname varchar(50), 
       colname varchar(50), 
       checkvalue varchar(50))
on [PRIMARY] 

declare cur_constr cursorfor
selectobject_name( parent_object_id ), c.definition, l.name
fromsys.check_constraints c joinsys.columns l
on l.object_id= c.parent_object_id and l.column_id=c.parent_column_id
where definition notlike‘%>%’and definition notlike‘%<%’

open cur_constr
fetch next from cur_constr into @tabname, @wkstr, @colname
while@@fetch_status= 0
begin
       select @replace1 =‘[‘+ @colname +‘]=’;
       select @replace2 =‘ OR ‘
       select @wkstr =replace ( replace(@wkstr, @replace1,), @replace2,‘,’)
       select @wkstr =replace(replace(@wkstr,‘(‘,),‘)’,)
       select @wkstr =replace(replace(@wkstr,””,),””,)
       while 2 > 1
       begin
              set @pointer = 1
              select @pointer =charindex(‘,’, @wkstr, @pointer)
              if  @pointer = 0 andlen(@wkstr)> 0
                     begin
                           select  @data = @wkstr
                           break
                     end 
              select @data =left(@wkstr, @pointer1 )
              select @wkstr =right(@wkstr,(len( @wkstr ) @pointer )) 
              insertinto CheckConstraintsValues values( @tabname, @colname, @data)
       end 
              insertinto CheckConstraintsValues values( @tabname, @colname, @data)
       fetch next from cur_constr into @tabname, @wkstr, @colname
end
close cur_constr
deallocate cur_constr

Advertisements
This entry was posted in Blogroll. Bookmark the permalink.

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