CREATE VIEW with existence check

Question

I'm trying to do this:

IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[viewName]')
AND type in (N'U')
)

CREATE VIEW [dbo].[viewName] AS
-- Such and such
GO

This will throw the "'CREATE VIEW' must be the only statement in the batch" error.

I've tried suggestions from here (surround with BEGIN/END) and here (use GO statements, use dynamic SQL) and none alleviate the error.

`type in (N'U')` is checking for a table not a view. Fix that and the dynamic SQL will work — Martin Smith, Nov 30 '16 at 16:40

Rich Benner · Accepted Answer · 2016-11-30T16:54:22.577

1

Try this logic, yours is a little off;

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE object_id= object_id(N'[dbo].[ViewName]') AND OBJECTPROPERTY(object_id, N'IsView') = 1)

I'll be honest, I always drop and recreate to ensure you're running the latest version of the view, something like this;

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE object_id= object_id(N'[dbo].[ViewName]') AND OBJECTPROPERTY(object_id, N'IsView') = 1)
BEGIN
    DROP VIEW [dbo].ViewName
END
GO

CREATE VIEW ViewName
AS
    SELECT

Yeah, so checking the comments you'll want to use dynamic SQL;

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[viewName]') AND OBJECTPROPERTY(object_id, N'IsView') = 1)
BEGIN
DECLARE @sql nvarchar(MAX)
--SELECT 'Do this mofo'
SET @sql = N'CREATE VIEW [dbo].[viewName] 
             AS
             SELECT COUNT(1) FieldName FROM sys.objects'

EXEC sp_executesql @sql

END
GO

edited Nov 30 '16 at 16:54

answered Nov 30 '16 at 16:41

Rich Benner

7,873
9
33
39

I would not advise this *DROP and CREATE*-approach. It can easily break, if the view is bound somewhere. And - very annoying! - you might lose connected GRANTs... – Shnugo Nov 30 '16 at 16:44
Sure, it all depends on the application. I work for a software vendor and this is the best way for our particular instance. Everything should obviously be tested in a development environment first. – Rich Benner Nov 30 '16 at 16:45
@Rich: Yeah, I wanted to drop too, but had removed it as part of debugging this. :-) – Nov 30 '16 at 16:46
1

Once I did this on a customer's system. Everything was fine. But there was a cross-system-job I didn't know of. Specially given GRANTs were gone and no server admin nearby... That was quite bad... – Shnugo Nov 30 '16 at 16:47
@Shnugo, appreciate that. – Nov 30 '16 at 16:48
@Francho, but this approach has still got one flaw: If the VIEW exists, but not in the right / last version, it will skip it's creation. Therefore I'd rather use an approach where you use `CREATE VIEW` for a *naked* system, and `ALTER VIEW` in case of existance... – Shnugo Nov 30 '16 at 17:02
@Shnugo, I will look into that. In this case it looked like it would be difficult to do an alter, but maybe my thinking's wrong. – Nov 30 '16 at 17:04
@Francho If you look at my answer, you just define the `CREATE VIEW` statement as you need it, and change the `CREATE` to `ALTER` if needed. There's nothing wrong to use `ALTER VIEW` with exactly the same code as it is existing already... Doing so, the VIEW will be written in any case... – Shnugo Nov 30 '16 at 17:05

Shnugo · Answer 2 · 2016-11-30T17:09:08.283

1

May be like this:

DECLARE @cmd NVARCHAR(MAX)=N'CREATE VIEW ....;';

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.VIEWS AS v WHERE v.TABLE_NAME='ViewName') --yes, it's TABLE_NAME!
    SET @cmd=STUFF(@cmd,1,6,N'ALTER');

EXEC(@cmd);

edited Nov 30 '16 at 17:09

answered Nov 30 '16 at 16:55

Shnugo

66,100
9
53
114

score 0 · Answer 3 · edited May 23 '17 at 11:53

0

It's unlikely you're currently using SQL 2016 SP1, but for posterity, you can now do

CREATE OR ALTER VIEW... https://stackoverflow.com/a/40707436/155892

edited May 23 '17 at 11:53

Community

1
1

answered Nov 30 '16 at 16:48

Mark Sowul

10,244
1
45
51

woohoo! This is something badly needed! – Shnugo Nov 30 '16 at 17:08

CREATE VIEW with existence check

3 Answers3