Renaming a view
-
Hi,
SQL Server 2000 Q.
What is the correct way to rename a view so that when you generate a sql script it creates the view with the new name.
Example:
I create a view in enterprise manager called test.
I rename the view in enterprise manager to renamed.
I generate a script and it gives me the following script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[renamed]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[renamed]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.test
AS
SELECT dbo.AA_AccountParts.*
FROM dbo.AA_AccountParts
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Apparently, this is a known feature
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q243198
it also happens when using: exec sp_rename 'OriginalName', 'NewName', 'OBJECT'
You MAY be able to script your own procedures to workaround this but if you are going to be messing around in the system catalog you better know exactly what you are doing.
-
After you successfully change the name in EM, double click the view to enter "properties" and change the name CREATE statement as well.
-
Yeah. I overthought the issue. After a quick scan of the question, I figured that he wanted to know how to automate the renaming of a view.
-
Experience has shown that renaming any object may produce "issues" . I've always insisted that a rename is done by a drop and re-create. The use of the GUI is a no-no in a controlled environment, use scripts which are repeatable and recordable.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Concur with THAT!!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply