判断主键是否存在,存在则删除,用SQL怎么写?

[nedved1020]

判断主键是否存在,存在则删除,用SQL怎么写?
我写的IF EXISTS (SELECT * FROM sys.all_objects WHERE type_desc= N'PK_NAME')
ALTER TABLE TABLE_NAME DROP CONSTRAINT PK_NAME

[-狙击手-]

create table tb(id int PRIMARY KEY)
go
exec sp_pkeys tb
/*
TABLE_QUALIFIER     
------------------
CSDN_TEST                                                                                                                        dbo                                                                                                                              tb                                                                                                                               id                                                                                                                               1       PK__tb__41FB19C2

（所影响的行数为 1 行）
*/
declare @tbname varchar(20),@pkname varchar(20)
select @tbname = table_name, @pkname = pk_name

FROM OPENROWSET('SQLOLEDB','.';'sa';'',
   'exec csdn_test..sp_pkeys tb') AS a

exec('ALTER TABLE '+@tbname+' DROP CONSTRAINT '+@pkname)
exec sp_pkeys tb
/*
/*
TABLE_QUALIFIER     
------------------
                                                                                                                  dbo                                                                                                                              tb                                                                                                                               id                                                                                                                               1       PK__tb__41FB19C2

（所影响的行数为 0 行）
*/
drop table tb

[wgsasd311]

create table test(id int primary key,col varchar(10))
go
declare @key varchar(100)
select @key=name from sysobjects where xtype='pk' and parent_obj=object_id('test')
select @key as 主键
if @key is not null
exec('alter table test drop '+ @key)
drop table test

[kk19840210]

if exists (select object_name(parent_obj) from sysobjects where xtype='pk' and name=主键名)
	begin
			declare @tab sysname
			select @tab=object_name(parent_obj) from sysobjects where xtype='pk' and name=主键名
			exec('alter table '+@tab+' drop CONSTRAINT '+主键名)		
	end

[chuifengde]

if (select  objectproperty(object_id(name),'IsPrimaryKey') from sysindexes where object_id('表')=id)=1
begin
   ..
end

[fwacky]

/*
有意思！
*/

[bruda]

if exists (select object_name(parent_obj) from sysobjects where xtype='pk' and name=主键名)
    begin
            declare @tab sysname
            select @tab=object_name(parent_obj) from sysobjects where xtype='pk' and name=主键名
            exec('alter table '+@tab+' drop CONSTRAINT '+主键名)        
    end

[kk19840210]

顶 10 楼的

[andy84]

学习

[-狙击手-]

exec sp_pkeys

[nedved1020]

楼上的朋友:讲清楚点,能否给个例子?

[cuidenghong123]

if exists(select * from sysobjects where name='csp_addBanner' and type='p')
begin
drop procedure dbo.csp_addBanner
end

[nedved1020]

3楼的朋友判断删除的是存储过程啊!!

[nedved1020]

谢谢"树上的鸟儿",原来主键是主索引,得在index里找找!!呵呵!