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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.