Smart rename on column causes unrelated SP's to be scripted

anarres

Maybe I am missing something, but when I smart rename a column it seems to script totally unrelated SP's.

It appears that this is happening for any SP that references an FK to the original (parent) table.

Example:

CREATE TABLE Parent(
	ID INT IDENTITY(1,1) NOT NULL,
	Column1 VARCHAR(50),
	PRIMARY KEY CLUSTERED(ID))
GO

CREATE TABLE Child(
	ID INT IDENTITY(1,1) NOT NULL,
	ParentID INT,
	PRIMARY KEY CLUSTERED(ID))
GO

CREATE PROCEDURE ChildSelect
	@ParentID INT
AS
	SELECT * FROM Child WHERE ParentID = @ParentID
GO

ALTER TABLE dbo.Child ADD CONSTRAINT FK_Child_Parent FOREIGN KEY(ParentID) REFERENCES Parent(ID)
GO

Now, when smart rename is done on Parent.Column1 to change to Column2, the following script is generated:

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].[ChildSelect]'
GO

ALTER PROCEDURE dbo.ChildSelect
	@ParentID INT
AS
	SELECT * FROM Child WHERE ParentID = @ParentID

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[Parent]'
GO
sp_rename N'[dbo].[Parent].[Column1]', N'Column2', 'COLUMN'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

Any ideas on why there is an ALTER PROC in the script?

It's not a terrible thing, but I like to keep my upgrade scripts clean, and this is causing many unrelated SPs to be re-created.

Ben Pinchen

I've added this as a bug in our bug tracking software. The developers will try to fix this for the next release of SQL Refactor.

anarres

Many thanks

anarres

Just out of interest, when is the next scheduled release? I noticed your last release was march 2007.

Marko68

This STILL happens in Smart Rename in SQL Prompt now! 

Also - the script generated does a column rename in a transaction, and all of the views and procs that ACTUALLY use that now renamed field - error out on their "alter" functions???

Marko68

It appears that using a transaction, while should work, prevents the rest of the DML from seeing the initial "rename" of the field in question??