MySQL AB :: MySQL 5.0 Reference Manual :: 20.15 The INFORMATION

20.15. The `INFORMATION_SCHEMA VIEWS` Table

The VIEWS table provides information about views in databases. You must have the SHOW VIEW privilege to access this table.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`TABLE_CATALOG`		`NULL`
`TABLE_SCHEMA`
`TABLE_NAME`
`VIEW_DEFINITION`
`CHECK_OPTION`
`IS_UPDATABLE`
`DEFINER`
`SECURITY_TYPE`

Notes:

The VIEW_DEFINITION column has most of what you see in the Create Table field that SHOW CREATE VIEW produces. Skip the words before SELECT and skip the words WITH CHECK OPTION. Suppose that the original statement was:
```
CREATE VIEW v AS
  SELECT s2,s1 FROM t
  WHERE s1 > 5
  ORDER BY s1
  WITH CHECK OPTION;
```
Then the view definition looks like this:
```
SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
```
The CHECK_OPTION column always has a value of NONE.
The IS_UPDATABLE column is YES if the view is updatable, NO if the view is not updatable.
The DEFINER and SECURITY_TYPE columns were added in MySQL 5.0.14. DEFINER indicates who defined the view. SECURITY_TYPE has a value of DEFINER or INVOKER.

User Comments

Posted by Troels on June 13 2006 11:45am

[Delete] [Edit]

Theese queries may come in handy:

# list all views
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS;

# describe view
# replace ? with view name
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE
TABLE_NAME = ?;

# describe view prettyprinted
# replace ? with view name
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(VIEW_DEFINITION, ",", "\n"),
"from", "\nfrom"), "where", "\nwhere"), "join", "\njoin"), "and", "\nand")
FROM INFORMATION_SCHEMA.VIEWS
WHERE
TABLE_NAME = ?;

20.15. The INFORMATION_SCHEMA VIEWS Table

User Comments

20.15. The `INFORMATION_SCHEMA VIEWS` Table