--try: declare @sql varchar(8000) select @sql=isnull(@sql+';','')+'alter table '+c.name+' alter column '+a.name+' bigint' from sysobjects c,syscolumns a,systypes b where a.id=c.id and a.xtype=b.xtype and b.name='int' and c.xtype='U' order by c.name print @sql exec(@sql)
DECLARE @SQL nvarchar(max);SET @SQL=''; --改为bigint SELECT @SQL=@SQL+'ALTER TABLE ['+s.name+'].['+t.name+']' +' ALTER COLUMN ['+c.name+'] BIGINT;' FROM sys.schemas AS s JOIN sys.tables AS t ON s.schema_id=t.schema_id AND t.name='tb_name' JOIN sys.columns AS c ON t.object_id=c.object_id JOIN sys.types AS tp ON c.user_type_id=tp.user_type_id AND tp.name='int';
PRINT @SQL --TimeID改为int SET @SQL='';SELECT @SQL=@SQL+'ALTER TABLE ['+s.name+'].['+t.name+']' +' ALTER COLUMN ['+c.name+'] INT;' FROM sys.schemas AS s JOIN sys.tables AS t ON s.schema_id=t.schema_id AND t.name='tb_name' JOIN sys.columns AS c ON t.object_id=c.object_id AND c.name='TimeID';
PRINT @SQL;
用游标可实现。 用动态sql,考虑长度,可将下面的语句多执行几遍即可。--获取所有int型字段 select top 50 tbname=a.name,colname=b.name,coltype=b.xtype into # from sysobjects a join syscolumns b on a.id=b.id where a.xtype='U' and b.xtype=56declare @sql varchar(8000) set @sql='' select @sql=@sql+' alter table '+quotename(tbname)+' alter column '+quotename(colname)+' bigint; ' from # exec(@sql)drop table #
select 'Alter table dbo.'+quotename(a.Name)+' alter Column '+b.Name+' bigint '+case when b.isnullable=1 then ' not null ' else '' end from Sysobjects a join Syscolumns b on a.ID=b.ID where USER_NAME (a.uid)='dbo' and a.xtype='U' and b.xusertype=56 生成腳本
select 'Alter table dbo.'+quotename(a.Name)+' alter Column '+b.Name+' bigint '+case when b.isnullable=1 then '' else ' not null' end--這里改改; from Sysobjects a join Syscolumns b on a.ID=b.ID where USER_NAME (a.uid)='dbo' and a.xtype='U' and b.xusertype=56
建议还是手动一个个改吧, aspnet_系列的表也算用户表, 一锅端的话麻烦就大了。
--直接操作系统表得了.. --较危险哈,不建议这么魯莽.update syscolumns set xtype=127, xusertype=127, length=8, xprec=19 from syscolumns a, sysobjects b where a.id=b.id and a.xtype=56 and b.uid=(select top 1 uid from sysusers where name='dbo' /* and b.id=object_id('t2') */ and b.xtype='u'
http://topic.csdn.net/t/20050414/21/3937472.html
--try:
declare @sql varchar(8000)
select @sql=isnull(@sql+';','')+'alter table '+c.name+' alter column '+a.name+' bigint' from sysobjects c,syscolumns a,systypes b
where a.id=c.id and a.xtype=b.xtype and b.name='int' and c.xtype='U'
order by c.name
print @sql
exec(@sql)
--改为bigint
SELECT @SQL=@SQL+'ALTER TABLE ['+s.name+'].['+t.name+']'
+' ALTER COLUMN ['+c.name+'] BIGINT;'
FROM sys.schemas AS s
JOIN sys.tables AS t
ON s.schema_id=t.schema_id
AND t.name='tb_name'
JOIN sys.columns AS c
ON t.object_id=c.object_id
JOIN sys.types AS tp
ON c.user_type_id=tp.user_type_id
AND tp.name='int';
PRINT @SQL
--TimeID改为int
SET @SQL='';SELECT @SQL=@SQL+'ALTER TABLE ['+s.name+'].['+t.name+']'
+' ALTER COLUMN ['+c.name+'] INT;'
FROM sys.schemas AS s
JOIN sys.tables AS t
ON s.schema_id=t.schema_id
AND t.name='tb_name'
JOIN sys.columns AS c
ON t.object_id=c.object_id
AND c.name='TimeID';
PRINT @SQL;
用动态sql,考虑长度,可将下面的语句多执行几遍即可。--获取所有int型字段
select top 50 tbname=a.name,colname=b.name,coltype=b.xtype into #
from sysobjects a join syscolumns b on a.id=b.id where a.xtype='U' and b.xtype=56declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' alter table '+quotename(tbname)+' alter column '+quotename(colname)+' bigint; ' from #
exec(@sql)drop table #
'Alter table dbo.'+quotename(a.Name)+' alter Column '+b.Name+' bigint '+case when b.isnullable=1 then ' not null ' else '' end
from
Sysobjects a
join
Syscolumns b on a.ID=b.ID
where
USER_NAME (a.uid)='dbo' and a.xtype='U' and b.xusertype=56
生成腳本
'Alter table dbo.'+quotename(a.Name)+' alter Column '+b.Name+' bigint '+case when b.isnullable=1 then '' else ' not null' end--這里改改;
from
Sysobjects a
join
Syscolumns b on a.ID=b.ID
where
USER_NAME (a.uid)='dbo' and a.xtype='U' and b.xusertype=56
aspnet_系列的表也算用户表,
一锅端的话麻烦就大了。
--较危险哈,不建议这么魯莽.update syscolumns
set xtype=127,
xusertype=127,
length=8,
xprec=19
from syscolumns a,
sysobjects b
where a.id=b.id
and a.xtype=56
and b.uid=(select top 1 uid from sysusers where name='dbo'
/* and b.id=object_id('t2') */
and b.xtype='u'
10楼小梁的方法--error,没有效果
11楼小雄的执行起来比较麻烦
中国风的方法可以得到所有语句怎么排除对timeid这个字段的更改呢?timeid原来的数据类型就是int,也是数据库里唯一不能从int改为bigint的字段
因为存在下面情况的话.将不能直接修改数据类型..用于索引的列。
用于 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 约束的列。
与默认值(由 DEFAULT 关键字定义)相关联的列,或绑定到默认对象的列。
绑定到规则的列。