3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
判断主键是否存在,存在则删除,用SQL怎么写?
判断主键是否存在,存在则删除,用SQL怎么写?
我写的IF EXISTS (SELECT * FROM sys.all_objects WHERE type_desc= N'PK_NAME')
ALTER TABLE TABLE_NAME DROP CONSTRAINT PK_NAME
我写的IF EXISTS (SELECT * FROM sys.all_objects WHERE type_desc= N'PK_NAME')
ALTER TABLE TABLE_NAME DROP CONSTRAINT PK_NAME
...全文
请发表友善的回复…
发表回复
nedved1020 2007-12-18
谢谢"树上的鸟儿",原来主键是主索引,得在index里找找!!呵呵!
nedved1020 2007-12-13
3楼的朋友判断删除的是存储过程啊!!
cuidenghong123 2007-12-13
if exists(select * from sysobjects where name='csp_addBanner' and type='p')
begin
drop procedure dbo.csp_addBanner
end
begin
drop procedure dbo.csp_addBanner
end
nedved1020 2007-12-13
楼上的朋友:讲清楚点,能否给个例子?
-狙击手- 2007-12-13
exec sp_pkeys
andy84 2007-12-13
学习
kk19840210 2007-12-13
顶 10 楼的
bruda 2007-12-13
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
fwacky 2007-12-13
/*
有意思！
*/
chuifengde 2007-12-13
if (select objectproperty(object_id(name),'IsPrimaryKey') from sysindexes where object_id('表')=id)=1
begin
..
endkk19840210 2007-12-13
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
wgsasd311 2007-12-13
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
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
-狙击手- 2007-12-13
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