Create view for view information from INFORMATION_SCHEMA.VIEWS : Matadata « Command MySQL « SQL / MySQL






  1. SQL / MySQL
  2. Command MySQL
  3. Matadata

Create view for view information from INFORMATION_SCHEMA.VIEWS

    
mysql>
mysql> CREATE   OR REPLACE VIEW VIEWS
    ->         (VIEW_CREATOR, VIEW_NAME, CREATE_TIMESTAMP,
    ->          WITHCHECKOPT, IS_UPDATABLE, VIEWFORMULA, COMMENT) AS
    -> SELECT   UPPER(V.TABLE_SCHEMA), UPPER(V.TABLE_NAME),
    ->          T.CREATE_TIME,
    ->          CASE
    ->             WHEN V.CHECK_OPTION = 'None' THEN 'NO'
    ->             WHEN V.CHECK_OPTION = 'Cascaded' THEN 'CASCADED'
    ->             WHEN V.CHECK_OPTION = 'Local' THEN 'LOCAL'
    ->             ELSE 'Yes'
    ->          END, V.IS_UPDATABLE, V.VIEW_DEFINITION, T.TABLE_COMMENT
    -> FROM     INFORMATION_SCHEMA.VIEWS AS V,
    ->          INFORMATION_SCHEMA.TABLES AS T
    -> WHERE    V.TABLE_NAME = T.TABLE_NAME
    -> AND      V.TABLE_SCHEMA = T.TABLE_SCHEMA
    -> ;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.Create view for base table and temporary table
2.Create view for columns
3.Create view for column information
4.Create view for user authentication
5.Create view for database authentication
6.Create view on table Privileges
7.Create view for Column Privileges