SQL - correct syntax

Hello experts,

I am trying to write a query that searches for columns with a certain phrase in the column name.  The environment is using SQLyog v5.1.

So far I have:

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%test%'
ORDER BY schema_name, table_name;

but it gives me an error:

Error Code : 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 '(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER ' at line 2


Any ideas?

Thanks
robthomas09Asked:
Who is Participating?
 
Walter RitzelSenior Software EngineerCommented:
Ok, I think I got it, but you have to be very specific:
- If your intention is to run the query against a MySQL Server database, please use the first I provided after you make sure you have permission to execute it;
- If in reality you need to run that on a MSSQL Server database, your first query is good.

By the result of your first query, you have executed against a MySQL database. Is this what you want or may have you connected to the wrong database?

It is not uncommon to mix between MySQL and MSSQL.
0
 
Walter RitzelSenior Software EngineerCommented:
it seems to me that you are using an ORACLE query to get metadata information in a MYSQL database.
Researching a translation for this...
0
 
Walter RitzelSenior Software EngineerCommented:
For MySQL, the query could be:
SELECT table_name, table_schema as schema_name, column_name
FROM information_schema.columns AS t
where t.column_name like '%test%'
order by table_schema, table_name;

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Walter RitzelSenior Software EngineerCommented:
And if you insist on having both tables involved:
SELECT t.table_name, 
t.table_schema as schema_name,
c.column_name
FROM 
information_schema.tables AS t
inner join 
information_schema.columns as c
on t.table_catalog = c.table_catalog
and t.table_schema = c.table_schema
and t.table_name = c.table_name
where c.column_name like '%test%'
order by t.table_schema, t.table_name;

Open in new window

0
 
robthomas09Author Commented:
With the first query I get:
Error Code : 1146
Table 'information_schema.columns' doesn't exist
(0 ms taken)

The second:
Error Code : 1146
Table 'information_schema.tables' doesn't exist
(0 ms taken)

Any other ideas?
0
 
Walter RitzelSenior Software EngineerCommented:
By the error you got on your first try, you are using a MySQL, so the queries I have provided should have worked.
But, looking specifically to the error, maybe is something simple to fix on your query. Try this:
SELECT t.name AS table_name,
t.schema_id AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c 
ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%test%'
ORDER BY schema_name, table_name;

Open in new window

0
 
robthomas09Author Commented:
This the error I received:
Error Code : 1146
Table 'sys.tables' doesn't exist
(0 ms taken)

So it looks like information_schema is not installed on the system so I am looking for a way to combine the tables without using information_schema.

Any ideas?
0
 
Walter RitzelSenior Software EngineerCommented:
Information_schema is installed. You need to request permission to your user to access it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.