Creating or modifying a view
create view xyz_v
as
select …
In order to change an already existing view, on SQL Server 2016 or newer, create or alter
can be used:
create or alter view xyz_v
as
select …
create or alter
roughly corresponds to Oracle's create or replace
clause.
Showing the definition of a view
The definition of a view can be queried with
sp_helptext
:
exec sp_helptext 'nameOfTheView'
sp_helptext
returns one record for each line of the view definition.
In order to get the view definition on one value (one record), the definition (plus some additional information about the view characteristics) can be extracted like so:
select
definition,
uses_ansi_nulls,
uses_quoted_identifier,
is_schema_bound
from
sys.sql_modules
where
object_id = object_id('nameOfView');