Script to Locate A Column Name in the Entire Database

Happy 2014.

Hope you will have a productive software career with some advancements too.

This is my first post of 2014. As always I have been doing, I will continue to write "The Answers to Questions Experts Don't Know How To Answer." And also these are notes to myself as I tend to forget I have done in the past unless I document them somewhere.

Issue:

I had to figure out someone else's database work as I cannot ask this "someone" a question. I am sure you may be brought into a gig that would require figuring out other people's work without asking them the questions (often you cannot.)

In this case I needed to figure out in every table and view of the SQL Server database that contained the word "Price." Then reconstruct their relationship.

Of all the answers I found the following query worked the best for me so give this a try.

SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name,
 c.name AS column_name
 FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Price%'
ORDER BY schema_name, table_name;