declare @s varchar(8000) declare @t varchar(100)set @s = '' set @t = 't1'select @s = @s + ' alter table '+@t+' alter column '+b.name+' '+c.name+' not null;' from sysobjects a, syscolumns b, systypes c where a.id = b.id and b.type = c.type and a.xtype = 'U' and a.id = object_id(@t) exec(@s)
1.创建一张表,表名为yourtable,其中id列为空 create table yourtable (id int) 2。脚本 declare @tname varchar(30),@cname varchar(20),@sql varchar(500)declare cur cursor for select s1.name,s3.name from syscolumns s1 join sysobjects s2 on s1.id=s2.id join systypes s3 on s1.xtype=s3.xtype where s2.id=(select object_id('yourtable')) open curfetch next from cur into @tname,@cnamewhile @@fetch_status=0 begin set @sql='Alter Table yourtable ALter column '+@tname+' ' + @cname+' not null' print (@sql) exec(@sql)fetch next from cur into @tname,@cname endclose cur deallocate cur3。查看 sp_help yourtable
declare @t varchar(100)set @s = ''
set @t = 't1'select
@s = @s + ' alter table '+@t+' alter column '+b.name+' '+c.name+' not null;'
from
sysobjects a,
syscolumns b,
systypes c
where
a.id = b.id
and
b.type = c.type
and
a.xtype = 'U'
and
a.id = object_id(@t)
exec(@s)
create table yourtable
(id int)
2。脚本
declare @tname varchar(30),@cname varchar(20),@sql varchar(500)declare cur cursor for
select s1.name,s3.name from syscolumns s1 join sysobjects s2
on s1.id=s2.id
join systypes s3
on s1.xtype=s3.xtype
where s2.id=(select object_id('yourtable'))
open curfetch next from cur into @tname,@cnamewhile @@fetch_status=0
begin
set @sql='Alter Table yourtable
ALter column '+@tname+' ' + @cname+' not null'
print (@sql)
exec(@sql)fetch next from cur into @tname,@cname
endclose cur
deallocate cur3。查看
sp_help yourtable
1。yourtable要变成你的表名
2。利用查找替换就可以了。
3。需要换2个地方
你的脚本是错误的
1.使用以后,字段类型全部编程 sysname ??? 长度变成 128
2.也没有注意 有索引,主键,和约束的情况,所以执行不了TO: libin_ftsafe(子陌红尘)
你的脚本也没有 注意 有索引,主键,和约束的情况
3.字段可能有各种形态,如有默认值约束,索引,主键 这3种形态,要求设置 NOT NULL 后不会丢失这些状态,并且现有的字段类型,长度不变.
" 设置合适的条件更新syscolumns表中的isnullable"这种方法是不行的啦,因为isnullable是一个计算列.
觉得还是用alter table比较爽.