--2. 删除字段
If Exists (Select * from sysColumns Where name='u_id' And ID=OBJECT_ID(N'uinfor'))
alter table uinfor
drop column u_id; --3. 添加字段
If Not Exists (Select * from sysColumns Where name='u_note' And ID=OBJECT_ID(N'uinfor'))
alter table uinfor
add u_note varchar(1) NULL;--4. 修改字段
If Exists (Select * from sysColumns Where name='u_key' And ID=OBJECT_ID(N'uinfor'))
ALTER TABLE uinfor
ALTER COLUMN u_key VARCHAR(18) NOT NULL;
If Exists (Select * from sysColumns Where name='u_id' And ID=OBJECT_ID(N'uinfor'))
alter table uinfor
drop column u_id; --3. 添加字段
If Not Exists (Select * from sysColumns Where name='u_note' And ID=OBJECT_ID(N'uinfor'))
alter table uinfor
add u_note varchar(1) NULL;--4. 修改字段
If Exists (Select * from sysColumns Where name='u_key' And ID=OBJECT_ID(N'uinfor'))
ALTER TABLE uinfor
ALTER COLUMN u_key VARCHAR(18) NOT NULL;
beginend
else
begin
end
if exists(select * from sysobjects where xtype='PK' and parent_obj=object_id(N'uinfor'))
begin
--使用动态语句可以保证无论主键名是怎么定义都可以正常删除
declare @s nvarchar(4000)
select @s=N'alter table uinfor drop constraint '+quotename(name)
from sysobjects where xtype='PK' and parent_obj=object_id(N'uinfor')
exec(@s)
end--2. 删除字段
if exists(select * from syscolumns where id=object_id(N'uinfor') and name='u_id')
alter table uinfor drop column u_id; --3. 添加字段
alter table uinfor add u_note varchar(1) NULL;--4. 修改字段
if exists(select * from syscolumns where id=object_id(N'uinfor') and name='u_key')
ALTER TABLE uinfor ALTER COLUMN u_key VARCHAR(18) NOT NULL;--5. 添加新主键
if not exists(select * from sysobjects where xtype='PK' and parent_obj=object_id(N'uinfor'))
alter table uinfor add primary key(u_key ,u_name );
--=======================================如果表uinfor中存在u_id字段,则删除该表中u_key字段重复的记录--1.判断是否存在u_id字段
If Exists (Select * from sysColumns Where name='u_id' And ID=OBJECT_ID(N'uinfor'))
--2.删除该表中u_key字段重复的记录
delete a from uinfor a
where exists(select * from uinfor where (u_key = a.u_key and
u_id > a.u_id) ) ;
--明明sysColumns中已不存在u_id字段,但还是执行第二步,并且提示u_id不是有效字段,怎么回事?--=======================================
If Exists (Select * from sysColumns Where name='u_id' And ID=OBJECT_ID(N'uinfor'))
GO --这个东东很关键,如果没有就会报错--2.删除该表中u_key字段重复的记录
delete a from uinfor a
where exists(select * from uinfor where (u_key = a.u_key and
u_id > a.u_id) ) ;
If Exists (Select * from sysColumns Where name='u_id' And ID=OBJECT_ID(N'uinfor'))--2.删除该表中u_key字段重复的记录
exec('
delete a from uinfor a
where exists(select * from uinfor where (u_key = a.u_key and
u_id > a.u_id) ) ;
')