导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

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

nedved1020 2007-12-13 11:04:14
判断主键是否存在,存在则删除,用SQL怎么写?
我写的IF EXISTS (SELECT * FROM sys.all_objects WHERE type_desc= N'PK_NAME')
ALTER TABLE TABLE_NAME DROP CONSTRAINT PK_NAME
...全文
810 点赞 收藏 13
写回复
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
回复
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
..
end
回复
kk19840210 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
回复
-狙击手- 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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告