Product DocumentationAppearance
sys.view
A system view that returns information about the system and user-defined views in the database.
| Column Name | Data Type | Description |
|---|---|---|
| view_id | bigint | Unique ID of the view. |
| database_id | bigint | Unique ID of the database where the view was created. |
| schema_id | bigint | Unique ID of the schema where the view was created. Join to sys.schema to get the schema name. |
| name | varchar(255) | Name of the view. |
| owner_id | bigint | Unique ID of the user who owns the view. Join to sys.user to get the name of the user. |
| definition | text | SQL text of the view. |
| is_temp | boolean | Whether the view was created as a temporary view (f=persistent; t=temporary). |
| creation_time | timestamptz | When the view was created. |
| database_name | varchar(255) | Name of the database where the procedure was created. |
This view also returns the system views and views that belong to the INFORMATION_SCHEMA (schema_id 10000).
For example:
premdb=# select sv.schema_id, ss.name, sv.name, sv.database_name
from sys.view sv join sys.schema ss on ss.schema_id=sv.schema_id and ss.database_id=sv.database_id
where database_name='premdb' and view_id>16000 order by 1,2;
schema_id | name | name | database_name
-----------+--------+-----------+---------------
2200 | public | teamview | premdb
2200 | public | matchview | premdb
16439 | prem | matchview | premdb
16439 | prem | teamview | premdb
(4 rows)premdb=# select * from sys.view
where schema_id=16439 and name='teamview';
view_id | database_id | schema_id | name | owner_id | definition | is_temp | creation_time | database_name
---------+-------------+-----------+----------+----------+----------------------+---------+------------------------------+---------------
16440 | 16402 | 16439 | teamview | 16007 | SELECT team.teamid,+| f | 2020-11-09 14:00:34.63173-08 | premdb
| | | | | team.htid, +| | |
| | | | | team.atid, +| | |
| | | | | team.name, +| | |
| | | | | team.nickname, +| | |
| | | | | team.city, +| | |
| | | | | team.stadium, +| | |
| | | | | team.capacity, +| | |
| | | | | team.avg_att +| | |
| | | | | FROM team; | | |
(1 row)