SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_drop_table]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_drop_table]
GO








CREATE PROCEDURE sp_drop_table
	@table_name nvarchar(128) /***Table to drop***/
AS
/****CREATED BY JASON BOHANON****/
/****QUESTIONS, PLEASE EMAIL j_bohanon@msn.com****/

/*** 	This procedure will accept a table name and drop 
	all constraints on the table before executing 
	a DROP TABLE command.    ***/
SET NOCOUNT ON
DECLARE 
	@alter_table1 nvarchar(4000),
	@key_name nvarchar(128), 
	@child_table nvarchar(128), 
	@drop_table1 nvarchar(4000)
	


BEGIN /***CREATE CONSTRAINT DROP SCRIPTS***/
CREATE TABLE #tmp_drop_keys
	(
	key_name nvarchar(128) COLLATE database_default, 
	child_table nvarchar(128) COLLATE database_default, 
	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

	EXEC (@alter_table1)


		UPDATE 
			#tmp_drop_keys
		SET 
			used = 1
		WHERE 
			key_name = @key_name
END
SELECT @drop_table1 = 'DROP TABLE ' + @table_name
EXEC (@drop_table1)

END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO