View Definition

Regular SQL view definitions are supported. A view can be seen as a stored SELECT query with a unique name. It can be used in queries at all the places where you can normally use a table name.

Views are useful to reduce user query complexity as they can include joins, computations, derivations, aggregations, selections, conditions, ordering, and more in the view definition, so the user doesn't have to repeat them in the queries again and again. Simply select from the predefined view. They are also very useful to standardize and simplify reporting.

Views do not contain or store data, so do not require disk space.

view_def:
    CREATE [ OR REPLACE ] VIEW [ schema_name '.' ] view_name
           [ '(' column_name [ ',' column_name ] [ ',' ...] ')' ]
           AS select_query_without_limit
           [ WITH CHECK OPTION ]

Examples

CREATE VIEW user_views AS
SELECT schema_id, id as view_id, name, type, substring(query, 0, 80) as view_def
  FROM sys.tables
 WHERE NOT system
   AND type IN (SELECT table_type_id FROM sys.table_types WHERE table_type_name = 'VIEW')
 ORDER BY schema_id, name;

SELECT * FROM user_views;

DROP VIEW user_views;

Drop view statement

drop_view:
     DROP VIEW [ IF EXISTS ] [ schema_name '.' ] view_name [ RESTRICT | CASCADE ]

If a view is referenced (e.g. from another view, a function or procedure or another db object except comments) the view can not be dropped. So default drop action is RESTRICT. To find out which dependencies exist for a view query the system view: sys.dependencies_vw. You will need to drop the referencing objects first or use drop action CASCADE to specify to also drop those referencing objects cascading.

See also system tables/views: sys.tables, information_schema.views, sys.columns, information_schema.columns.

To find out which user created views are defined in your database run query:

SELECT * FROM sys.tables
 WHERE NOT system
   AND type IN (SELECT table_type_id FROM sys.table_types WHERE table_type_name = 'VIEW')
 ORDER BY schema_id, name;

or alternatively use query

SELECT * FROM information_schema.views
 WHERE NOT is_system
 ORDER BY table_schema, table_name;