批量修改数据库表字段类型代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】批量修改数据库表字段类型
作者:Dezai.CN / 发布于2011/11/22/ 1122
<div>
实际使用例子：
/*--将所需表的某特定数值类型批量转换为其他类型--*/

/*--调用示例:E1T04_1996 char(10)-转换为nvarchar(100) --*/



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE procedure [dbo].[p_set]
as
declare tb cursor for
SELECT sql='alter table ['+d.name+'] alter column ['+a.name+'] nvarchar'
+' (100)' FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name='E1T04_1996' where b.name ='char' AND a.length='10'

declare @sql varchar(1000)
open tb
fetch next from tb into @sql
while @@fetch_status = 0
begin
exec(@sql)
fetch next from tb INTO @sql
end
close tb
deallocate tb


--
--DECLARE @sql VARCHAR(1000)
--select @sql='alter table ['+d.name+'] alter column ['+a.name+'] nvarchar'
-- +'(100)' FROM syscolumns a
-- left join systypes b on a.xtype=b.xusertype
-- inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name='E1T04_1996' where b.name ='char' AND a.length='10'
--
-- PRINT @sql

--------------------------------------------------------------------------------------


<h1 class="title_txt"><img border="0" alt="转载" src="http://blog.csdn.net/images/turnship.gif" width="15" height="15" /> 批量修改数据库表字段类型</h1>
<div class="blogstory">
<table border="0">
    <tbody>
        <tr>
            <td>
            <div id="blog_text" class="cnt">
            <div class="ReplyLine"><span class="ReplyFloor">2楼</span> <span class="ReplyUserName">zjcxc</span> (<span class="ReplyUserNickName">邹建</span>) <img alt="三级钻石用户 该版得分小于等于800000分，大于500000分" src="http://topic.csdn.net/Include/RankImages/diam3.gif" /> 回复于 <span class="ReplyTime">2004-08-11 21:13:16</span> 得分 <span class="ReplyPoint">100</span></div>
            <div class="ReplyContext">
            if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_set]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
            drop procedure [dbo].[p_set] 
            GO 
            
            /*--将所有的表中,数值类型由char,varchar改为nchar,nvarchar 
            
            --*/ 
            
            /*--调用示例: 
            exec p_set 
            --*/ 
            --修改的存储过程 
            create procedure p_set 
            as 
            declare tb cursor for 
            SELECT sql='alter table ['+d.name 
            +'] alter column ['+a.name+'] n' 
            +b.name+'('+cast(a.length*2 as varchar)+')' 
            FROM syscolumns a 
            left join systypes b on a.xtype=b.xusertype 
            inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
            where 
            b.name in('char','varchar') 
            and 
            not exists(SELECT 1 FROM sysobjects where xtype='PK' and name in ( 
            SELECT name FROM sysindexes WHERE indid in( 
            SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid 
            ))) --主键不能修改 
            order by d.name,a.name 
            
            declare @sql varchar(1000) 
            open tb 
            fetch next from tb into @sql 
            while @@fetch_status = 0 
            begin 
            exec(@sql) 
            fetch next from tb into @sql 
            end 
            close tb 
            deallocate tb 
            go</div>
            <div class="ReplyContext">----------------------------------------------------------------------------------------------</div>
            <div class="ReplyContext">作者tag:<a href="http://tag.csdn.net/tag/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1/1.html" target="_blank"><font color="#ca0000">数据库设计</font></a> <a href="http://tag.csdn.net/tag/%E6%89%B9%E9%87%8F%E6%9B%B4%E6%94%B9%E6%9F%90%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%AD%E6%89%80%E6%9C%89%E8%A1%A8%E4%B8%AD%E6%9F%90%E5%AD%97%E6%AE%B5%E7%9A%84%E7%B1%BB%E5%9E%8B/1.html" target="_blank"><font color="#ca0000">批量更改某数据库中所有表中某字段的类型</font></a>
            <div class="post">
            <div class="postTitle"><a href="http://blog.csdn.net/xiaohutushen/archive/2006/08/16/1074671.aspx"><img border="0" alt="" src="http://blog.csdn.net/images/authorship.gif" width="15" height="13" /><span style="color: rgb(128,0,128)"> 批量更改某数据库中所有表中某字段的类型</span></a></div>
            <div class="postText">
            <p align="left"><span>-- =======================================================</span>
            <p align="left"><span>-- </span><span>批量更改某数据库中所有表中某字段的类型</span>
            <p align="left"><span>-- =======================================================</span>
            <p align="left"><span>IF</span><span> <span>EXISTS</span> <span>(</span></span>
            <p align="left"><span><span>SELECT</span> <span>*</span></span>
            <p align="left"><span><span>FROM</span> <span>INFORMATION_SCHEMA.ROUTINES</span></span>
            <p align="left"><span><span>WHERE</span> SPECIFIC_NAME <span>=</span> N<span>'sp_AlterColumnType'</span></span>
            <p align="left"><span>)</span>
            <p align="left"><span><span>DROP</span> <span>PROCEDURE</span> sp_AlterColumnType</span>
            <p align="left"><span>GO</span>
            <p align="left">
            <p align="left"><span>CREATE</span><span> <span>PROCEDURE</span> sp_AlterColumnType</span>
            <p align="left"><span>AS</span>
            <p align="left"><span>-- =============================================</span>
            <p align="left"><span>-- </span><span>循环当前数据库中所有用户建的表</span>
            <p align="left"><span>-- =============================================</span>
            <p align="left"><span>DECLARE</span><span> @TableName<span> </span><span>nvarchar</span><span>(</span>100<span>)</span></span>
            <p align="left"><span>DECLARE</span><span> @TableID<span> </span><span>nvarchar</span><span>(</span>100<span>)</span></span>
            <p align="left">
            <p align="left"><span>DECLARE</span><span> cursor_CustomTable <span>CURSOR</span> <span>FOR</span></span>
            <p align="left"><span>SELECT</span><span> [name]<span>,</span>[ID] <span>FROM</span> sysobjects <span>where</span> <span>type</span> <span>=</span><span>'U'</span></span>
            <p align="left">
            <p align="left"><span>OPEN</span><span> cursor_CustomTable</span>
            <p align="left">
            <p align="left"><span>FETCH</span><span> NEXT <span>FROM</span> cursor_CustomTable <span>INTO</span> @TableName<span>,</span>@TableID</span>
            <p align="left">
            <p align="left"><span>WHILE</span><span> <span>@@FETCH_STATUS</span> <span>=</span> 0</span>
            <p align="left"><span>BEGIN</span>
            <p align="left"><span><span>-- =============================================</span></span>
            <p align="left"><span><span>-- </span></span><span>循环当前表中所有列</span><span>,</span><span>取出列名和列的类型</span>
            <p align="left"><span><span>-- =============================================</span></span>
            <p align="left"><span><span>DECLARE</span> @ColumnName <span>nvarchar</span><span>(</span>100<span>)</span></span>
            <p align="left"><span><span>DECLARE</span> @ColumnType <span>nvarchar</span><span>(</span>100<span>)</span></span>
            <p align="left">
            <p align="left"><span><span>DECLARE</span> cursor_Column <span>CURSOR</span> <span>FOR</span></span>
            <p align="left"><span><span>select</span> [name]<span>,</span>xtype <span>from</span> syscolumns <span>where</span> [id] <span>=</span> @TableID <span>and</span> xtype<span>=(</span><span>select</span> xtype <span>from</span> systypes <span>where</span> <span>name</span><span>=</span><span>'numeric'</span><span>)</span></span>
            <p align="left">
            <p align="left"><span><span>OPEN</span> cursor_Column</span>
            <p align="left"><span><span>FETCH</span> NEXT <span>FROM</span> cursor_Column <span>INTO</span> @ColumnName<span>,</span>@ColumnType</span>
            <p align="left">
            <p align="left"><span><span>WHILE</span> <span>@@FETCH_STATUS</span> <span>=</span> 0</span>
            <p align="left"><span><span>BEGIN</span></span>
            <p align="left"><span><span>--</span></span><span>在此处修改列类型为</span><span>numeric(14,2)</span>
            <p align="left"><span><span>exec</span><span>(</span><span>'ALTER TABLE '</span><span>+</span>@TableName <span>+</span><span>' ALTER COLUMN '</span><span>+</span>@ColumnName <span>+</span><span>' numeric(14,2) null'</span><span>)</span></span>
            <p align="left">
            <p align="left"><span><span>FETCH</span> NEXT <span>FROM</span> cursor_Column <span>INTO</span> @ColumnName<span>,</span>@ColumnType</span>
            <p align="left">
            <p align="left"><span><span>END</span></span>
            <p align="left">
            <p align="left"><span><span>CLOSE</span> cursor_Column</span>
            <p align="left"><span><span>DEALLOCATE</span> cursor_Column</span>
            <p align="left">
            <p align="left"><span>-- </span><span>循环到下一个表</span><span>.</span>
            <p align="left"><span>FETCH</span><span> NEXT <span>FROM</span> cursor_CustomTable <span>INTO</span> @TableName<span>,</span>@TableID</span>
            <p align="left"><span>END</span>
            <p align="left"><span>CLOSE</span><span> cursor_CustomTable</span>
            <p align="left"><span>DEALLOCATE</span><span> cursor_CustomTable</span>
            <p align="left"><span>GO</span>
            </div>
            </div>
            </div>
            </div>
            </td>
        </tr>
    </tbody>
</table>
</div>
</div>
试试其它关键字
　修改数据库表字段类型　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)