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?

Dyanne

TODO: Change tags to something like: sql-server sql-server-2005 tsql – Unearth

Answer 1

You can use the ALTER VIEW statement something like this :

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

Reference on MSDN

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".

Megalo

OK, but where do I set the new name of the myView view? Can't find it in the code you posted. – Dyanne

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. – Andrien

This answer is incomplete. I tried to edit it but the edit queue was full so instead I wrote what I consider to be a more nuanced response - stackoverflow.com/a/78950945 – Tumbrel

Answer 2

you can use the gui in sms

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

Telesthesia

Answer 3

ALTER VIEW cannot be used to rename a view. You need to either recreate it (recommended) or rename it using sp_rename:

EXEC sp_rename 'vContatcs', 'vContacts'

Renaming has the benefit of preserving existing permissions but Microsoft warns against renaming views in their documentation for sp_rename as it "can break scripts and stored procedures". They suggest "instead, drop the [view] and re-create it with the new name". I don't understand how dropping the view won't also break scripts and stored procedures that are reliant on the old one but I'm sure they have a good reason for this warning.

More importantly to me (and as Gautam pointed out), the old name will persist in the definition column of sys.sql_modules which may cause confusion down the track.

For both these reasons, I recommend creating a new view.

If you don't have the old CREATE VIEW script handy, most GUIs will allow you to use the existing view to create a new one. In SSMS 19 you can right-click the old view and choose Script View as → CREATE to → New Query Editor Window:

In the query editor, give your new view a new name (if you don't, it will warn you a view with the old name already exists) and execute the query. Once you're happy with the new view, you can either keep the old one around or drop it with DROP VIEW vContatcs

If you're not using a GUI, one way to get the current definition for a view named "vContacts" is to use the following query:

SELECT       definition

FROM         sys.sql_modules
  INNER JOIN sys.objects
    ON       sql_modules.object_id = objects.object_id

WHERE        objects.name = 'vContacts'

Other ways to get the view definition include:

• use the sp_helptext stored procedure
• use the OBJECT_DEFINITION() function
• query the view_definition column in information_schema.views

See 4 Ways to Get a View’s Definition using Transact-SQL for more information.

Tumbrel

Answer 4

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

-Shaun

Vaporize