/**********************************************************************

Procedure Name:  RemooveAllReference

Created By:      Mangesh Singh

Date:            24 April 2009

Purpose:         To Remoove All Constraints / Foreign Keys In Database

**********************************************************************/

ALTER Procedure RemooveAllReference

AS

DECLARE @ERR INT 

Begin Tran Reference

Create Table #temp (ID INT IDENTITY(1,1),TableName varchar(100),IDD INT)

Insert INTO #temp(TableName,IDD)

Select Name,ID From Sys.SysObjects Where ID IN( select Distinct Parent_Obj from sys.sysobjects where xtype='F' )

Declare @Count INT

Declare @A INT

Declare @TableName varchar(100)

Set @A=1

Select @Count=Count(*) From #temp

While(@A<=@Count)

Begin

      Select @TableName=TableName From #temp Where ID=@A

    Create Table #Temp1(ID INT IDENTITY(1,1),ColumnName Varchar(100))

      Insert Into #Temp1(ColumnName)

      select Name from Sys.SysObjects  Where XType='F' AND Parent_Obj=(Select IDD From #temp Where ID=@A)

      Declare @CNT INT

      Declare @AA INT

      Set @AA=1

      Select @CNT=Count(*) From #Temp1

      While(@AA<=@CNT)

            Begin

            Declare @CLNAME Varchar(200)

                  Declare @SQL Varchar(1000)

                  Select @CLNAME=ColumnName From #Temp1 Where ID=@AA

                  Set @SQL='ALTER TABLE dbo.'+@TableName+'

                  DROP CONSTRAINT '+@CLNAME+''

                  Exec(@SQL)

                  Set @Err=@@ERROR         

                  if (@ERR <> 0)         

                  Begin             

                  Raiserror('Insert Statement Fail',16,1)             

                  RollBack Transaction Reference               

                  End

                Set @AA=@AA+1

            End

   Set @A=@A+1

   Drop Table #temp1   

End

Drop Table #temp

Commit Tran