Solved

filter out mySQL table name

Posted on 2014-10-08
3
426 Views
Last Modified: 2014-12-15
Dear all,

right now try to filter out table name with prefxi tblT_ so we don't want this kind of table from showing out, my query is :

 SELECT  DISTINCT TABLE_NAME 	
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA=<databaseName>  and table_type<> 'view' and TABLE_NAME  NOT LIKE 'tblT_%';

Open in new window


and I try to test the result set by only show out table has prefix like that:

 SELECT  DISTINCT TABLE_NAME 	
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA=<databaseName>  and table_type<> 'view' and TABLE_NAME  LIKE 'tblT_%';

Open in new window


but it seems MySQL will return all table with name prefix with tblT% instead of what we want : tblT_xxxxxx

how to solve this ?
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40370077
You are using the  wildcard chareacter in your query so you need to 'tell' My SQL engine that you want it in the name i.e.

use it like this

'
SELECT  DISTINCT TABLE_NAME 	
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA=<databaseName>  and table_type<> 'view' and TABLE_NAME  LIKE 'tblT\_%';

Open in new window

0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 40370156
very nice !
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40499817
0

Featured Post

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question