Script to Drop Primary key in whole database

,

I have wriiten this Procedure which Drop all primary keys
in whole database Dynamically. Create This Procedure in any User database . Execute it . It Will drop all Primary Keys

Enjoy

Create Procedure DropPrimaryKeys
As
DECLARE 
@MPrimaryKeyname  varchar(40),
@MTableName       varchar(40),
@Id                  int,
@sql              varchar(1000)

Declare @Temp Table(
Id        int IDENTITY (1, 1) NOT NULL ,
PrimaryKeyname  varchar(40),
TableName       varchar(40))

Insert into   @Temp
           (PrimaryKeyName,TableName)
           select a.name,b.name from sysobjects a,sysobjects b
                  where a.xtype='PK' and b.xtype='U' 
                  and a.parent_obj=b.id

   select @Id=min(Id) from @Temp
While @Id is not Null
   Begin
      Select    @MPrimaryKeyname=PrimaryKeyName,
                @MTableName=TableName   From @Temp  Where @Id=Id

        set @sql='ALTER TABLE ' + @mtablename + ' DROP CONSTRAINT ' + @MPrimaryKeyname
     EXEC (@sql)

       SELECT    @id=min(id) FROM    @TEMP
                 WHERE id>@id
 End

Rate

Share

Share

Rate