Smart Rename Issues

LxocramLxocram Posts: 25 Bronze 3
Some issues still in smart rename(after column_rename_wildcard_fix):

When renaming a column not all statements in a trigger are corrected:
e.g. UPDATE(fieldToRename)

Hypothetical indices (from DTA) are scripted to be created as real indices
wich after a few renames leads to loads of redundant and duplicate indices
(luckily i wrote a clean_redundant_indices tool)

sp_refreshview sometimes called for views that are not related to the specified column

when renaming column in history table (trigger after update original insert into original_history) the inserted and deleted columns in the trigger on the original table are also falsly renamed

Comments

  • LxocramLxocram Posts: 25 Bronze 3
    run this scenario to see the UPDATE( ) issue and the inserted/deleted column name issue (i'll post code to prove other two issues soon):

    CREATE TABLE t (RowID INT NOT NULL
    IDENTITY(1, 1)
    PRIMARY KEY,
    fooID INT,
    fooBAR VARCHAR(50),
    fooTLOOSE float)
    go
    CREATE TABLE t_Hist (HistID INT NOT NULL
    IDENTITY(1, 1)
    PRIMARY KEY,
    RowID INT,
    fooID INT,
    fooBAR VARCHAR(50),
    fooTLOOSE FLOAT,
    [audit_StartTime] [datetime] NULL,
    [audit_StartUser] [varchar](50) NULL,
    [audit_EndTime] [datetime] NULL,
    [audit_EndUser] [varchar](50) NULL)
    go
    CREATE TRIGGER [dbo].[hist_t_VALID_TIME_TABLE] ON [dbo].[t]
    FOR INSERT, UPDATE, DELETE
    NOT FOR REPLICATION
    AS
    /*AVOID ACCESS SQL PITFALL start code*/
    SET NOCOUNT on
    DECLARE @identity int,
    @strsql varchar(128)
    SET @IDENTITY
    /*AVOID ACCESS SQL PITFALL end code*/

    DECLARE @TrigTime DateTime
    set @TrigTime = getDate()


    IF UPDATE(fooID)
    OR UPDATE(fooBAR)
    OR UPDATE(footLOOSE)
    BEGIN
    UPDATE t_hist
    SET audit_endtime = (@TrigTime),
    audit_enduser = (USER_Name())
    FROM deleted,
    t_hist
    WHERE t_Hist.RowID = deleted.RowID
    AND audit_endtime = '9/9/9999'

    INSERT INTO dbo.hist_t ([RowID], fooID, fooBAR, fooTLOOSE,
    [audit_StartTime], [audit_EndTime],
    [audit_StartUser])
    SELECT [RowID], fooID, fooBAR, fooTLOOSE, @TrigTime, '9/9/9999',
    user_name()
    FROM inserted
    END
    /*AVOID ACCESS SQL PITFALL start code*/
    SET @strsql = 'select identity (int, ' + CAST(@identity as varchar(10))
    + ',1) as id into #tmp'
    EXECUTE (@strsql)
    SET NOCOUNT OFF
    /*AVOID ACCESS SQL PITFALL end code*/

    -- NOW WE RENAME e.g. FOOBAR to FOOBAROLD in t_Hist
    /*
    Script created by SQL Refactor version 1.2.0.6 from Red Gate Software Ltd at 4-03-2008 16:19:39
    Run this script on GPS to perform the Smart Rename refactoring.

    Please back up your database before running this script.
    */
    -- Summary for the smart rename:
    --
    -- Action Plan:
    -- Alter table [dbo].[t_Hist]
    -- Alter trigger [dbo].[hist_t_VALID_TIME_TABLE] on [dbo].[t]
    --
    -- No warnings
    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
    GO
    CREATE TABLE #tmpErrors (Error int)
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    GO
    BEGIN TRANSACTION
    GO
    PRINT N'Altering [dbo].[t_Hist]'
    GO
    sp_rename N'[dbo].[t_Hist].[fooBAR]', N'fooBAROLD', 'COLUMN'
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Altering trigger [dbo].[hist_t_VALID_TIME_TABLE] on [dbo].[t]'
    GO
    ALTER TRIGGER dbo.hist_t_VALID_TIME_TABLE ON [dbo].[t]
    FOR INSERT, UPDATE, DELETE
    NOT FOR REPLICATION
    AS
    /*AVOID ACCESS SQL PITFALL start code*/
    SET NOCOUNT on
    DECLARE @identity int,
    @strsql varchar(128)
    SET @IDENTITY
    /*AVOID ACCESS SQL PITFALL end code*/

    DECLARE @TrigTime DateTime
    set @TrigTime = getDate()


    IF UPDATE(fooID)
    OR UPDATE(fooBAR)
    OR UPDATE(footLOOSE)
    BEGIN
    UPDATE t_hist
    SET audit_endtime = (@TrigTime),
    audit_enduser = (USER_Name())
    FROM deleted,
    t_hist
    WHERE t_Hist.RowID = deleted.RowID
    AND audit_endtime = '9/9/9999'

    INSERT INTO dbo.hist_t ([RowID], fooID, fooBAR, fooTLOOSE,
    [audit_StartTime], [audit_EndTime],
    [audit_StartUser])
    SELECT [RowID], fooID, fooBAROLD, fooTLOOSE, @TrigTime, '9/9/9999',
    user_name()
    FROM inserted
    END
    /*AVOID ACCESS SQL PITFALL start code*/
    SET @strsql = 'select identity (int, ' + CAST(@identity as varchar(10))
    + ',1) as id into #tmp'
    EXECUTE (@strsql)
    SET NOCOUNT OFF
    /*AVOID ACCESS SQL PITFALL end code*/

    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
    GO
    @TRANCOUNT>0 BEGIN
    PRINT 'The database update succeeded'
    COMMIT TRANSACTION
    END
    ELSE PRINT 'The database update failed'
    GO
    DROP TABLE #tmpErrors
    GO
    -- THIS YIELDS
    --Altering [dbo].[t_Hist]
    --Caution: Changing any part of an object name could break scripts and stored
    --procedures.
    --Altering trigger [dbo].[hist_t_VALID_TIME_TABLE] on [dbo].[t]
    --Msg 207, Level 16, State 1, Procedure hist_t_VALID_TIME_TABLE, Line 31
    --Invalid column name 'fooBAROLD'.
    --(1 row(s) affected)
    --The database update failed

    - Now let’s try another – rename column RowID on t_hist makes it even worse

    /*
    Script created by SQL Refactor version 1.2.0.6 from Red Gate Software Ltd at 4-03-2008 16:24:58
    Run this script on GPS to perform the Smart Rename refactoring.

    Please back up your database before running this script.
    */
    -- Summary for the smart rename:
    --
    -- Action Plan:
    -- Alter table [dbo].[t_Hist]
    -- Alter trigger [dbo].[hist_t_VALID_TIME_TABLE] on [dbo].[t]
    --
    -- No warnings
    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
    GO
    CREATE TABLE #tmpErrors (Error int)
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    GO
    BEGIN TRANSACTION
    GO
    PRINT N'Altering [dbo].[t_Hist]'
    GO
    sp_rename N'[dbo].[t_Hist].[RowID]', N'RowIDOLD', 'COLUMN'
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Altering trigger [dbo].[hist_t_VALID_TIME_TABLE] on [dbo].[t]'
    GO
    ALTER TRIGGER dbo.hist_t_VALID_TIME_TABLE ON [dbo].[t]
    FOR INSERT, UPDATE, DELETE
    NOT FOR REPLICATION
    AS
    /*AVOID ACCESS SQL PITFALL start code*/
    SET NOCOUNT on
    DECLARE @identity int,
    @strsql varchar(128)
    SET @IDENTITY
    /*AVOID ACCESS SQL PITFALL end code*/

    DECLARE @TrigTime DateTime
    set @TrigTime = getDate()


    IF UPDATE(fooID)
    OR UPDATE(fooBAR)
    OR UPDATE(footLOOSE)
    BEGIN
    UPDATE t_hist
    SET audit_endtime = (@TrigTime),
    audit_enduser = (USER_Name())
    FROM deleted,
    t_hist
    WHERE t_Hist.RowIDOLD = deleted.RowIDOLD
    AND audit_endtime = '9/9/9999'

    INSERT INTO dbo.hist_t ([RowID], fooID, fooBAR, fooTLOOSE,
    [audit_StartTime], [audit_EndTime],
    [audit_StartUser])
    SELECT RowIDOLD, fooID, fooBAR, fooTLOOSE, @TrigTime, '9/9/9999',
    user_name()
    FROM inserted
    END
    /*AVOID ACCESS SQL PITFALL start code*/
    SET @strsql = 'select identity (int, ' + CAST(@identity as varchar(10))
    + ',1) as id into #tmp'
    EXECUTE (@strsql)
    SET NOCOUNT OFF
    /*AVOID ACCESS SQL PITFALL end code*/

    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
    GO
    @TRANCOUNT>0 BEGIN
    PRINT 'The database update succeeded'
    COMMIT TRANSACTION
    END
    ELSE PRINT 'The database update failed'
    GO
    DROP TABLE #tmpErrors
    GO

    --Now renaming on table T

    /*
    Script created by SQL Refactor version 1.2.0.6 from Red Gate Software Ltd at 4-03-2008 16:32:39
    Run this script on GPS to perform the Smart Rename refactoring.

    Please back up your database before running this script.
    */
    -- Summary for the smart rename:
    --
    -- Action Plan:
    -- Alter table [dbo].[t]
    -- Alter trigger [dbo].[hist_t_VALID_TIME_TABLE] on [dbo].[t]
    --
    -- No warnings
    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
    GO
    CREATE TABLE #tmpErrors (Error int)
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    GO
    BEGIN TRANSACTION
    GO
    PRINT N'Altering [dbo].[t]'
    GO
    sp_rename N'[dbo].[t].[fooBAR]', N'fooBARNEW', 'COLUMN'
    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    PRINT N'Altering trigger [dbo].[hist_t_VALID_TIME_TABLE] on [dbo].[t]'
    GO
    ALTER TRIGGER dbo.hist_t_VALID_TIME_TABLE ON [dbo].[t]
    FOR INSERT, UPDATE, DELETE
    NOT FOR REPLICATION
    AS
    /*AVOID ACCESS SQL PITFALL start code*/
    SET NOCOUNT on
    DECLARE @identity int,
    @strsql varchar(128)
    SET @IDENTITY
    /*AVOID ACCESS SQL PITFALL end code*/

    DECLARE @TrigTime DateTime
    set @TrigTime = getDate()


    IF UPDATE(fooID)
    OR UPDATE(fooBAR)
    OR UPDATE(footLOOSE)
    BEGIN
    UPDATE t_hist
    SET audit_endtime = (@TrigTime),
    audit_enduser = (USER_Name())
    FROM deleted,
    t_hist
    WHERE t_Hist.RowID = deleted.RowID
    AND audit_endtime = '9/9/9999'

    INSERT INTO dbo.hist_t ([RowID], fooID, fooBAR, fooTLOOSE,
    [audit_StartTime], [audit_EndTime],
    [audit_StartUser])
    SELECT [RowID], fooID, fooBARNEW, fooTLOOSE, @TrigTime, '9/9/9999',
    user_name()
    FROM inserted
    END
    /*AVOID ACCESS SQL PITFALL start code*/
    SET @strsql = 'select identity (int, ' + CAST(@identity as varchar(10))
    + ',1) as id into #tmp'
    EXECUTE (@strsql)
    SET NOCOUNT OFF
    /*AVOID ACCESS SQL PITFALL end code*/

    GO
    @TRANCOUNT>0 ROLLBACK TRANSACTION
    GO
    @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
    GO
    IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
    GO
    @TRANCOUNT>0 BEGIN
    PRINT 'The database update succeeded'
    COMMIT TRANSACTION
    END
    ELSE PRINT 'The database update failed'
    GO
    DROP TABLE #tmpErrors
    GO
  • Thanks for the script - I have presented this to the developers to have a look at.
    Chris Buckingham
    Red-Gate support
  • LxocramLxocram Posts: 25 Bronze 3
    Since i can't script dta to run i will not give a script but i tried it again
    and the column rename script (it does not happen on a table rename) does create hypothetical indexes

    Step 1 create a table
    Step 2 have dta run on some query's on the table
    Step 3 you can see a lot of _dta_index_tbl_... under the statistics of the table in SSMS or if you query
    FROM sys.sysindexes AS idx INNER JOIN
    sys.sysobjects AS tbl ON idx.id = tbl.id
    WHERE (idx.indid > 0) AND (INDEXPROPERTY(tbl.id,
    idx.name, 'IsStatistics') = 0) AND
    (NOT (idx.name LIKE 'SSMA_PK%' OR
    idx.name LIKE 'PK%')) AND (INDEXPROPERTY(tbl.id,
    idx.name, 'IsHypothetical') = 1)

    It's these hypothetical indices that are created as real indices in the column rename script
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Regarding the hypothetical indexes, this should be fixed in the next version of Refactor. The problem was identified in v5 of the RedGate.SQLCompare.Engine.dll and has since been fixed in SQL Compare, however, Refactor uses Engine v5.x. When Refactor is updated, the update should fix the problem. It had origianlly been reported here:
    http://www.red-gate.com/MessageBoard/vi ... ical+index
  • SQL Refactor 1.3 has now been released and uses the latest version of the SQL Compare engine.

    You can upgrade by 'checking for updates' in a previous version of SQL Refactor or it can be downloaded from:

    http://www.red-gate.com/products/SQL_Refactor/index.htm
Sign In or Register to comment.