Does MySQL have information_schema.VIEW_COLUMN_USAGE or an equivalent?

Question

SQL Server has a an information_schema view, 'VIEW_COLUMN_USAGE', which you can use to determine the actual/base tables that the columns in a view reside in.

I'm looking for the equivalent in MySQL - the only view-related information_schema item i can see is VIEWS which has no column info.

Is the only way to find the tables that the columns in a view belongs to to parse the view definition? This seems rather barbaric, and maybe not possible if one doesn't control the SQL being used to define views.

Thanks for your time!

Answer 1

Sorry - the answer is: no. Indeed what you found is the only thing in I_S regarding views.

Not only is this barbaric on the client side, it is also such on the server side: when you:

SELECT ... FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='your_view'

MySQL actually drills down to referenced table definitions and this type of query is very costly if there are many nested views (I have a particular case on my servers).

Answer 2

You have a chance using the DESCRIBE command

For example, here is a view called Contact_Report using SHOW CREATE TABLE:

mysql> show create table Contact_Report\G
*************************** 1. row ***************************
                View: Contact_Report
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `Contact_Report` AS select concat(`s`.`first`,_latin1' ',`s`.`last`) AS `Salesperson_Name`,`c`.`name` AS `Lead_Provider`,date_format(from_unixtime(`cl`.`date`),_latin1'%Y-%m-%d') AS `Record_Date`,sum((case when (`cl`.`type` = _latin1'appointment') then 1 else 0 end)) AS `Appointment`,sum((case when (`cl`.`type` = _latin1'Appointment Canceled') then 1 else 0 end)) AS `Appointment_Canceled`,sum((case when (`cl`.`type` = _latin1'Bad Lead Note') then 1 else 0 end)) AS `Bad_Lead_Note`,sum((case when (`cl`.`type` = _latin1'call') then 1 else 0 end)) AS `Phone_Call`,sum((case when (`cl`.`type` = _latin1'chat') then 1 else 0 end)) AS `Chat`,sum((case when (`cl`.`type` = _latin1'delay') then 1 else 0 end)) AS `Delay`,sum((case when (`cl`.`type` = _latin1'eBrochure Viewed') then 1 else 0 end)) AS `eBrochure_Viewed`,sum((case when (`cl`.`type` = _latin1'email') then 1 else 0 end)) AS `Email`,sum((case when (`cl`.`type` = _latin1'Lead Ownership Change') then 1 else 0 end)) AS `Lead_Ownership_Change`,sum((case when (`cl`.`type` = _latin1'letter') then 1 else 0 end)) AS `Letter`,sum((case when (`cl`.`type` = _latin1'Lost Lead Note') then 1 else 0 end)) AS `Lost_Lead_Note`,sum((case when (`cl`.`type` = _latin1'note') then 1 else 0 end)) AS `Note`,sum((case when (`cl`.`type` = _latin1'Ownership Change') then 1 else 0 end)) AS `Ownership_Change`,sum((case when (`cl`.`type` = _latin1'Sold Note') then 1 else 0 end)) AS `Sold_Note`,sum((case when (`cl`.`type` = _latin1'viewed') then 1 else 0 end)) AS `Viewed`,sum((case when (`cl`.`testDrive` = _latin1'true') then 1 else 0 end)) AS `Test_Drive` from ((((`D21`.`contactLog` `cl` join `icar`.`sales` `s` on((`s`.`id` = `cl`.`salesID`))) join `icar`.`dealer` `d` on((`d`.`id` = 21))) join `D21`.`leads` `l` on((`l`.`id` = `cl`.`leadID`))) join `icar`.`sources` `c` on((`c`.`id` = `l`.`sourceID`))) group by concat(`s`.`first`,_latin1' ',`s`.`last`),date_format(from_unixtime(`cl`.`date`),_latin1'%Y-%m-%d')
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

That was hideous. Now, let's try DESCRIBE:

mysql> desc Contact_Report;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Salesperson_Name      | varchar(151)  | NO   |     |         |       |
| Lead_Provider         | varchar(255)  | NO   |     |         |       |
| Record_Date           | varchar(10)   | YES  |     | NULL    |       |
| Appointment           | decimal(23,0) | YES  |     | NULL    |       |
| Appointment_Canceled  | decimal(23,0) | YES  |     | NULL    |       |
| Bad_Lead_Note         | decimal(23,0) | YES  |     | NULL    |       |
| Phone_Call            | decimal(23,0) | YES  |     | NULL    |       |
| Chat                  | decimal(23,0) | YES  |     | NULL    |       |
| Delay                 | decimal(23,0) | YES  |     | NULL    |       |
| eBrochure_Viewed      | decimal(23,0) | YES  |     | NULL    |       |
| Email                 | decimal(23,0) | YES  |     | NULL    |       |
| Lead_Ownership_Change | decimal(23,0) | YES  |     | NULL    |       |
| Letter                | decimal(23,0) | YES  |     | NULL    |       |
| Lost_Lead_Note        | decimal(23,0) | YES  |     | NULL    |       |
| Note                  | decimal(23,0) | YES  |     | NULL    |       |
| Ownership_Change      | decimal(23,0) | YES  |     | NULL    |       |
| Sold_Note             | decimal(23,0) | YES  |     | NULL    |       |
| Viewed                | decimal(23,0) | YES  |     | NULL    |       |
| Test_Drive            | decimal(23,0) | YES  |     | NULL    |       |
+-----------------------+---------------+------+-----+---------+-------+
19 rows in set (0.01 sec)

mysql>

Much Better !!!