I have 2-3 different column names that I want to look up in the entire database and list out all tables which have those columns. Is there any easy script?
-
1Refer this: winashwin.wordpress.com/2012/08/28/mysql-searchuser1573308– user15733082012-09-05 06:44:24 +00:00Commented Sep 5, 2012 at 6:44
Add a comment
|
11 Answers
To get all tables with columns columnA or ColumnB in the database YourDatabase:
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('columnA','ColumnB')
AND TABLE_SCHEMA='YourDatabase';
16 Comments
Ken
@Echo - you can always play with mysqldump with --skip-extended-insert and then grep through the file... dirty but strangely satifying :)
Radu M.
@Echo, for versions prior to 5 dump the structure to file using mysqldump, see my answer.
Sherlock
You can also use
DATABASE() instead of a string to search in the currently selected database.
Fred FLECHE
@Ken : is there a way to add one more filter to your query ? Indeed, selecting the tables where de columnA has a specific value.
Ken
@FredFLECHE I think at that stage I would just loop through them in a script and keep it simple
|
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%wild%';
7 Comments
John Millikin
If there are multiple databases, add a WHERE TABLE_SCHEMA="" line also.
Jobi Joy
Thanks John, how will the query if I need to get Table names which has columnA and ColumnB present?
Abel
@JohnMillikin: or conversely, if you don't know the database but only the field name, leave it out and add
TABLE_SCHEMA to the fields of the return set to see all databases + tables that contain that column name.
Uncle Iroh
Shouldn't that read either: like '%wild%' or = 'wild' instead of like 'wild'?
wdkrnls
I just tried this but noticed all the results I got where for objects that don't show up with the query
show tables;. Can anyone explain why this might be the case? |
More simply done in one line of SQL:
SELECT * FROM information_schema.columns WHERE column_name = 'column_name';
2 Comments
Guney Ozsan
Are there any disadvantages of using * here?
timetofly
@GuneyOzsan the only disadvantage is getting more data than just the table name, which is what the OP was asking for
SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'employee%'
AND TABLE_SCHEMA='YourDatabase'
Comments
In older MySQL versions or some MySQL NDB Cluster versions that do not have information_schema, you can dump the table structure and search the column manually.
mysqldump -h$host -u$user -p$pass --compact --no-data --all-databases > some_file.sql
Now search the column name in some_file.sql using your preferred text editor, or use some nifty AWK scripts.
And a simple sed script to find the column. Just replace COLUMN_NAME with yours:
sed -n '/^USE/{h};/^CREATE/{H;x;s/\nCREATE.*\n/\n/;x};/COLUMN_NAME/{x;p};' <some_file.sql
USE `DATABASE_NAME`;
CREATE TABLE `TABLE_NAME` (
`COLUMN_NAME` varchar(10) NOT NULL,
You can pipe the dump directly in sed, but that's trivial.
Comments
For those searching for the inverse of this, i.e. looking for tables that do not contain a certain column name, here is the query...
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');
This came in really handy when we began to slowly implement use of InnoDB's special ai_col column and needed to figure out which of our 200 tables had yet to be upgraded.
Comments
Use this one line query. Replace desired_column_name by your column name.
SELECT TABLE_NAME FROM information_schema.columns WHERE column_name = 'desired_column_name';
Comments
If you want to "get all tables only", then use this query:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like '%'
and TABLE_SCHEMA = 'tresbu_lk'
If you want "to get all tables with columns", then use this query:
SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE '%'
AND TABLE_SCHEMA='tresbu_lk'
Comments
select distinct table_name
from information_schema.columns
where column_name in ('ColumnA')
and table_schema='YourDatabase';
and table_name in
(
select distinct table_name
from information_schema.columns
where column_name in ('ColumnB')
and table_schema='YourDatabase';
);
That ^^ will get the tables with ColumnA and ColumnB instead of ColumnA or ColumnB like the accepted answer
1 Comment
Peter Mortensen
Does that actually work? What about the first semicolon? In any case, an explanation would be in order. E.g., what is the gist of it? What version of MySQL did you test it on? Etc. Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today).
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%city_id%' AND TABLE_SCHEMA='database'
Comments
The problem with information_schema is that it can be terribly slow. It is faster to use the SHOW commands.
After you select the database you first send the query SHOW TABLES. And then you do SHOW COLUMNS for each of the tables.
In PHP that would look something like
$res = mysqli_query("SHOW TABLES");
while($row = mysqli_fetch_array($res))
{ $rs2 = mysqli_query("SHOW COLUMNS FROM ".$row[0]);
while($rw2 = mysqli_fetch_array($rs2))
{ if($rw2[0] == $target)
....
}
}
1 Comment
Peter Mortensen
Can you quantify "terribly slow"? Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today).