Xeoncross Posted February 11, 2008 Share Posted February 11, 2008 I am trying to show tables that have a "cms_" prefix to them. I can show all tables but I can't limit the tables shown to just tables that contain that prefix. Can someone help me with my pseudo-code? SHOW OPEN TABLES WHERE `table` LIKE '%cms_%' http://dev.mysql.com/doc/refman/5.0/en/show-open-tables.html Quote Link to comment Share on other sites More sharing options...
effigy Posted February 11, 2008 Share Posted February 11, 2008 Since you want a prefix it should be 'cms_%'. Quote Link to comment Share on other sites More sharing options...
Xeoncross Posted February 11, 2008 Author Share Posted February 11, 2008 True, but that really isn't the problem. I just can't figure out what MYSQL would call the table name while I did a LIKE on it. Quote Link to comment Share on other sites More sharing options...
effigy Posted February 12, 2008 Share Posted February 12, 2008 I just can't figure out what MYSQL would call the table name while I did a LIKE on it. What do you mean? What results are you getting from that SQL? Quote Link to comment Share on other sites More sharing options...
Xeoncross Posted February 12, 2008 Author Share Posted February 12, 2008 I can run the following and get ALL tables from a database: SHOW OPEN TABLES But when I try to limit the tables to ONLY those with a "cms_* prefix I don't know the SQL stantaz to do that. I don't even know if it is possible! SHOW OPEN TABLES WHERE `table` LIKE 'cms_%' Quote Link to comment Share on other sites More sharing options...
effigy Posted February 12, 2008 Share Posted February 12, 2008 So you haven't even tried SHOW OPEN TABLES WHERE `table` LIKE 'cms_%'? Quote Link to comment Share on other sites More sharing options...
Xeoncross Posted February 12, 2008 Author Share Posted February 12, 2008 So you haven't even tried SHOW OPEN TABLES WHERE `table` LIKE 'cms_%'? Yes, I tried it Why would I waste my time asking a question about something I can test in 3 seconds? In the above query you will notice the "`table`" keyword is used. I think that one of the main problems with this statement is that '`table`' is the wrong name for the comparison I am using. What would MySQL call a "table" when doing a LIKE test? "NAME"? "VALUE"? "TABLE NAME"? Quote Link to comment Share on other sites More sharing options...
svivian Posted February 12, 2008 Share Posted February 12, 2008 I just tested what you posted above and it works fine for me. `table` is fine because it is in slanty quotes so MySQL knows to treat it as a column name. If you need to limit it to one database then you'll need to add another clause to the end, something like: AND `database`='dbname' BTW a good way of working this sort of thing out is looking at the general query without clauses, ie "SHOW OPEN TABLES". I ran this is phpmyadmin and it brought back 4 columns: Database, Table, In_use and Name_locked. These can be used just like any other DB column. You can simply add WHERE clauses to the base query to be more specific. Quote Link to comment Share on other sites More sharing options...
Xeoncross Posted February 12, 2008 Author Share Posted February 12, 2008 I just tested what you posted above and it works fine for me. `table` is fine because it is in slanty quotes so MySQL knows to treat it as a column name. First, It is not a column name I want - it is a table name. Columns have nothing to do with my question. Second, the query doesn't work on my server, so if you changed anything to get it to work let me know. ERROR: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `table` LIKE 'cms_%'' at line 1 BTW a good way of working this sort of thing out is looking at the general query without clauses, ie "SHOW OPEN TABLES". I ran this is phpmyadmin and it brought back 4 columns: Database, Table, In_use and Name_locked. I already said twice that "SHOW OPEN TABLES" (no temp tables) and "SHOW TABLES" (all tables) both work - They are built into MYSQL. My question is how to LIMIT the tables so that Temp tables and tables without a 'cms_' prefix DON'T show up. Quote Link to comment Share on other sites More sharing options...
Xeoncross Posted February 12, 2008 Author Share Posted February 12, 2008 Ok, I finally found it. To limit the results to ONLY tables with a prefix use the following: SHOW TABLES LIKE 'cms_%' I hope this helps someone Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.