In Oracle, I can re-create a view with a single statement, as shown here:
CREATE OR REPLACE VIEW MY_VIEW AS
SELECT SOME_FIELD
FROM SOME_TABLE
WHERE SOME_CONDITIONS
As the syntax implies, this will drop the old view and re-create it with whatever definition I've given.
Is there an equivalent in MSSQL (SQL Server 2005 or later) that will do the same thing?
This question is related to
sql-server
I typically use something like this:
if exists (select * from dbo.sysobjects
where id = object_id(N'dbo.MyView') and
OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.MyView
go
create view dbo.MyView [...]
For reference from SQL Server 2016 SP1+
you could use CREATE OR ALTER VIEW
syntax.
CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH <view_attribute> [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] [ ; ]
OR ALTER
Conditionally alters the view only if it already exists.
In SQL Server 2016 (or newer) you can use this:
CREATE OR ALTER VIEW VW_NAMEOFVIEW AS ...
In older versions of SQL server you have to use something like
DECLARE @script NVARCHAR(MAX) = N'VIEW [dbo].[VW_NAMEOFVIEW] AS ...';
IF NOT EXISTS(SELECT * FROM sys.views WHERE name = 'VW_NAMEOFVIEW')
-- IF OBJECT_ID('[dbo].[VW_NAMEOFVIEW]') IS NOT NULL
BEGIN EXEC('CREATE ' + @script) END
ELSE
BEGIN EXEC('ALTER ' + @script) END
Or, if there are no dependencies on the view, you can just drop it and recreate:
IF EXISTS(SELECT * FROM sys.views WHERE name = 'VW_NAMEOFVIEW')
-- IF OBJECT_ID('[dbo].[VW_NAMEOFVIEW]') IS NOT NULL
BEGIN
DROP VIEW [VW_NAMEOFVIEW];
END
CREATE VIEW [VW_NAMEOFVIEW] AS ...
I use:
IF OBJECT_ID('[dbo].[myView]') IS NOT NULL
DROP VIEW [dbo].[myView]
GO
CREATE VIEW [dbo].[myView]
AS
...
Recently I added some utility procedures for this kind of stuff:
CREATE PROCEDURE dbo.DropView
@ASchema VARCHAR(100),
@AView VARCHAR(100)
AS
BEGIN
DECLARE @sql VARCHAR(1000);
IF OBJECT_ID('[' + @ASchema + '].[' + @AView + ']') IS NOT NULL
BEGIN
SET @sql = 'DROP VIEW ' + '[' + @ASchema + '].[' + @AView + '] ';
EXEC(@sql);
END
END
So now I write
EXEC dbo.DropView 'mySchema', 'myView'
GO
CREATE View myView
...
GO
I think it makes my changescripts a bit more readable
It works fine for me on SQL Server 2017:
USE MSSQLTipsDemo
GO
CREATE OR ALTER PROC CreateOrAlterDemo
AS
BEGIN
SELECT TOP 10 * FROM [dbo].[CountryInfoNew]
END
GO
https://www.mssqltips.com/sqlservertip/4640/new-create-or-alter-statement-in-
You can use 'IF EXISTS' to check if the view exists and drop if it does.
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'MyView') DROP VIEW MyView GO CREATE VIEW MyView AS .... GO
You can use ALTER to update a view, but this is different than the Oracle command since it only works if the view already exists. Probably better off with DaveK's answer since that will always work.
Source: Stackoverflow.com