-- ====================================================
-- Author:       Pawel Potasinski
-- Create date:  2009-06-20
-- Procedure:    sp_dropforeignkeys
-- Description:  Drops foreign keys referencing a table
-- ====================================================
USE master;
GO
IF OBJECT_ID('[dbo].[sp_dropforeignkeys]','P') IS NOT NULL
  DROP PROCEDURE [dbo].[sp_dropforeignkeys];
GO
CREATE PROCEDURE [dbo].[sp_dropforeignkeys]
  @ObjectName nvarchar(4000)
AS
SET NOCOUNT ON;
DECLARE @Lines TABLE (
  LineID int IDENTITY(1,1) NOT NULL PRIMARY KEY, 
  Line nvarchar(4000)
);
DECLARE @ObjectID int;
SET @ObjectID = OBJECT_ID(@ObjectName);
INSERT INTO @Lines (Line)
SELECT N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + N'.' 
  + QUOTENAME(OBJECT_NAME(parent_object_id)) + NCHAR(13) + NCHAR(10)
  + N'DROP CONSTRAINT ' + QUOTENAME(name) + N';'  
FROM sys.foreign_keys
WHERE referenced_object_id = @ObjectID;
SELECT Line AS ' ' FROM @Lines ORDER BY LineID;
GO
EXEC sp_MS_marksystemobject 'dbo.sp_dropforeignkeys';
GO
-- Test
USE AdventureWorks;
GO
EXEC sp_dropforeignkeys 'Person.Contact';
GO