Dropping Primary Keys 1

[lachie]

I've got a table and I need to delete it's primary key unfortunately I don't know what the name of original constraint is. (Well, of course I can have a look and find out, but I'm doing this through code).

I'm looking for a statement similar to

(1)
ALTER TABLE blah
DROP PRIMARY KEY

but, this doesn't work

i can use

(2)
ALTER TABLE blah
DROP CONSTRAINT blahsprimarykeyconstraintname

and this works fine - getting the name of the constraint is the hard part (for me at least!)

I would prefer to use a generic solution like (1), but if you can lend a hand with (2) that would be great.


Thanks,

L

 

[tlbroadbent]

This script will create a SQL statement to DROP a PRIMARY KEY, if it exists. You can modify it as needed for your needs. For example, you may want to create a stored procedure.

Declare @sql nvarchar(1024), @tblname nvarchar(40)

Set @tblname='tblname'

Select @sql='ALTER Table ' + @tblname + ' DROP CONSTRAINT ' + a.name
From sysobjects a Inner Join sysobjects b
On a.Parent_obj=b.ID
where a.xtype='PK'
And b.name=@tblname

Exec(@sql) Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes