3

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?

Jon Weinraub
  • 397
  • 1
  • 8
  • 18

2 Answers2

4
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
  • 99,456
  • 24
  • 206
  • 195
  • 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 – Jon Weinraub Aug 04 '09 at 20:29
  • 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 Aug 04 '09 at 20:33
  • I removed the square bracket and got another error: ERROR 1146 (42S02): Table 'INFORMATION_SCHEMA.COLUMNS' doesn't exist – Jon Weinraub Aug 04 '09 at 20:40
  • 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 Aug 04 '09 at 21:01
  • I am sorry don't know what I did wrong previously but this in fact does work. Thank you for your patience with me! – Jon Weinraub Mar 17 '14 at 16:48
1

This shows you a bit more info...

DECLARE @columnName as varchar(100)
SET @columnName = 'ColumnName'

SELECT t.name AS Table, c.name AS Column,
ty.name AS Type, c.max_length AS Length, c.precision AS Precision
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE c.name LIKE @columnName
ORDER BY t.name, c.name

Hope it helps!

Mauro Bilotti
  • 5,628
  • 4
  • 44
  • 65