I am getting an error incorrect syntax near the key word 'view'
if object_id('my_view') is null
create view dbo.my_view as
select * from dbo.my_table;
Syntax indicates that it is
SQL Server. You could use dynamic sql:
IF OBJECT_ID('my_view') IS NULL BEGIN EXEC('CREATE VIEW dbo.my_view AS SELECT * FROM dbo.my_table;'); END;
The point is that CREATE VIEW:
A view can be created only in the current database. The CREATE VIEW must be the first statement in a query batch. A view can have a maximum of 1,024 columns.
One way to achieve it is to use Dynamic-SQL to create new "context", where DDL is the first statment in batch.
Also I strongly recommend to specify all columns explicitly. Using
SELECT * is common anti-pattern.