Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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).

share|improve this answer
    
Well, the plus side is that at least i can get column metadata, but it's unfortunate that there's no way to tell from I_S what base tables are involved.. thank you! –  ilasno Oct 24 '12 at 20:09
    
Added bummer.. it looks like neither 'TABLE_CONSTRAINTS' nor 'KEY_COLUMN_USAGE' shows any info about primary/foreign keys in the view, so it seems impossible to get that info without prior knowledge of what tables make up the view :-( –  ilasno Oct 24 '12 at 20:18
    
Correct. There is no info on underlying indexes from view definitions, since there may not be index usage with the view. –  Shlomi Noach Oct 25 '12 at 10:55

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 !!!

share|improve this answer
    
the question is about the underlying tables owning those columns; I'm afraid DESC is no different from I_S in this regard: no info. –  Shlomi Noach Oct 25 '12 at 10:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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