I am trying to locate a specific column that is unknown in a database with 125 tables. I am looking for a wildcard, say, "%watcher%". Is this possible?
+2
A:
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE '%watcher%'
[AND table_schema = 'database']
ChssPly76
2009-08-04 19:33:50
I got an error when I ran this on the shell.Perhaps I mistyped?mysql> SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name LIKE '%watcher%' [AND table_schema = 'database'];ERROR 1064 (42000): 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 '[AND table_schema = 'database']' at line 1
Jonathan
2009-08-04 20:29:26
I've used square brackets to indicate that condition in them is optional. Either remove it altogether (e.g. everything after LIKE '%watcher%') or remove just the square bracket characters and replace "database" with your actual database name (keep single quotes)
ChssPly76
2009-08-04 20:33:57
I removed the square bracket and got another error: ERROR 1146 (42S02): Table 'INFORMATION_SCHEMA.COLUMNS' doesn't exist
Jonathan
2009-08-04 20:40:15
What MySQL version are you using? It should work for 5.0 and above. If you're using 4.1 and below I'm afraid you're out of luck - the only way to show columns would be a "SHOW COLUMNS" statement which can only run for one table at a time.
ChssPly76
2009-08-04 21:01:38
I am using 4.1.20
Jonathan
2009-08-05 13:29:24