[SQL]stored procedure 批次修改欄位型態(含主鍵變更)

 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

沒有留言:

張貼留言

[DataSet Bug]無法啟用條件約束。一或多個資料列的值違反非 Null、唯一或外部索引鍵條件約束

在使用TableAdapter作Query時發生 1. 原因為有些資料欄位Query出來後為null值 但是該欄位在dbDataSet的屬性AllowDBNull為False 因此把該欄位(或乾脆把全部欄位屬性AllowDBNull改為True 2. maxLength 檢...