create table beyond
(
id int,
name varchar(20),
age int default 10
)
我现在想 把 age 字段删除
前提:1 用SQL语句
2 不用知道现有默认值的名称
不要把
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'datedflt'
AND type = 'D')
DROP DEFAULT datedflt
GO
这样的例子给我看,因为我是在delphi操作的,所以一般来说不会知道 默认值的名称
6 个解决方案
#1
alter table beyond
drop column age
drop column age
#2
declare @dfname varchar(50)
select @dfname=a.name
from sysobjects a join syscomments b on a.id=b.id
join sysconstraints c on c.constid=a.id
join syscolumns d on c.colid=d.colid and c.id=d.id
where a.xtype='D' and object_name(d.id)='beyond'
and d.name='age'
if @dfname is not null
exec('alter table beyond drop constraint '+@dfname)
delphi中加入上述SQL语句,然后调用相应方法执行(ADOQuery1.ExecSQL)
select @dfname=a.name
from sysobjects a join syscomments b on a.id=b.id
join sysconstraints c on c.constid=a.id
join syscolumns d on c.colid=d.colid and c.id=d.id
where a.xtype='D' and object_name(d.id)='beyond'
and d.name='age'
if @dfname is not null
exec('alter table beyond drop constraint '+@dfname)
delphi中加入上述SQL语句,然后调用相应方法执行(ADOQuery1.ExecSQL)
#3
上面是删除默认值,删除后即可删除字段:
alter table beyond drop column age
alter table beyond drop column age
#4
测试
CREATE TABLE [TABLE1] (
[aa] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[cc] [datetime] DEFAULT (getdate())--默认值
) ON [PRIMARY]
go
--
declare @df varchar(8000)
declare @dc varchar(8000)
select @df=name from sysobjects where object_name(parent_obj)='TABLE1'
select @dc=name from syscolumns where object_name(cdefault)=@df
exec('ALTER TABLE TABLE1 DROP constraint '+@df)
exec('ALTER TABLE TABLE1 DROP column '+@dc)
select * from TABLE1
drop Table TABLE1
/*
aa
----------
*/
CREATE TABLE [TABLE1] (
[aa] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[cc] [datetime] DEFAULT (getdate())--默认值
) ON [PRIMARY]
go
--
declare @df varchar(8000)
declare @dc varchar(8000)
select @df=name from sysobjects where object_name(parent_obj)='TABLE1'
select @dc=name from syscolumns where object_name(cdefault)=@df
exec('ALTER TABLE TABLE1 DROP constraint '+@df)
exec('ALTER TABLE TABLE1 DROP column '+@dc)
select * from TABLE1
drop Table TABLE1
/*
aa
----------
*/
#5
--你可以这样来查找默认值的名称.
select o.name
from sysobjects o join syscolumns c on o.id=c.cdefault
where o.xtype='D' and c.name='age'
select o.name
from sysobjects o join syscolumns c on o.id=c.cdefault
where o.xtype='D' and c.name='age'
#6
哈哈 果然是高手如云啊
开始 请客
开始 请客
#1
alter table beyond
drop column age
drop column age
#2
declare @dfname varchar(50)
select @dfname=a.name
from sysobjects a join syscomments b on a.id=b.id
join sysconstraints c on c.constid=a.id
join syscolumns d on c.colid=d.colid and c.id=d.id
where a.xtype='D' and object_name(d.id)='beyond'
and d.name='age'
if @dfname is not null
exec('alter table beyond drop constraint '+@dfname)
delphi中加入上述SQL语句,然后调用相应方法执行(ADOQuery1.ExecSQL)
select @dfname=a.name
from sysobjects a join syscomments b on a.id=b.id
join sysconstraints c on c.constid=a.id
join syscolumns d on c.colid=d.colid and c.id=d.id
where a.xtype='D' and object_name(d.id)='beyond'
and d.name='age'
if @dfname is not null
exec('alter table beyond drop constraint '+@dfname)
delphi中加入上述SQL语句,然后调用相应方法执行(ADOQuery1.ExecSQL)
#3
上面是删除默认值,删除后即可删除字段:
alter table beyond drop column age
alter table beyond drop column age
#4
测试
CREATE TABLE [TABLE1] (
[aa] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[cc] [datetime] DEFAULT (getdate())--默认值
) ON [PRIMARY]
go
--
declare @df varchar(8000)
declare @dc varchar(8000)
select @df=name from sysobjects where object_name(parent_obj)='TABLE1'
select @dc=name from syscolumns where object_name(cdefault)=@df
exec('ALTER TABLE TABLE1 DROP constraint '+@df)
exec('ALTER TABLE TABLE1 DROP column '+@dc)
select * from TABLE1
drop Table TABLE1
/*
aa
----------
*/
CREATE TABLE [TABLE1] (
[aa] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[cc] [datetime] DEFAULT (getdate())--默认值
) ON [PRIMARY]
go
--
declare @df varchar(8000)
declare @dc varchar(8000)
select @df=name from sysobjects where object_name(parent_obj)='TABLE1'
select @dc=name from syscolumns where object_name(cdefault)=@df
exec('ALTER TABLE TABLE1 DROP constraint '+@df)
exec('ALTER TABLE TABLE1 DROP column '+@dc)
select * from TABLE1
drop Table TABLE1
/*
aa
----------
*/
#5
--你可以这样来查找默认值的名称.
select o.name
from sysobjects o join syscolumns c on o.id=c.cdefault
where o.xtype='D' and c.name='age'
select o.name
from sysobjects o join syscolumns c on o.id=c.cdefault
where o.xtype='D' and c.name='age'
#6
哈哈 果然是高手如云啊
开始 请客
开始 请客