Constraint Generator

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