How many times a day do you need to find out where a piece of data is stored in a table but don’t have a clue where to look?  If you’ve been working on the same database for the past three years you probably know where everything is.

But if you work on multiple schemas, are a consultant and work on all kinds of data structures you did not design, or just providing backup database support for a team member on vacation, you know what I’m talking about. 

Rather than hunting and pecking through the SSMS object tree you can narrow the scope of your search.

The following query will search the entire AdventureWorks database for any column that looks like ‘address’.

USE AdventureWorks;
GO

SELECT    OBJECT_NAME(id),
        name
FROM    syscolumns
WHERE    name like '%address%'
ORDER BY 1 ASC;

Here’s what we get for the output:

You can also see it returns 32 rows that includes tables and views, greatly narrowing your search.  With a tiny bit of insight you can probably narrow your search to 2 or 3 tables.

It’s a simple little query but one that always saves me a bunch of time. 

Happy SQLing!