Tuesday, December 18, 2012

Looking for columns in SQL databases

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.name as col_name, tbls.name AS tbl_name
FROM sys.all_columns cols


INNER JOIN sys.all_objects tbls ON cols.object_id = tbls.object_id AND tbls.type='u'

WHERE  cols.name LIKE '%track%'

That's it!