45fan.com - 路饭网

搜索: 您的位置主页 > 网络频道 > 阅读资讯:如何批量更改某数据库中所有表中字段的类型?

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

2016-09-02 15:22:41 来源:www.45fan.com 【

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

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

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

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

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

 

本文地址:http://www.45fan.com/a/question/71352.html
Tags: 数据库 批量 更改
编辑:路饭网
关于我们 | 联系我们 | 友情链接 | 网站地图 | Sitemap | App | 返回顶部