use master
go
create procedure sp__fktext
@cnstname varchar(30)
as
-- created 01-23-96 by Clayton Groom. Copyright 1996, all rights
reserved.

declare @objid int              -- the object id of the table
declare @cnstdes varchar(255) -- string to build up index desc
declare @tptr varbinary(16) -- pointer for building text strings.
declare @i int
declare @thiskey varchar(32)
declare @cnststatus int
declare @numkeys int
declare @cnstid int
declare @rkeyid int
declare @fkeyid int
declare @fkey1 tinyint
declare @fkey2 tinyint
declare @fkey3 tinyint
declare @fkey4 tinyint
declare @fkey5 tinyint
declare @fkey6 tinyint
declare @fkey7 tinyint
declare @fkey8 tinyint
declare @fkey9 tinyint
declare @fkey10 tinyint
declare @fkey11 tinyint
declare @fkey12 tinyint
declare @fkey13 tinyint
declare @fkey14 tinyint
declare @fkey15 tinyint
declare @fkey16 tinyint
declare @dbname varchar(30)

set nocount on

declare @table_id int,
@msg varchar(255)

select @cnstid = object_id (@cnstname)

--  constraint doesn't exist so return. Note: need to find correct
error#
if @cnstid is NULL
begin
  select @dbname = db_name()
  raiserror(15009,-1,-1,@cnstname,@dbname)
  -- return (1)
end

select  @cnststatus = c.status
from  sysconstraints c,
  sysobjects o
where  c.constid = @cnstid

create table #spcnstkeys
(
cnst_colid int NOT NULL
)

DECLARE tloop CURSOR
FOR SELECT
constid,
fkeyid,
rkeyid,

fkey1,
fkey2,
fkey3,
fkey4,
fkey5,
fkey6,
fkey7,
fkey8,
fkey9,
fkey10,
fkey11,
fkey12,
fkey13,
fkey14,
fkey15,
fkey16
FROM  sysreferences
where constid = object_id(@cnstname)

OPEN tloop

FETCH NEXT FROM tloop
INTO

@cnstid,
@fkeyid,
@rkeyid,
@fkey1,
@fkey2,
@fkey3,
@fkey4,
@fkey5,
@fkey6,

@fkey7,
@fkey8,
@fkey9,
@fkey10,
@fkey11,
@fkey12,
@fkey13,
@fkey14,
@fkey15,
@fkey16

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
   insert into #spcnstkeys values(@fkey1)
   insert into #spcnstkeys values(@fkey2)
   insert into #spcnstkeys values(@fkey3)
   insert into #spcnstkeys values(@fkey4)
   insert into #spcnstkeys values(@fkey5)
   insert into #spcnstkeys values(@fkey6)
   insert into #spcnstkeys values(@fkey7)
   insert into #spcnstkeys values(@fkey8)
   insert into #spcnstkeys values(@fkey9)
   insert into #spcnstkeys values(@fkey10)
   insert into #spcnstkeys values(@fkey11)
   insert into #spcnstkeys values(@fkey12)
   insert into #spcnstkeys values(@fkey13)
   insert into #spcnstkeys values(@fkey14)
   insert into #spcnstkeys values(@fkey15)
   insert into #spcnstkeys values(@fkey16)

   delete from #spcnstkeys where cnst_colid = 0


   /* Need a unique index so we can use a cursor. */
   create unique index ind1 on #spcnstkeys(cnst_colid)

   exec('declare fkey_curs cursor for select cnst_colid
    from #spcnstkeys for read only')

   open fkey_curs
   fetch fkey_curs into @i
   select @numkeys=1
   while @@fetch_status >= 0
                        begin
    select @thiskey = col_name(@fkeyid, @i)

    /* If it's the first component of the key, don't
    ** stick a ', ' in front of it. */

     if @numkeys = 1
     begin
      select @cnstdes = @thiskey
     end
    else
     begin
                           select @cnstdes  = @cnstdes + ', ' + @thiskey

     end
    select @numkeys=@numkeys+1
    fetch fkey_curs into @i
   end
   truncate table #spcnstkeys
   drop index #spcnstkeys.ind1
   deallocate fkey_curs

SELECT @msg = 'ALTER TABLE '
  + object_name(@fkeyid) + char( 13 ) + char( 10 ) +
   ' ADD CONSTRAINT '
  + object_name( @cnstid)
  + ' FOREIGN KEY ( '
  + @cnstdes + ' )' + char( 13 ) + char( 10 ) +
  '  REFERENCES '
  + object_name(@rkeyid)

select @msg + char( 13 ) + char( 10 ) + 'go'

END -- OF IF (@@FETCH_STATUS <> -2) for tloop

FETCH NEXT FROM tloop
INTO
@cnstid,
@fkeyid,
@rkeyid,
@fkey1,
@fkey2,
@fkey3,
@fkey4,
@fkey5,
@fkey6,
@fkey7,
@fkey8,
@fkey9,
@fkey10,
@fkey11,
@fkey12,
@fkey13,
@fkey14,
@fkey15,
@fkey16

END  --+++ OF WHILE (@@FETCH_STATUS <> -1) for tloop

DEALLOCATE tloop




GO
create procedure sp__fktext
@cnstname varchar(30)
as
-- created 01-23-96 by Clayton Groom. Copyright 1996, all rights
reserved

declare @objid int              -- the object id of the table
declare @cnstdes varchar(255) -- string to build up index desc
declare @tptr varbinary(16) -- pointer for building text strings.
declare @i int
declare @thiskey varchar(32)
declare @cnststatus int
declare @numkeys int
declare @cnstid int
declare @rkeyid int
declare @fkeyid int
declare @fkey1 tinyint
declare @fkey2 tinyint
declare @fkey3 tinyint
declare @fkey4 tinyint
declare @fkey5 tinyint
declare @fkey6 tinyint
declare @fkey7 tinyint
declare @fkey8 tinyint
declare @fkey9 tinyint
declare @fkey10 tinyint
declare @fkey11 tinyint
declare @fkey12 tinyint
declare @fkey13 tinyint
declare @fkey14 tinyint
declare @fkey15 tinyint
declare @fkey16 tinyint
declare @dbname varchar(30)

set nocount on

declare @table_id int,
@msg varchar(255)

select @cnstid = object_id (@cnstname)

--  constraint doesn't exist so return. Note: need to find correct
error#
if @cnstid is NULL
begin
  select @dbname = db_name()
  raiserror(15009,-1,-1,@cnstname,@dbname)
  -- return (1)
end

select  @cnststatus = c.status
from  sysconstraints c,
  sysobjects o
where  c.constid = @cnstid

create table #spcnstkeys
(
cnst_colid int NOT NULL
)

DECLARE tloop CURSOR
FOR SELECT
constid,
fkeyid,
rkeyid,

fkey1,
fkey2,
fkey3,
fkey4,
fkey5,
fkey6,
fkey7,
fkey8,
fkey9,
fkey10,
fkey11,
fkey12,
fkey13,
fkey14,
fkey15,
fkey16
FROM  sysreferences
where constid = object_id(@cnstname)

OPEN tloop

FETCH NEXT FROM tloop
INTO

@cnstid,
@fkeyid,
@rkeyid,
@fkey1,
@fkey2,
@fkey3,
@fkey4,
@fkey5,
@fkey6,

@fkey7,
@fkey8,
@fkey9,
@fkey10,
@fkey11,
@fkey12,
@fkey13,
@fkey14,
@fkey15,
@fkey16

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
   insert into #spcnstkeys values(@fkey1)
   insert into #spcnstkeys values(@fkey2)
   insert into #spcnstkeys values(@fkey3)
   insert into #spcnstkeys values(@fkey4)
   insert into #spcnstkeys values(@fkey5)
   insert into #spcnstkeys values(@fkey6)
   insert into #spcnstkeys values(@fkey7)
   insert into #spcnstkeys values(@fkey8)
   insert into #spcnstkeys values(@fkey9)
   insert into #spcnstkeys values(@fkey10)
   insert into #spcnstkeys values(@fkey11)
   insert into #spcnstkeys values(@fkey12)
   insert into #spcnstkeys values(@fkey13)
   insert into #spcnstkeys values(@fkey14)
   insert into #spcnstkeys values(@fkey15)
   insert into #spcnstkeys values(@fkey16)

   delete from #spcnstkeys where cnst_colid = 0


   /* Need a unique index so we can use a cursor. */
   create unique index ind1 on #spcnstkeys(cnst_colid)

   exec('declare fkey_curs cursor for select cnst_colid
    from #spcnstkeys for read only')

   open fkey_curs
   fetch fkey_curs into @i
   select @numkeys=1
   while @@fetch_status >= 0
                        begin
    select @thiskey = col_name(@fkeyid, @i)

    /* If it's the first component of the key, don't
    ** stick a ', ' in front of it. */

     if @numkeys = 1
     begin
      select @cnstdes = @thiskey
     end
    else
     begin
                           select @cnstdes  = @cnstdes + ', ' + @thiskey

     end
    select @numkeys=@numkeys+1
    fetch fkey_curs into @i
   end
   truncate table #spcnstkeys
   drop index #spcnstkeys.ind1
   deallocate fkey_curs

SELECT @msg = 'ALTER TABLE '
  + object_name(@fkeyid) + char( 13 ) + char( 10 ) +
   ' ADD CONSTRAINT '
  + object_name( @cnstid)
  + ' FOREIGN KEY ( '
  + @cnstdes + ' )' + char( 13 ) + char( 10 ) +
  '  REFERENCES '
  + object_name(@rkeyid)

select @msg + char( 13 ) + char( 10 ) + 'go'

END -- OF IF (@@FETCH_STATUS <> -2) for tloop

FETCH NEXT FROM tloop
INTO
@cnstid,
@fkeyid,
@rkeyid,
@fkey1,
@fkey2,
@fkey3,
@fkey4,
@fkey5,
@fkey6,
@fkey7,
@fkey8,
@fkey9,
@fkey10,
@fkey11,
@fkey12,
@fkey13,
@fkey14,
@fkey15,
@fkey16

END  --+++ OF WHILE (@@FETCH_STATUS <> -1) for tloop
DEALLOCATE tloop
GO

create procedure sp__fkRebuild
@tablename varchar(30)
as

declare @constraint_id int,
@constraint_name varchar(30)

DECLARE fk_loop CURSOR
FOR SELECT
constid
FROM  sysreferences
where rkeyid = object_id(@tablename)
OPEN fk_loop

FETCH NEXT FROM fk_loop
INTO  @constraint_id
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
  select @constraint_name = + object_name( @constraint_id)
  exec sp__fktext @constraint_name

END -- OF IF (@@FETCH_STATUS <> -2) for fk_loop

FETCH NEXT FROM fk_loop
INTO   @constraint_id

END  --+++ OF WHILE (@@FETCH_STATUS <> -1) for fk_loop
DEALLOCATE fk_loop
GO
create procedure sp__fkdrop
@tablename varchar(30) = null
,@now_yn tinyint = 0
as
-- created 01-23-96 by Clayton Groom. Copyright 1996, all rights
reserved
if @tablename is null
begin
print 'Format:  sp__fkdrop <tablename> [,<execute now> (= 0 or 1)]'
print 'Where <tablename> is the name of the referenced table you want
to drop'
print 'contstraints from.'
end
else
begin
declare @test int
,@msg varchar(255)
select @test = object_id(@tablename)
if @test is null
begin
  print 'No such object in database.'
end
else
begin
select @msg = 'alter table '
   + object_name(r.fkeyid)
   + ' drop constraint '
   + object_name( r.constid)
   + char( 13 ) + char( 10 ) + 'go'
  from sysreferences r
  where object_name( r.rkeyid) = @tablename
if @now_yn = 1
begin
  exec( @msg)
end
else
  select @msg
end
end
GO

create procedure sp__fkDropAll
@now_yn tinyint = 0
as
-- created 01-23-96 by Clayton Groom. Copyright 1996, all rights
reserved
declare @table_id int,
@constraint_id int,
@msg varchar(255)

DECLARE tloop CURSOR
FOR SELECT distinct
fkeyid, constid
FROM  sysreferences

OPEN tloop

FETCH NEXT FROM tloop
INTO  @table_id, @constraint_id
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
select @msg = 'alter table '

   + object_name(@table_id)
   + ' drop constraint '
   + object_name( @constraint_id)
select @msg + char( 13 ) + char( 10 ) + 'go'
if @now_yn = 1
begin
exec( @msg)
end

END -- OF IF (@@FETCH_STATUS <> -2) for tloop

FETCH NEXT FROM tloop
INTO  @table_id, @constraint_id

END  --+++ OF WHILE (@@FETCH_STATUS <> -1) for tloop
DEALLOCATE tloop
GO

create procedure sp__fkRebuildAll
as
-- created 01-23-96 by Clayton Groom. Copyright 1996, all rights
reserved

declare @constraint_id int,
@constraint_name varchar(30)

DECLARE fk_loop CURSOR
FOR SELECT
constid
FROM  sysreferences
OPEN fk_loop

FETCH NEXT FROM fk_loop
INTO  @constraint_id
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
  select @constraint_name = + object_name( @constraint_id)
  exec sp__fktext @constraint_name

END -- OF IF (@@FETCH_STATUS <> -2) for fk_loop

FETCH NEXT FROM fk_loop
INTO   @constraint_id

END  --+++ OF WHILE (@@FETCH_STATUS <> -1) for fk_loop

DEALLOCATE fk_loop
GO