Check if a sql view exists and then create it dynamically before altering it

Question

I am trying check if a SQL Server view exists and if it doesn't exist, create a view with dynamic script and then alter it.

I am using the following script

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[test]') AND OBJECTPROPERTY(id,N'IsView') = 1)
BEGIN
    EXEC dbo.sp_executesql @statement = N' CREATE VIEW [dbo].[test] AS '
END
GO

ALTER VIEW [dbo].[test]
---
---

The above script throws this error

Msg 102, Level 15, State 1, Procedure test, Line 1
Incorrect syntax near 'AS'.

May I know the correct way to do it?

Try adding a simple SELECT statement, like: ' CREATE VIEW [dbo].[test] AS SELECT 0' — Tab Alleman
– Tab Alleman, Commented Feb 10, 2016 at 16:19
@TabAlleman That would work as a SPROC but views require a column name — Chris Schiffhauer
– Chris Schiffhauer, Commented Feb 10, 2016 at 16:25

Chris Schiffhauer · Accepted Answer · 2016-02-10 16:21:48Z

3

The CREATE statement is incomplete. You can create the procedure by editing it to the following.

CREATE VIEW [dbo].[test] AS SELECT 1 'foo'

Once it is created, you can move on to altering it.

Edit: Fully escaped it will look like this:

EXEC dbo.sp_executesql @statement = N' CREATE VIEW [dbo].[test1] AS SELECT 1 ''foo'' '

answered Feb 10, 2016 at 16:21

Chris Schiffhauer

17.3k15 gold badges82 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom H · Accepted Answer · 2016-02-10 16:23:05Z

0

You have no actual view definition in your code. That being said, why not just drop the view if it does exist and then your code can just always create the view instead of trying to do an ALTER?

answered Feb 10, 2016 at 16:23

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

2 Comments

DoIt Over a year ago

yeah but dropping a view will drop all the permissions for the users as we are creating a new view altogether

Tom H Over a year ago

True, although I usually have permissions included as part of my deploy process or included as part of the object script, so it's not a problem for me. I suppose it all depends on how you're handling that.

Collectives™ on Stack Overflow

Check if a sql view exists and then create it dynamically before altering it

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related