Hunting for the elusive column, table, proc, ...

{

One of our clients has a Microsoft SQL Server table with 284 fields. So when I'm given a spec that references the "OrigLoanAmount" field, it's sometimes difficult to ascertain where that field lives. On some systems, especially if you've got familiarity where data are likely to be stored, you can make an educated guess. But if you're interested in some quick digging, try these queries from the Information Schema views:

-- find that elusive column
SELECT * FROM Information_Schema.Columns WHERE Column_Name LIKE '%MyColumn%'

-- list out the tables
SELECT * FROM Information_Schema.Tables

For procedures, views, and functions, I've just queried sysobjects. I learned this from a guy who was in my class once; I used to be one of the hapless Enterprise Manager users until I saw him digging effortlessly with queries like:

-- show me all stored procedures
SELECT * FROM syobjects WHERE type = 'p' AND name NOT LIKE 'dt%'

-- show me all views
SELECT * FROM sysobjects WHERE type='v'

-- show me all user defined functions
SELECT * FROM sysobjects WHERE type='FN'

... and so on.

}