SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.sp_FIND_FOREIGN_KEYS    Script Date: 10/17/2001 3:17:55 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_FIND_FOREIGN_KEYS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_FIND_FOREIGN_KEYS]
GO


CREATE        PROCEDURE sp_FIND_FOREIGN_KEYS
@table_name nvarchar(128), /***Table to check for dependent constraints***/
@generate_drop_scripts bit = 0 /***SET to 1 to PRINT scripts***/
AS

/****CREATED BY JASON BOHANON****/
/****QUESTIONS, PLEASE EMAIL bohanon@qwest.net****/

SET NOCOUNT ON
DECLARE @alter_table1 nvarchar(4000),
	@key_name nvarchar(128), 
	@child_table nvarchar(128)

SELECT 
	@table_name as 'Referenced Table', 
	f.name as 'Referenced Column',
	c.name as 'Tables That Reference', 
	e.name as 'Referencing Column', 
	d.name as 'Foreign_Key_Name'
FROM sysobjects a 
	JOIN sysforeignkeys b
	on a.id = b.rkeyid 
	JOIN sysobjects c
	on c.id = b.fkeyid
	JOIN sysobjects d
	on d.id = b.constid
	JOIN syscolumns f
	on f.id = a.id
	AND b.rkey = f.colid
	JOIN syscolumns e
	on c.id = e.id 
	AND b.fkey = e.colid
WHERE a.name = @table_name 
ORDER BY c.name

IF @generate_drop_scripts = 1
BEGIN /***CREATE CONSTRAINT DROP SCRIPTS***/

CREATE TABLE #tmp_drop_keys
(key_name nvarchar(128), child_table nvarchar(128), used bit)

INSERT INTO #tmp_drop_keys
(key_name, child_table, used)
SELECT d.name ,c.name ,  0
FROM sysobjects a 
	JOIN sysforeignkeys b
	on a.id = b.rkeyid 
	JOIN sysobjects c
	on c.id = b.fkeyid
	JOIN sysobjects d
	on d.id = b.constid
WHERE a.name = @table_name 

WHILE EXISTS (SELECT * FROM #tmp_drop_keys WHERE used = 0)
BEGIN

SELECT TOP 1 @key_name = key_name, @child_table = child_table
FROM #tmp_drop_keys
WHERE used = 0

SELECT @alter_table1 = ' ALTER TABLE ' + @child_table + 
			' DROP CONSTRAINT ' + @key_name
PRINT ''
PRINT '------ALTERING TABLE ' + UPPER(@child_table) + '------'
PRINT @alter_table1
PRINT ''

UPDATE #tmp_drop_keys
SET used = 1
WHERE key_name = @key_name

END


END /***END CREATE CONSTRAINT DROP SCRIPTS***/





GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO