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

anarresanarres Posts: 7
edited January 8, 2008 5:22AM in SQL Refactor Previous Versions
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.

Comments

Sign In or Register to comment.