Tuesday, December 18, 2012

A really quick solution that I always have to look up.
Every once in a while I need to find some table in a database, I don't know the name of it but I have an idea of how one of the columns is called.
For example - if I have a ZenCart database and I want to know where tracking numbers are stored I can guess that the column name might contain the string track in it.
So for MySQL I can run a simple query:

SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
FROM
information_schema.columns
WHERE information_schema.columns.COLUMN_NAME LIKE '%track%'

It's that simple.

What about SQL server:SELECTcols.nameascol_name,tbls.nameAStbl_nameFROMsys.all_columnscolsINNERJOINsys.all_objectstblsONcols.object_id=tbls.object_idANDtbls.type='u'WHEREcols.nameLIKE'%track%'
That's it!

Digg

Limitation of Liability for using this code

In no event will the publisher of this site or writer of this blog be liable for any damages, including loss of data, lost profits, cost of cover, or other special, incidental, consequential, direct or indirect damages arising from the software or the use thereof, however caused and on any theory of liability. This limitation will apply even if the publisher of this site or writer of this blog has been advised of the possibility of such damage. You acknowledge that this is a reasonable allocation of risk.