批量修改数据库表字段类型

最新推荐文章于 2024-02-02 16:59:59 发布

vipyjb

最新推荐文章于 2024-02-02 16:59:59 发布

阅读量3.7k

点赞数

文章标签：数据库 sql join table object go

批量修改数据库表字段类型

2007-03-11 12:06

2楼 zjcxc ( 邹建) 三级钻石用户该版得分小于等于800000分，大于500000分

回复于 2004-08-11 21:13:16 得分 100

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

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

作者tag: 数据库设计批量更改某数据库中所有表中某字段的类型