Constraint Generator
This script can be used for generating the sql script for the constraints available on a particular table. There is a provision to generate script if the specified field(s) are part of the constraint.
While specifing the field name , delimit them with ';'.
Usage is ;
Use pubs
-- for generating the script for the table
exec Pre_Porting @objname = 'titleauthor',
@ScriptAction = 'ADD',
@FieldList = 'IGNORE'
-- for generating the script for the table, filter is job_id
exec Pre_Porting @objname = 'employee',
@ScriptAction = 'ADD',
@FieldList = 'job_id;'
drop procedure Pre_Porting
go
Create procedure Pre_Porting @objname varchar(100) = 'IGNORE',
@ScriptAction char(5) = 'ADD',
@FieldList varchar(100) = 'IGNORE'
-- terminate @feildlist with ; for eg., objectid;hfcid;
as
SET NOCOUNT ON
declare @indid smallint, @objid int, @indtype char(20),
@Field varchar(50), @FieldFound bit, @groupid int, @groupname sysname, @FieldList_temp varchar(100)
declare @thiskey nvarchar(131)
,@cnstdesnvarchar(4000)
,@cnstnamesysname
,@iint
,@cnstidint
,@cnsttypecharacter(5)
,@keysnvarchar(2126)
,@dbnamesysname
If @objname = 'IGNORE'
Return
select @objid = object_id(@objname)
if exists ( select name from sysobjects where name = 'Porting_Cnst' and type = 'u' )
Drop Table Porting_Cnst
Create table Porting_Cnst (
FromTablename varchar(100),
ConstraintType char(5),
ConstraintName varchar(100),
ColumnList varchar(200),
ToTableName varchar(100),
ToColumnList varchar(100),
IndexType char(50) ,
GroupName sysname NULL) -- clustered or non clustered
declare Cur_cnst cursor local static for
select id, xtype, name
from sysobjects
where parent_obj = @objid
and xtype in ('PK','UQ','F')
for read only
Open Cur_cnst
Fetch Cur_cnst into @cnstid ,@cnsttype ,@cnstname
While @@fetch_status >= 0
Begin
If @cnsttype in ('PK','UQ')
Begin
-- get indid and index description
select @indid = indid,
@indtype = case when (status & 16) = 16 then 'CLUSTERED' else 'NONCLUSTERED' end,
@groupid = groupid
fromsysindexes
where name = object_name(@cnstid) and id = @objid
-- Format keys string
select @keys = index_col(@objname, @indid, 1), @i = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
select @keys = '['+ @keys + '] DESC '
select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = '[' + @thiskey + '] DESC '
while (@thiskey is not null)
begin
select @keys = @keys + ', [' + @thiskey +']', @i = @i + 1
select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and
(indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = '[' + @thiskey + '] DESC '
end
Select @FieldFound = 0
If @FieldList = 'IGNORE'
Select @FieldFound = 1
Select @FieldList_temp = @fieldlist
--Check for the fields passed.
while (charindex(';',@fieldlist_temp) > 0)
Begin
select @field = substring(@fieldlist_temp,1,charindex(';',@fieldlist_temp)-1)
if (charindex(@field,@keys) > 0)
Begin
Select @FieldFound = 1
Break
End
select @fieldlist_temp = substring(@fieldlist_temp,charindex(';',@fieldlist_temp)+1, len(@fieldlist_temp))
End
select @groupname = groupname from sysfilegroups where groupid = @groupid
If @FieldFound = 1
Insert into Porting_Cnst (
FromTablename ,
ConstraintType ,
ConstraintName ,
ColumnList ,
ToTableName ,
ToColumnList ,
IndexType,
GroupName )
Values (
@objname,
@cnsttype,
@cnstname,
@keys,
NULL,
NULL,
@indtype,
@groupname )
end -- if pk or uq
Else If @cnsttype in ('F')
Begin
declare @fkeyid int, @rkeyid int
select @fkeyid = fkeyid, @rkeyid = rkeyid
from sysreferences where constid = @cnstid
declare Cur_fkey cursor local for select fkey, rkey from sysforeignkeys where constid = @cnstid
open Cur_fkey
declare @fkeycol smallint, @rkeycol smallint
fetch Cur_fkey into @fkeycol, @rkeycol
select @keys = '['+col_name(@fkeyid, @fkeycol)+']', @cnstdes = '['+col_name(@rkeyid, @rkeycol)+']'
fetch Cur_fkey into @fkeycol, @rkeycol
while @@fetch_status >= 0
begin
select @keys = @keys + ', ' + '['+col_name(@fkeyid, @fkeycol)+']',
@cnstdes = @cnstdes + ', ' + '['+col_name(@rkeyid, @rkeycol)+']'
fetch Cur_fkey into @fkeycol, @rkeycol
end
deallocate Cur_fkey
-- ADD ROWS FOR BOTH SIDES OF FOREIGN KEY
Select @FieldFound = 0
If @FieldList = 'IGNORE'
Select @FieldFound = 1
Select @FieldList_temp = @fieldlist
--Check for the fields passed.
while (charindex(';',@fieldlist_temp) > 0)
Begin
select @field = substring(@fieldlist_temp,1,charindex(';',@fieldlist_temp)-1)
if (charindex(@field,@keys) > 0)
Begin
Select @FieldFound = 1
Break
End
select @fieldlist_temp = substring(@fieldlist_temp,charindex(';',@fieldlist_temp)+1, len(@fieldlist_temp))
End
select @groupname = groupname from sysfilegroups where groupid = @groupid
If @FieldFound = 1
Insert into Porting_Cnst (
FromTablename ,
ConstraintType ,
ConstraintName ,
ColumnList ,
ToTableName ,
ToColumnList ,
IndexType ,
GroupName )
Values (
@objname,
@cnsttype,
@cnstname,
@keys,
OBJECT_NAME(@rkeyid),
@cnstdes,
NULL,
@groupname )
End
Fetch Cur_cnst into @cnstid ,@cnsttype ,@cnstname
End -- Cursor loop
close Cur_cnst
deallocate Cur_cnst
--Look for indexes
declare Cur_Index cursor local for
select indid,name,
case when (status & 2) <> 0 then 'UNIQUE ' end + case when (status & 16) = 16 then 'CLUSTERED' else 'NONCLUSTERED' end,
'IND'
from sysindexes
where id = @objid
and indid > 0 and indid < 255 and (status & 64)=0 order by indid
Open Cur_Index
Fetch Cur_Index into @indid , @cnstname, @indtype ,@cnsttype
While @@fetch_status >= 0
Begin
select @keys = '['+index_col(@objname, @indid, 1)+']', @i = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
select @keys = @keys + ' DESC'
select @thiskey = '['+index_col(@objname, @indid, @i)+']'
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + ' DESC'
while (@thiskey is not null)
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = '['+index_col(@objname, @indid, @i)+']'
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + ' DESC'
end
Select @FieldFound = 0
If @FieldList = 'IGNORE'
Select @FieldFound = 1
Select @FieldList_temp = @fieldlist
--Check for the fields passed.
while (charindex(';',@fieldlist_temp) > 0)
Begin
select @field = substring(@fieldlist_temp,1,charindex(';',@fieldlist_temp)-1)
if (charindex(@field,@keys) > 0)
Begin
Select @FieldFound = 1
Break
End
select @fieldlist_temp = substring(@fieldlist_temp,charindex(';',@fieldlist_temp)+1, len(@fieldlist_temp))
End
select @groupname = groupname from sysfilegroups where groupid = @groupid
If @FieldFound = 1
Insert into Porting_Cnst (
FromTablename ,
ConstraintType ,
ConstraintName ,
ColumnList ,
ToTableName ,
ToColumnList ,
IndexType ,
GroupName )
Values (
@objname,
@cnsttype,
@cnstname,
@keys,
NULL,
NULL,
@indtype,
@groupname )
Fetch Cur_Index into @indid , @cnstname, @indtype, @cnsttype
End
close Cur_Index
deallocate Cur_Index
-- Delete the duplicate constraints
Create Clustered Index Porting_Cnst_CI on Porting_Cnst (FromTableName,ConstraintType)
Delete a
From Porting_Cnst a, Porting_Cnst b
Where a.FromTableName = b.FromTableName
And a.ConstraintType = 'IND'
And a.ConstraintName = b.ConstraintName
And b.ConstraintType = 'PK'
SELECT * FROM PORTING_CNST
If @ScriptAction IN ('ADD' ,'DROP')
Begin
Select 'Script Generation starts....'
EXEC Arc_ConstraintScriptGen @ActionType = @ScriptAction
Select 'Script Generation ends....'
End
Return
GO
--
-- Procedure to Create Add/Drop script for the constraints in the temp table
Drop procedure Arc_ConstraintScriptGen
go
Create procedure Arc_ConstraintScriptGen ( @ActionType varchar(5) = 'ADD' )
As
SET NOCOUNT ON
declare @FromTablename varchar(100),
@ConstraintType char(5),
@ConstraintName varchar(100),
@ColumnList varchar(200),
@ToTableName varchar(100),
@ToColumnList varchar(100),
@IndexType char(20) ,
@strsql varchar(1000),
@groupname sysname
declare Cur_cnst cursor local static for
select FromTableName, ConstraintType, ConstraintName, ColumnList, ToTableName,
ToColumnList, IndexType, GroupName
from Porting_Cnst
Order by FromTableName, ConstraintType
for read only
Open Cur_cnst
Fetch Cur_cnst into @FromTableName, @ConstraintType, @ConstraintName, @ColumnList,
@ToTableName, @ToColumnList, @IndexType, @groupname
While @@fetch_status >= 0
Begin
If @ConstraintType = 'PK'
Begin
If @ActionType = 'ADD'
Select @StrSql = 'ALTER TABLE [' + @FromTableName + '] WITH NOCHECK ADD' + char(10) +
'CONSTRAINT [' + @ConstraintName + '] PRIMARY KEY ' + @IndexType + char(10) +
'(' + char(10) + ' ' + @ColumnList + char(10) + ') ' + 'ON ' + @groupname + char(10) + 'GO'
Else if @ActionType = 'DROP'
Select @StrSql = 'ALTER TABLE ' + @FromTableName + ' DROP CONSTRAINT ' + @ConstraintName + char(10) + 'GO'
End
If @ConstraintType = 'F'
Begin
If @ActionType = 'ADD'
Select @StrSql = 'ALTER TABLE [' + @FromTableName + '] ADD' + char(10) +
'CONSTRAINT [' + @ConstraintName + '] FOREIGN KEY ' + char(10) +
'(' + char(10) + ' ' + @ColumnList + char(10) + ') REFERENCES ' +
@ToTableName + ' (' + char(10) + @ToColumnList + char(10) + ')'
Else if @ActionType = 'DROP'
Select @StrSql = 'ALTER TABLE ' + @FromTableName + ' DROP CONSTRAINT ' + @ConstraintName + char(10) + 'GO'
End
If @ConstraintType = 'IND'
Begin
If @ActionType = 'ADD'
Select @StrSql = 'CREATE ' + RTRIM(@IndexType) + ' INDEX ' + @ConstraintName + ' ON ' +
'['+@FromTableName+']('+@ColumnList +')' + ' ON ' + @groupname + char(10)+'GO'
Else if @ActionType = 'DROP'
Select @StrSql = 'DROP INDEX ' + @FromTableName +'.'+@ConstraintName + char(10)+'GO'
End
select @strsql
Fetch Cur_cnst into @FromTableName, @ConstraintType, @ConstraintName, @ColumnList,
@ToTableName, @ToColumnList, @IndexType, @groupname
End
Return
GO 
