create proc AlterTableColumn_proc @tablename varchar(50) as begin set nocount on --declare @tablename varchar(50) declare @colname varchar(50) declare @strsql varchar(1000) declare C_col cursor for select name from syscolumns where id in (select id from sysobjects where name=@tablename) and colid>=5open C_colfetch next from C_col into @colnamewhile @@FETCH_STATUS = 0 begin select @strsql='alter table '+@tablename+' drop column '+@colname exec(@strsql) fetch next from C_col into @colname end close C_col deallocate C_col set nocount off end exec AlterTableColumn_proc 'a' 不过我不知道在系统表中,怎么判断列类型,要不然就可以用这个过程全搞定了
alter table tablename drop column columnname 要加个column才好
declare @sql varchar(300) declare @i int set @i = 1 while @i <= 5 begin set @sql = 'alter table A drop column ' select top 1 @sql = @sql + A.name from syscolumns as A left join sysobjects as B on A.id = B.id where B.name = 'A' order by A.colorder desc exec (@sql) set @i = @i + 1 end
应该可以做到的.
用asp怎么执行呢
能写成sql="alter table tablename add columname type"
conn.execute(sql)
吗?
@tablename varchar(50)
as
begin
set nocount on
--declare @tablename varchar(50)
declare @colname varchar(50)
declare @strsql varchar(1000)
declare C_col cursor
for select name from syscolumns where id in (select id from sysobjects where name=@tablename) and colid>=5open C_colfetch next from C_col
into @colnamewhile @@FETCH_STATUS = 0
begin
select @strsql='alter table '+@tablename+' drop column '+@colname
exec(@strsql)
fetch next from C_col into @colname
end
close C_col
deallocate C_col
set nocount off
end
exec AlterTableColumn_proc 'a'
不过我不知道在系统表中,怎么判断列类型,要不然就可以用这个过程全搞定了
要加个column才好
declare @i int
set @i = 1
while @i <= 5
begin
set @sql = 'alter table A drop column '
select top 1 @sql = @sql + A.name
from syscolumns as A
left join sysobjects as B
on A.id = B.id
where B.name = 'A'
order by A.colorder desc
exec (@sql)
set @i = @i + 1
end