Reid McCamish Reid McCamish - 3 months ago 9
SQL Question

How do you save a CREATE VIEW statement?

EDIT: This question was based on the incorrect premise that SQL VIEWS were cleared from a database when the user that created them disconnects from the server. Leaving this question in existence in case others have that assumption.

I'm trying to use views in my database, but I'm running up against an inability to save the code as a SQL Server object for repeated use.

I tried saving

CREATE VIEW
statements as procedures and user defined functions, but as many have answered on stack overflow,
CREATE PROCEDURE
and
CREATE FUNCTION
are incompatible with
CREATE VIEW
due to the only one in batch issue.

Obviously I don't want to retype my CREATE VIEW statements every time, and I'd prefer not to have to load them from text files. I must be missing something here.

Answer

If you really want to, you could create a view in a proc like this:

CREATE PROCEDURE uspCreateView AS 
  EXEC('CREATE VIEW...  ')

Though, you'll have to escape single quotes in your view code with ''

However, I have to agree with the other comments that this seems like a strange thing to do.

Some other thoughts:

You can use sp_helptext to get the code of an existing view:

sp_helptext '<your view name here>'

Also, INFORMATION_SCHEMA.VIEWS includes a VIEW_DEFINITION column with the same code:

SELECT * FROM INFORMATION_SCHEMA.VIEWS