Quickly Find Any Fields with This SQL Trick!

This SQL trick was taught to me by a former colleague so I can’t take credit for it. She did however encourage me to share the knowledge!

Scenario: You’ve been asked to a build a report or to query data from a new enterprise application that you’re not familiar with. The person making the request point to fields they want from his/her screen.

Problem: You quickly realize the person is showing you fields on the front-end side of the system. You “sigh” to yourself as you think about the hours you will waste digging through tables/views in the database to look for those fields…

Solution: Just use Kelly’s SQL Trick:

SELECT	c.name AS ‘ColumnName’
	,t.name AS ‘TableName’
FROM #YourDatabase#.columns c
JOIN #YourDatabase#.sys.tables t
	ON c.object_id = t.object_id
WHERE c.name LIKE ‘%demographic%’
ORDER 	BY TableName
	,ColumnName;

In the above code snippet, you essentially search all the columns in all the tables of a database for the field you’re looking for, say, “demographics”. The results will return all column names with the word “demographics” and which tables they were found in.

This also works for views. You just replace tables with views

SELECT	c.name AS ‘ColumnName’
 	,t.name AS ‘TableName’
FROM #YourDatabase#.columns c
JOIN #YourDatabase#.sys.views t
	ON c.object_id = t.object_id
WHERE c.name LIKE ‘%demographic%’
ORDER 	BY TableName
	,ColumnName;

Try it out yourself and let me know what you think!

Thanks Kelly!