Where in the seven levels of Microsoft is that bloody column!? Posted by Mischa S.

Back

Date: May 10, 2012

Today is the day when I had to, yet again, Google something that I'm sure I've Googled a dozen times.  I was trying to find a column with the name 'email' somewhere in the great labyrinth that is our CRM/ERP/OMG system.  For some reason, the creators of this much beloved beast of a database, didn't put an email field in their contact table.  This is probably so you can assign multiple emails to a contact...yeah, I know, that's probably a good thing, but it makes my job harder because I have no idea where those emails are stored.

So, GET TO THE POINT already right?  OK, here it is:

SELECT SO.name, SC.name 
FROM sysobjects AS SO
INNER JOIN syscolumns AS SC ON SO.id = SC.id
WHERE 1=1
	AND SO.type = 'U' 
	AND SC.name LIKE '%email%'
ORDER BY SC.name

You join the sysobjects table to the syscolumns table and then search only sysobjects that are of the type "U" (user) and then find only columns that contain the word "email" in them.