A表: a varchar(10) not null b varchar(3) not null c varchar(8) not null .....修改结果: a varchar(30) null b varchar(30) null c varchar(80) null .....因为字段太多,想统一修改的SQL语句!
declare cur cursor for select 'alter table ['+object_name(o.id)+'] alter column ['+c.name+'] varchar(20) null' from sysobjects o join syscolumns c on o.id=c.id join systypes t on c.xusertype=t.xusertype where o.xtype='U' and o.name='titles' and t.name='varchar' and c.isnullable=0 open curdeclare @sql varchar(8000) fetch next from cur into @sql while @@fetch_status=0 begin exec (@sql) fetch next from cur into @sql end close cur deallocate cur
update syscolumns set length=30 from sysobjects o join syscolumns c on o.id=c.id join systypes t on c.xusertype=t.xusertype where o.xtype='U' and o.name='A'服务器: 消息 259,级别 16,状态 2,行 1 未启用对系统目录的特殊更新。系统管理员必须重新配置 SQL Server 以允许这种操作。 提示错误怎么修改?
结帖了,谢谢小梁!启用对系统目录的特殊更新。系统管理员必须重新配置 SQL Server 以允许这种操作。解决方法: 打开mssql server 属性---》服务器选项----》启用对系统目录的特殊更新 即可
a varchar(10) not null
b varchar(3) not null
c varchar(8) not null
.....修改结果:
a varchar(30) null
b varchar(30) null
c varchar(80) null
.....因为字段太多,想统一修改的SQL语句!
select 'alter table ['+object_name(o.id)+'] alter column ['+c.name+'] varchar(20) null'
from sysobjects o
join syscolumns c
on o.id=c.id
join systypes t
on c.xusertype=t.xusertype
where o.xtype='U' and o.name='titles'
and t.name='varchar' and c.isnullable=0
open curdeclare @sql varchar(8000)
fetch next from cur into @sql
while @@fetch_status=0
begin
exec (@sql)
fetch next from cur into @sql
end
close cur
deallocate cur
未启用对系统目录的特殊更新。系统管理员必须重新配置 SQL Server 以允许这种操作。
提示错误怎么修改?