How to list all tables that contain a specific column name in MySQL? You want to look for tables using the name of columns in them. SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA. COLUMNS WHERE COLUMN_NAME IN('column1', 'column2') AND TABLE_SCHEMA = 'schema_name';
MyTable) and hit ALT + F1 , you'll get a list of column names, type, length, etc.
USE YourDatabseName GO 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.
select * from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position
To list all the fields from a table in MySQL:
select *
from information_schema.columns
where table_schema = 'your_DB_name'
and table_name = 'Your_tablename'
it is better that you use the following query to get all column names easily
Show columns from tablename
SELECT * FROM information_schema.columns
WHERE table_schema = DATABASE()
ORDER BY table_name, ordinal_position
Since I don't have enough rep to comment, here's a minor improvement (in my view) over nick rulez's excellent answer: replacing WHERE table_schema = 'your_db'
with WHERE table_schema = DATABASE()
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With