27,579
社区成员
发帖
与我相关
我的任务
分享用sql语句添加列
我要用sql语句添加一个列，并要求这列设为主键。值为默认的或程序添加都可以。之后，还要将其删除。如果原来的表没有主键和有主键的话，应该怎么做。
...全文
请发表友善的回复…
发表回复
zjcxc 2004-07-26
- 打赏
- 举报
--删除刚添加的字段
--首先删除该字段上的主键/默认值约束及其他可能有的关系
declare @tbname sysname,@fdname sysname
select @tbname='tb' --要处理的表名
,@fdname='列名' --要处理的字段名
declare #tb cursor local for
--默认值约束
select sql='alter table ['+b.name+'] drop constraint ['+d.name+']'
from syscolumns a
join sysobjects b on a.id=b.id
join syscomments c on a.cdefault=c.id
join sysobjects d on c.id=d.id
where b.name=@tbname
and a.name=@fdname
union all --外键引用
select s='alter table ['+c.name+'] drop constraint ['+b.name+']'
from sysforeignkeys a
join sysobjects b on b.id=a.constid
join sysobjects c on c.id=a.fkeyid
join syscolumns d on d.id=c.id and a.fkey=d.colid
join sysobjects e on e.id=a.rkeyid
join syscolumns f on f.id=e.id and a.rkey=f.colid
where e.name=@tbname
and d.name=@fdname
union all --索引
select case e.xtype when 'PK' then 'alter table ['+c.name+'] drop constraint ['+e.name+']'
else 'drop index ['+c.name+'].['+a.name+']' end
from sysindexes a
join sysindexkeys b on a.id=b.id and a.indid=b.indid
join sysobjects c on b.id=c.id and c.xtype='U' and c.name<>'dtproperties'
join syscolumns d on b.id=d.id and b.colid=d.colid
join sysobjects e on c.id=e.parent_obj
where a.indid not in(0,255)
and c.name=@tbname
and d.name=@fdname
declare @s varchar(8000)
open #tb
fetch next from #tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from #tb into @s
end
close #tb
deallocate #tb
--然后再删除字段
alter table tb drop column 列名
--首先删除该字段上的主键/默认值约束及其他可能有的关系
declare @tbname sysname,@fdname sysname
select @tbname='tb' --要处理的表名
,@fdname='列名' --要处理的字段名
declare #tb cursor local for
--默认值约束
select sql='alter table ['+b.name+'] drop constraint ['+d.name+']'
from syscolumns a
join sysobjects b on a.id=b.id
join syscomments c on a.cdefault=c.id
join sysobjects d on c.id=d.id
where b.name=@tbname
and a.name=@fdname
union all --外键引用
select s='alter table ['+c.name+'] drop constraint ['+b.name+']'
from sysforeignkeys a
join sysobjects b on b.id=a.constid
join sysobjects c on c.id=a.fkeyid
join syscolumns d on d.id=c.id and a.fkey=d.colid
join sysobjects e on e.id=a.rkeyid
join syscolumns f on f.id=e.id and a.rkey=f.colid
where e.name=@tbname
and d.name=@fdname
union all --索引
select case e.xtype when 'PK' then 'alter table ['+c.name+'] drop constraint ['+e.name+']'
else 'drop index ['+c.name+'].['+a.name+']' end
from sysindexes a
join sysindexkeys b on a.id=b.id and a.indid=b.indid
join sysobjects c on b.id=c.id and c.xtype='U' and c.name<>'dtproperties'
join syscolumns d on b.id=d.id and b.colid=d.colid
join sysobjects e on c.id=e.parent_obj
where a.indid not in(0,255)
and c.name=@tbname
and d.name=@fdname
declare @s varchar(8000)
open #tb
fetch next from #tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from #tb into @s
end
close #tb
deallocate #tb
--然后再删除字段
alter table tb drop column 列名
pisces007 2004-07-26
- 打赏
- 举报
删除列
alter table yourtable drop column id
alter table yourtable drop column id
zjcxc 2004-07-26
- 打赏
- 举报
--假设要处理的表名为: tb
--判断要添加列的表中是否有主键
if exists(select 1 from sysobjects where parent_obj=object_id('tb') and xtype='PK')
begin
print '表中已经有主键,列只能做为普通列添加'
--添加int类型的列,默认值为0
alter table tb add 列名 int default 0
end
else
begin
print '表中无主键,添加主键列'
--添加int类型的列,默认值为0
alter table tb add 列名 int primary key default 0
end
--判断要添加列的表中是否有主键
if exists(select 1 from sysobjects where parent_obj=object_id('tb') and xtype='PK')
begin
print '表中已经有主键,列只能做为普通列添加'
--添加int类型的列,默认值为0
alter table tb add 列名 int default 0
end
else
begin
print '表中无主键,添加主键列'
--添加int类型的列,默认值为0
alter table tb add 列名 int primary key default 0
end
pisces007 2004-07-26
- 打赏
- 举报
设置主键
alter table yourtable add constraint PK_ID primary key(ID)
alter table yourtable add constraint PK_ID primary key(ID)
yesterday2000 2004-07-26
- 打赏
- 举报
增加字段
alter table docdsp add dspcode char(200)
删除字段
ALTER TABLE table_NAME DROP COLUMN column_NAME
修改字段类型
ALTER TABLE table_name ALTER COLUMN column_name new_data_type
改名
sp_rename
更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。
语法
sp_rename [ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]
pisces007 2004-07-26
- 打赏
- 举报
添加列
alter table yourtable add id int
alter table yourtable add id int
