GEEK: how to determine which tables have a given field?

[archerships]

Suppose I have a database with a bunch of tables in it. Some of the tables have a stockkey field, but some don’t. Is there a single query that I could run that would display a list of the tables that have a stockkey field? I could write a script that iterated through all of the tables, running a “show table” against each one, but it seems like there might be a way to do it in sql alone.

Original: craschworks - comments

Comments

[fishsupreme]

You need to use the meta tables. However, I can't give you a specific query, as the meta tables differ by implementation.

For instance, on Microsoft SQL Server, there is a table called dbo.syscolumns that contains a list of metadata about the columns in every table in the system. Oracle and MySQL have similar tables, but I don't know what they're called or what their schema is, as there is no standard for an RDBMS data dictionary.

[crasch.livejournal.com]

Thanks! Very helpful. I'm using FrontBase on Mac OS X. I'll see what the docs say about meta tables.

[blueadept.livejournal.com]

A lot of DBs use the schema INFORMATION_SCHEMA for metadata. Looks like FrontBase is one of those. That might help with the search keywords...

[flw.livejournal.com]

How about HasField()?

I'm just making it up. Just guessing. I try yelling at the monitor. It hasn't produced results yet, but in the movies it almost always results in the computer springing into life and becoming the main character's best friend.

Also: try pounding your fists on either side of the keyboard. It won't make things work, but it might cause coffee to spill on a bunch of wires... and that will again cause the computer to take over every electric appliance in the house and randomly murder people.

It's worth a shot.

Also: clean the gunk out of the mouse.

Unplug it and plug it back in.

[other.livejournal.com]

Or listen to The Rolling Stones. You might just find that song you are listening to gives you the key (primary or otherwise) you are looking for.