34,838
社区成员




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
/*
有意思!
*/
if (select objectproperty(object_id(name),'IsPrimaryKey') from sysindexes where object_id('表')=id)=1
begin
..
end
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
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