如何批量更改某数据库中所有表中字段的类型?
-- =======================================================
-- 批量更改某数据库中所有表中某字段的类型
-- =======================================================
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