Menu
How do a I list all tables containing a given column name? I'm using Mysql version 4.1.13-nt-log. I know versions less than 5 dont have an information_scheme DB.
751
SELECT DISTINCT TABLE_NAME FROM information_schema. columns WHERE TABLE_SCHEMA = 'your_db_name' AND TABLE_NAME NOT IN (SELECT DISTINCT TABLE_NAME FROM information_schema. columns WHERE column_name = 'column_name' AND TABLE_SCHEMA = 'your_db_name');
USE db_name; DESCRIBE table_name; it'll give you column names with the type.
Here's the query you can use on the metastore: select TBL_NAME, COLUMN_NAME, TYPE_NAME from TBLS left join COLUMNS_V2 on CD_ID = TBL_ID where COLUMN_NAME like 'column'; where 'column' is the column name you're looking for.
Find all tables and columns where column names are like the search term:
SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%partial_column_name%'
AND TABLE_SCHEMA='YourDatabase';
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
