[转载]如何将数据库所有表里面的varchar字段批量改成N

数据库里面大概有2百多张表,每张表都有很多VARCHAR的字段,如何做,才能将数据库里面的所有表,字段类型为VARCHAR的,全部一次性改为NVARCHAR呢?

 

由于需要考虑到主键外键 约束的问题,所以这个问题变得复杂了许多,现在提供可行的一种方法:

 

-- xtype = '167' 字段類型為varchar 可用select * from systypes 查看

Declare  curAlterInfo Cursor For Select a.Name AlterFieldName, a.Length, a.IsNullable, b.Name AlterTableName

                                                    From SysColumns a, SysObjects b Where a.id = b.id and a.xType = '167'

                                                    and b.xType = 'u' and a.cdefault = 0

declare @AlterFieldName nvarchar(50),  -- 修改的字段

            @AlterTableName nvarchar(50),  -- 修改的表名

            @Length int, -- 字段以長度

            @IsNullable bit, -- 字段是否允許為空

            @PkName nvarchar(50), -- 主鍵名

            @PkFieldName nvarchar(500), --主鍵字段名

            @TmpFieldName nvarchar(50),

            @TmpTableName nvarchar(50),

            @Sql nvarchar(500)

 

Set NoCount On

Begin Tran

open curAlterInfo

Fetch curAlterInfo Into @AlterFieldName, @Length, @IsNullable, @AlterTableName

While @@Fetch_Status=0

Begin

    print @AlterTableName

    --檢查修改的表是否有主鍵

    If Exists(Select Name From SysObjects Where xType = 'PK'

                  and Parent_Obj = (Select id From SysObjects Where Name = @AlterTableName))

    Begin

       Set @TmpTableName = @AlterTableName

       -- 取得主鍵名

       Select @PkName = Name From SysObjects Where xType = 'PK'

                 and Parent_Obj = (Select id From SysObjects Where Name = @AlterTableName)

       Set @PkFieldName = ''

       -- 主鍵字段

       Declare curPkFieldName Cursor For Select b.Name From SysIndexKeys a, SysColumns b

                                                Where a.id = (Select id From SysIndexes Where Name = @PkName)

                                                and a.indid = 1 and a.colid = b.colid and a.id = b.id

       -- 取得所有的主鍵字段

       Open curPkFieldName

       Fetch curPkFieldName Into @TmpFieldName

       While @@fetch_status = 0

       Begin

           Set @PkFieldName = @PkFieldName + @TmpFieldName + ','

           Fetch curPkFieldName Into @TmpFieldName

       End

       Close curPkFieldName

       Deallocate curPkFieldName

       -- 刪除舊主鍵

       Set @Sql = 'ALTER TABLE '+ @AlterTableName + ' DROP CONSTRAINT ' + @PkName

       Print @Sql

       Exec(@Sql)   

    end

    -- 修改字段

    Set @Sql = 'ALTER TABLE ' + @AlterTableName + ' ALTER COLUMN ' + @AlterFieldName

                   + ' NVARCHAR( ' + CAST(@Length AS NVARCHAR) + ')'

    -- 是否允許為空

    if @IsNullable = 0

        Set @Sql = @Sql + ' NOT NULL'

    Print @sql

    Exec(@sql)

    Fetch curAlterInfo Into @AlterFieldName, @Length, @IsNullable, @AlterTableName

    -- 創建主鍵

    If (@AlterTableName <> @TmpTableName or @@fetch_status <> 0) and @PkFieldName <> ''

    Begin

         Set @PkFieldName = Left(@PkFieldName, Len(@PkFieldName) - 1)

         Set @Sql =  ' ALTER TABLE ' + @TmpTableName + ' ADD CONSTRAINT ' + @PkName

                        +  ' PRIMARY KEY CLUSTERED(' + @PkFieldName + ') ON [PRIMARY]'

         Print @Sql

         Exec(@Sql)

         print '-----------------------------'

         Set @PkFieldName = ''

    End

End

Close curAlterInfo

Deallocate curAlterInfo

If @@Error > 0

   Rollback Tran

Else

   Commit Tran

Set NoCount Off

posted @ 2011-10-25 16:35  刘竹青  阅读(161)  评论(0编辑  收藏  举报