[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

[SQL]比較欄位相同兩個表之間的差異-詳細列出不同之欄位

 ---- 比較兩個表之間的差異並列出不同欄位

--相同key值為 comp_id + auto_no

SELECT

    s1_log.comp_id,

    s1_log.auto_no,

    -- 每個欄位比對

    CASE WHEN ISNULL(s1_log.apply_date, '') <> ISNULL(test.apply_date, '') THEN CONCAT('apply_date: ', s1_log.apply_date, ' <> ', test.apply_date) ELSE 'Same' END AS apply_date_diff,

    CASE WHEN ISNULL(s1_log.cust_no, '') <> ISNULL(test.cust_no, '') THEN CONCAT('cust_no: ', s1_log.cust_no, ' <> ', test.cust_no) ELSE 'Same' END AS cust_no_diff,

    CASE WHEN ISNULL(s1_log.apply_kind, '') <> ISNULL(test.apply_kind, '') THEN CONCAT('apply_kind: ', s1_log.apply_kind, ' <> ', test.apply_kind) ELSE 'Same' END AS apply_kind_diff,

    CASE WHEN ISNULL(s1_log.apply_remark, '') <> ISNULL(test.apply_remark, '') THEN CONCAT('apply_remark: ', s1_log.apply_remark, ' <> ', test.apply_remark) ELSE 'Same' END AS apply_remark_diff,

    CASE WHEN ISNULL(s1_log.emp_id, '') <> ISNULL(test.emp_id, '') THEN CONCAT('emp_id: ', s1_log.emp_id, ' <> ', test.emp_id) ELSE 'Same' END AS emp_id_diff,

    CASE WHEN ISNULL(s1_log.comp_no, '') <> ISNULL(test.comp_no, '') THEN CONCAT('comp_no: ', s1_log.comp_no, ' <> ', test.comp_no) ELSE 'Same' END AS comp_no_diff,

    CASE WHEN ISNULL(s1_log.dept_no, '') <> ISNULL(test.dept_no, '') THEN CONCAT('dept_no: ', s1_log.dept_no, ' <> ', test.dept_no) ELSE 'Same' END AS dept_no_diff,

    CASE WHEN ISNULL(s1_log.bank_no, '') <> ISNULL(test.bank_no, '') THEN CONCAT('bank_no: ', s1_log.bank_no, ' <> ', test.bank_no) ELSE 'Same' END AS bank_no_diff,

    CASE WHEN ISNULL(s1_log.bank_acc, '') <> ISNULL(test.bank_acc, '') THEN CONCAT('bank_acc: ', s1_log.bank_acc, ' <> ', test.bank_acc) ELSE 'Same' END AS bank_acc_diff,

    CASE WHEN ISNULL(s1_log.note_no, '') <> ISNULL(test.note_no, '') THEN CONCAT('note_no: ', s1_log.note_no, ' <> ', test.note_no) ELSE 'Same' END AS note_no_diff,

    CASE WHEN ISNULL(s1_log.lift_date, '') <> ISNULL(test.lift_date, '') THEN CONCAT('lift_date: ', s1_log.lift_date, ' <> ', test.lift_date) ELSE 'Same' END AS lift_date_diff,

    CASE WHEN ISNULL(s1_log.inward_amt, 0) <> ISNULL(test.inward_amt, 0) THEN CONCAT('inward_amt: ', s1_log.inward_amt, ' <> ', test.inward_amt) ELSE 'Same' END AS inward_amt_diff,

    CASE WHEN ISNULL(s1_log.rem_amt, 0) <> ISNULL(test.rem_amt, 0) THEN CONCAT('rem_amt: ', s1_log.rem_amt, ' <> ', test.rem_amt) ELSE 'Same' END AS rem_amt_diff,

    CASE WHEN ISNULL(s1_log.inward_tot, 0) <> ISNULL(test.inward_tot, 0) THEN CONCAT('inward_tot: ', s1_log.inward_tot, ' <> ', test.inward_tot) ELSE 'Same' END AS inward_tot_diff,

    CASE WHEN ISNULL(s1_log.sub_amt, 0) <> ISNULL(test.sub_amt, 0) THEN CONCAT('sub_amt: ', s1_log.sub_amt, ' <> ', test.sub_amt) ELSE 'Same' END AS sub_amt_diff,

    CASE WHEN ISNULL(s1_log.remark, '') <> ISNULL(test.remark, '') THEN CONCAT('remark: ', s1_log.remark, ' <> ', test.remark) ELSE 'Same' END AS remark_diff,

    CASE WHEN ISNULL(s1_log.keyin_emp, '') <> ISNULL(test.keyin_emp, '') THEN CONCAT('keyin_emp: ', s1_log.keyin_emp, ' <> ', test.keyin_emp) ELSE 'Same' END AS keyin_emp_diff,

    CASE WHEN ISNULL(s1_log.keyin_date, '') <> ISNULL(test.keyin_date, '') THEN CONCAT('keyin_date: ', s1_log.keyin_date, ' <> ', test.keyin_date) ELSE 'Same' END AS keyin_date_diff,

    CASE WHEN ISNULL(s1_log.pay_date, '') <> ISNULL(test.pay_date, '') THEN CONCAT('pay_date: ', s1_log.pay_date, ' <> ', test.pay_date) ELSE 'Same' END AS pay_date_diff,

    CASE WHEN ISNULL(s1_log.pick_yn, '') <> ISNULL(test.pick_yn, '') THEN CONCAT('pick_yn: ', s1_log.pick_yn, ' <> ', test.pick_yn) ELSE 'Same' END AS pick_yn_diff,

    CASE WHEN ISNULL(s1_log.snap_no, '') <> ISNULL(test.snap_no, '') THEN CONCAT('snap_no: ', s1_log.snap_no, ' <> ', test.snap_no) ELSE 'Same' END AS snap_no_diff,

    CASE WHEN ISNULL(s1_log.pick_date, '') <> ISNULL(test.pick_date, '') THEN CONCAT('pick_date: ', s1_log.pick_date, ' <> ', test.pick_date) ELSE 'Same' END AS pick_date_diff


FROM ERP_02.dbo.aaa270_log s1_log

INNER JOIN ERP.dbo.aaa270 test

    ON s1_log.comp_id = test.comp_id

   AND s1_log.auto_no = test.auto_no


[SQL]直接比對2個資料庫,共有的同名TABLE,有那些同名欄位型態長度不同

 

只要有DBlink 可以直接比對2個資料庫,共有的同名TABLE

 

有那些同名欄位型態長度不同

 

SELECT 

T1.TABLE_NAME AS TABLE_NAME

    ,T1.COLUMN_NAME AS COLUMN_NAME,

    T1.DATA_TYPE AS TABLE1_DATA_TYPE,

    T1.CHARACTER_MAXIMUM_LENGTH AS TABLE1_MAX_LENGTH,

    T2.DATA_TYPE AS TABLE2_DATA_TYPE,

    T2.CHARACTER_MAXIMUM_LENGTH AS TABLE2_MAX_LENGTH

                  , T1.NUMERIC_PRECISION as T1_NUMERIC_PRECISION

        ,     T2.NUMERIC_PRECISION as T2_NUMERIC_PRECISION

FROM

    FI2LH.INFORMATION_SCHEMA.COLUMNS T1

JOIN

    [XXXXXX].FI2LH.INFORMATION_SCHEMA.COLUMNS T2

ON

    T1.COLUMN_NAME = T2.COLUMN_NAME

WHERE

    T1.TABLE_NAME = T2.TABLE_NAME

    AND (T1.DATA_TYPE <> T2.DATA_TYPE

         OR COALESCE(T1.CHARACTER_MAXIMUM_LENGTH, -1) <> COALESCE(T2.CHARACTER_MAXIMUM_LENGTH, -1)

                or T1.NUMERIC_PRECISION <> T2.NUMERIC_PRECISION

                )             order by T1.TABLE_NAME , T1.COLUMN_NAME

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

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