1. 建立table
CREATE TABLE [dbo].[ColumnChanges0423](
[TableName] [nvarchar](128) NULL, --//要修改的TABLE
[ColumnName] [nvarchar](128) NULL, -- //要修改的Column
[NewType] [nvarchar](50) NULL, --//要修改為型態
[IsPKey] [bit] NULL --//是否為PKey (1 = 是 , 0 = 否)
) ON [PRIMARY]
2. 寫入資料(範例)
INSERT INTO [dbo].[ColumnChanges0423]([TableName],[ColumnName],[NewType],[IsPKey]) VALUES ('baf008','emp_id','varchar (20)',0)
INSERT INTO [dbo].[ColumnChanges0423]([TableName],[ColumnName],[NewType],[IsPKey]) VALUES ('bof110','extend_no','varchar(14)',1)
3. 建立stored procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_UpdateColumnTypes]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CurrentTable NVARCHAR(128), @SQL NVARCHAR(MAX), @PKName NVARCHAR(128);
DECLARE @ColumnName NVARCHAR(128), @NewType NVARCHAR(100);
DECLARE @PKCols NVARCHAR(MAX);
CREATE TABLE #ColumnChanges (
TableName NVARCHAR(128),
ColumnName NVARCHAR(128),
NewType NVARCHAR(50),
IsPKey BIT
)
--CREATE TABLE [dbo].[ColumnChanges0423](
-- [TableName] [nvarchar](128) NULL,
-- [ColumnName] [nvarchar](128) NULL,
-- [NewType] [nvarchar](50) NULL,
-- [IsPKey] [bit] NULL
--) ON [PRIMARY]
insert into #ColumnChanges
select * from [dbo].[ColumnChanges0423]
DECLARE TableCursor CURSOR FOR
SELECT DISTINCT TableName FROM #ColumnChanges;
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @CurrentTable;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 取得主鍵名稱與欄位（含順序）
SET @PKName = NULL
SELECT TOP 1 @PKName = kc.name
FROM sys.key_constraints kc
JOIN sys.tables t ON kc.parent_object_id = t.object_id
WHERE kc.[type] = 'PK' AND t.name = @CurrentTable;
SELECT @PKCols = STRING_AGG('[' + c.name + ']', ',') WITHIN GROUP (ORDER BY ic.key_ordinal)
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.is_primary_key = 1 AND t.name = @CurrentTable;
-- 移除主鍵（若存在）
IF @PKName IS NOT NULL
BEGIN
SET @SQL = 'ALTER TABLE [' + @CurrentTable + '] DROP CONSTRAINT [' + @PKName + ']';
print @SQL
EXEC sp_executesql @SQL;
END
-- 修改欄位型態
DECLARE ColumnCursor CURSOR FOR
SELECT ColumnName, NewType FROM #ColumnChanges
WHERE TableName = @CurrentTable;
OPEN ColumnCursor;
FETCH NEXT FROM ColumnCursor INTO @ColumnName, @NewType;
WHILE @@FETCH_STATUS = 0
BEGIN
IF CHARINDEX(@ColumnName, @PKCols) > 0
BEGIN
SET @SQL = 'ALTER TABLE [' + @CurrentTable + '] ALTER COLUMN [' + @ColumnName + '] ' + @NewType + ' NOT NULL';
END
ELSE
BEGIN
SET @SQL = 'ALTER TABLE [' + @CurrentTable + '] ALTER COLUMN [' + @ColumnName + '] ' + @NewType;
END
print @SQL
EXEC sp_executesql @SQL;
FETCH NEXT FROM ColumnCursor INTO @ColumnName, @NewType;
END
CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;
-- 重建主鍵（若原本存在）
IF @PKName IS NOT NULL AND @PKCols IS NOT NULL AND @PKCols <> ''
BEGIN
SET @SQL = 'ALTER TABLE [' + @CurrentTable + '] ADD CONSTRAINT [' + @PKName + '] PRIMARY KEY (' + @PKCols + ')';
print @SQL
EXEC sp_executesql @SQL;
END
FETCH NEXT FROM TableCursor INTO @CurrentTable;
END
CLOSE TableCursor;
DEALLOCATE TableCursor;
END
4. 執行 exec usp_UpdateColumnTypes