22
将数据库某种类型的字段更新为另一种类型
source link: https://www.tuicool.com/articles/3y67nef
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
有时,我们可能会遇到这样的情况,当我们数据表的float类型精度不够时,可能需要把它统一调整成decimal或者money,而这时你一个一个去修改可能会崩溃,因为你无法从几千张表里确实找到所有的float类型的字段,而这时我们就需要自动的,批量的去处理它们。
实现思路:从系统表中查询所有用户建立的表,然后查询指定类型的所有字段,最后使用alter table alter column去更新这个字段.
知识点
- 游标
- exec
- SYSOBJECTS表和SYSCOLUMNS表
常用类型的typeid值
xtype= 35 'text' xtype=36 'uniqueidentifier' xtype=48 'tinyint' xtype=52 'smallint' xtype=56 'int' xtype=58 'smalldatetime' xtype=59 'real' xtype=60 'money' xtype=61 'datetime' xtype=62 'float' xtype=98 'sql_variant' xtype=99 'ntext' xtype=104 'bit' xtype=106 'decimal' xtype=108 'numeric' xtype=122 'smallmoney' xtype=127 'bigint' xtype=165 'varbinary' xtype=167 'varchar' xtype=173 'binary' xtype=175 'char' xtype=189 'timestamp' xtype=231 'nvarchar' xtype=239 'nchar' xtype=241 'xml' xtype=231 'sysname'
实现代码
DECLARE @tableName varchar(256) DECLARE @columnName varchar(256) DECLARE cursor2 CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE TYPE='U' OPEN cursor2 FETCH NEXT FROM cursor2 INTO @tableName WHILE @@fetch_status = 0 BEGIN DECLARE cursor3 CURSOR FOR SELECT name FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@tableName) and xtype=60 OPEN cursor3 FETCH NEXT FROM cursor3 INTO @columnName WHILE @@fetch_status = 0 BEGIN print '更新表'+@tableName+',更新字段'+@columnName Exec('ALTER TABLE '+@tableName+' ALTER COLUMN '+@columnName+' [float] ') FETCH NEXT FROM cursor3 INTO @columnName END CLOSE cursor3 DEALLOCATE cursor3 FETCH NEXT FROM cursor2 INTO @tableName END CLOSE cursor2 DEALLOCATE cursor2
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK