Procedura pro smazání a vytvoření referencí

create procedure usp_GetReferenceScript(@ReferencedTableName sysname)
as
begin
  declare @myTable table(
    RefName   sysname not null,
    RTable    sysname not null,
    FTable    sysname not null,
    RColumn   sysname not null,
    FColumn   sysname not null,
    OnDelete  bit   not null,
    OnUpdate  bit   not null)
  declare @crlf   char(2)
  set @crlf = char(13) + char(10)
  
  set nocount on

  insert into @myTable
    select o.Name,
           r.Name,
           f.Name,
           scr.Name,
           scf.Name,
           objectproperty(ref.constid, 'CnstIsDeleteCascade'),
           objectproperty(ref.constid, 'CnstIsUpdateCascade')
      from sysobjects o
        inner join sysreferences ref
          on (ref.constid = o.id)
        inner join sysobjects r
          on (ref.rkeyid = r.id)
        inner join sysobjects f
          on (ref.fkeyid = f.id)
        inner join syscolumns scr
          on ((scr.id = ref.rkeyid) and (scr.colid = ref.rkey1))
        inner join syscolumns scf
          on ((scf.id = ref.fkeyid) and (scf.colid = ref.fkey1))
      where o.xtype = 'F' and r.Name = @ReferencedTableName
      order by 3
  select 'alter table ' + FTable + @crlf +
         '  drop constraint ' + RefName
    from @myTable
  select 'alter table ' + FTable + @crlf +
         '  add constraint ' + RefName +
         ' foreign key(' + FColumn + ')' + @crlf +
         '  references ' + RTable + ' (' + RColumn + ')' +
         case when OnUpdate = 1
           then @crlf + '  on update cascade'
           else ''
         end +
         case when OnDelete = 1
           then @crlf + '  on delete cascade'
           else ''
         end
    from @myTable
end