Smart Rename Issues

Lxocram

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

Lxocram

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

chris.buckingham

Thanks for the script - I have presented this to the developers to have a look at.

Lxocram

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 Donahue

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

Ben Pinchen

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