19

for my company I created a view which is used for exporting. Maybe there is a problem with the query that is used to create this table.

So here is my question:

Is it possible to get the query I used to create said view table ?

0

5 Answers 5

30

View definitions are stored in the information schema:

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
   WHERE TABLE_NAME = 'yourviewname'
0
5

You can use the documented feature SHOW CREATE VIEW view_name which is documented on the official dev.MySQL.com website:

Reference:
13.7.5.14 SHOW CREATE VIEW Syntax (Version 5.5)
14.7.5.13 SHOW CREATE VIEW Syntax (Version 5.7)

mysql> SHOW CREATE VIEW v\G
*************************** 1. row ***************************
                View: v
         Create View: CREATE ALGORITHM=UNDEFINED
                      DEFINER=`bob`@`localhost`
                      SQL SECURITY DEFINER VIEW
                      `v` AS select 1 AS `a`,2 AS `b`
character_set_client: latin1
collation_connection: latin1_swedish_ci
3

"show create view" will provide you the DDL to recreate the view exactly as it is (complete with select statement).

show create view view_name_here \G

\G is enhanced go format (displays columns vertically instead of horizontally)

1

View definitions are stored in INFORMATION_SCHEMA table.

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'viewnamehere'

if you have created a view 'xyz' and after some time you have modified this view then this above query will show both query that was used to create view and query to modify view.

If you want just latest view query then you can simply run:

SHOW CREATE VIEW 'viewnamehere'

This query will show you all information about view like character_set_client, collation_connection , DEFINER etc.

-2

For the purposes of recreating the query used to create your view and for the purposes of backing up all views, I found the following:

Dump all views:

select CONCAT('CREATE or REPLACE VIEW ',table_name,' AS ',view_definition,';') from information_schema.views;

Dump views from only a specific DB:

select CONCAT('CREATE or REPLACE VIEW ',table_name,' AS ',view_definition,';') from information_schema.views WHERE table_schema = '$DB_NAME';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.