SQL Server: How to change name in a view?

Question

I am using Visual Studio 2008 and SQL Server 2008 Express.

How can I change the name of the view? I can change tables' names, but I can't change the view name.

Any suggestion?

Thank you, Fabio Milheiro

TODO: Change tags to something like: sql-server sql-server-2005 tsql — Wayne Bloss, Sep 10 '09 at 00:18

score 24 · Accepted Answer · edited Sep 08 '14 at 14:41

24

You can use the ALTER VIEW statement something like this :

ALTER VIEW dbo.myView
AS
SELECT foo
FROM dbo.bar
WHERE widget = 'foo'
GO

To rename a view, use sp_rename System Stored Procedure :

EXEC sp_rename 'dbo.myView', 'myNewViewName'

Note: don't include the schema name in the second string, or else you'll get a name like "dbo.dbo.myNewViewName".

edited Sep 08 '14 at 14:41

TheRubberDuck

answered Sep 10 '09 at 00:16

MaxiWheat

OK, but where do I set the new name of the myView view? Can't find it in the code you posted. – Fabio Milheiro Sep 10 '09 at 14:10
A word of warning in case of 'sp_rename' - `Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.` – Gautam Kumar Samal Apr 22 '17 at 12:12

score 3 · Answer 2 · answered Sep 10 '09 at 00:14

3

you can use the gui in sms

you can right click view, edit it, then do generate script if you want the code

answered Sep 10 '09 at 00:14

mson

score 0 · Answer 3 · answered Sep 10 '09 at 00:13

0

You can use the ALTER command or CREATE a new one and delete the old one.

-Shaun

answered Sep 10 '09 at 00:13

Shaun McDonnell

3 Answers3