update_sql.sql

一个非常好的jsp+wml的例子（包括代码） 源码,对初接触wap编程的爱好者 ...

use [wapmo]
go

if exists (select name from sysobjects where name='p_deletefield')
drop proc p_deletefield
go

create proc p_deletefield
@tbname sysname,	--要处理的表名
@fdname sysname,	--要处理的字段名
@delfield bit=1		--0只删除关系,1同时删除字段
as
declare hCForEach cursor global for
--默认值约束
select sql='alter table ['+b.name+'] drop constraint ['+d.name+']'
from syscolumns a
	join sysobjects b on a.id=b.id and a.name=@fdname and b.name=@tbname 
	join syscomments c on a.cdefault=c.id
	join sysobjects d on c.id=d.id
union	--外键引用
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 and d.name=@fdname
	join sysobjects e on e.id=a.rkeyid and e.name=@tbname
	join syscolumns f on f.id=e.id and a.rkey=f.colid 
union	--主键/唯一键/索引
select case when e.xtype in('PK','UQ') 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=@tbname
	join syscolumns d on b.id=d.id and b.colid=d.colid and d.name=@fdname
	left join sysobjects e on e.id=object_id(a.name)
where a.indid not in(0,255)

exec sp_msforeach_worker '?'

if @delfield=1
	exec('alter table ['+@tbname+'] drop column ['+@fdname+']')
go

if exists (select name from sysobjects where name='p_resetfield')
drop proc p_resetfield
go

create proc p_resetfield
@tbname sysname,
@fdname sysname
as
begin
exec ('alter table '+@tbname+' add '+@fdname+'x int default 0 not null')
exec ('update '+@tbname+' set '+@fdname+'x=DateDiff(second,''1970-01-01 08:00:00'','+@fdname+')')
exec p_deletefield @tbname,@fdname,1
exec ('sp_rename '''+@tbname+'.'+@fdname+'x'', '''+@fdname+''', ''COLUMN''')
end
go

--update wapmo_admin
exec sp_rename 'wapmo_admin.Timestamp', 'Timeval', 'COLUMN'
exec p_resetfield 'wapmo_admin','Intime'
exec p_resetfield 'wapmo_admin','Outime'
go

--update user_db
exec p_resetfield 'user_db','RegisterTime'
exec p_resetfield 'user_db','FirstUsedTime'
exec p_resetfield 'user_db','LastUsedTime'
exec p_resetfield 'user_db','FirstSearchTime'
exec p_resetfield 'user_db','LastSearchTime'

--update staple
exec sp_rename 'staple.Comment', 'Remark', 'COLUMN'
exec sp_rename 'staple.Lock', 'Locked', 'COLUMN'
exec p_resetfield 'staple','Intime'
exec p_resetfield 'staple','Outime'
go

--update content
alter table content add Category int default 0 not null
alter table content alter column attach varchar(100)
exec p_resetfield 'content','Intime'
exec p_resetfield 'content','Outime'
go

--update forum
exec p_resetfield 'forum','Intime'
go

--update article
exec p_resetfield 'article','Intime'
exec p_resetfield 'article','Outime'
go

--update trade
alter table trade add UserId int default 0 not null
exec p_resetfield 'trade','Intime'
go

--update remark
exec sp_rename 'comment', 'remark', 'OBJECT'
alter table remark add UserId int default 0 not null
exec p_resetfield 'remark','Intime'
go