Technical Article

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

Read 415 times
(1 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating