批量更改某数据库中所有表中某字段的类型

-- =======================================================

-- 批量更改某数据库中所有表中某字段的类型

-- =======================================================

IF EXISTS (

  SELECT *

    FROM INFORMATION_SCHEMA.ROUTINES

   WHERE SPECIFIC_NAME = N'sp_AlterColumnType'

)

   DROP PROCEDURE sp_AlterColumnType

GO

 

CREATE PROCEDURE sp_AlterColumnType

AS

-- =============================================

-- 循环当前数据库中所有用户建的表

-- =============================================

DECLARE @TableName   nvarchar(100)

DECLARE @TableID  nvarchar(100)

 

DECLARE cursor_CustomTable CURSOR FOR

SELECT [name],[ID] FROM sysobjects where type ='U'

 

OPEN cursor_CustomTable

 

FETCH NEXT FROM cursor_CustomTable INTO @TableName,@TableID

 

WHILE @@FETCH_STATUS = 0

BEGIN

    -- =============================================

    -- 循环当前表中所有列,取出列名和列的类型

    -- =============================================

    DECLARE @ColumnName nvarchar(100)

    DECLARE @ColumnType nvarchar(100)

 

    DECLARE cursor_Column CURSOR FOR

    select [name],xtype from syscolumns where [id] = @TableID and xtype=(select xtype from systypes where name='numeric')

 

    OPEN cursor_Column

    FETCH NEXT FROM cursor_Column INTO @ColumnName,@ColumnType

 

    WHILE @@FETCH_STATUS = 0

    BEGIN

       --在此处修改列类型为numeric(14,2)

       exec('ALTER TABLE '+@TableName +' ALTER COLUMN '+@ColumnName +' numeric(14,2) null')

   

       FETCH NEXT FROM cursor_Column INTO @ColumnName,@ColumnType

 

    END

 

    CLOSE cursor_Column

    DEALLOCATE cursor_Column

 

-- 循环到下一个表.

FETCH NEXT FROM cursor_CustomTable INTO @TableName,@TableID

END

CLOSE cursor_CustomTable

DEALLOCATE cursor_CustomTable

GO