Link to home
Start Free TrialLog in
Avatar of ChrisEGP
ChrisEGPFlag for United States of America

asked on

Search T-SQL (views, stored procedures, triggers) for use of a column name.

I'm sure this problem could be solved by documenting things better but...

We've had problems where we changed what a user-defined field was used for or the range of allowed values for a field and we forgot about someplace we were using that field (view, SP, etc.) that would break due to the change.

I'm looking for a way to search through all views, stored procedures, triggers, functions, etc in a given table for use of a particular field.  Searching the field name would suffice, but searching for a field from a particular table would be better.

Thanks.
SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lowfatspread
this information is not available to you from sql server, as you acknowledge its basically down to you to document
your applications/data structures...


you can write search sql to look into your procedure/trigger/function logic  but nothing will assist you in parsing the sql
statements tat you find within so you will not be able to consistently determine where columns or even tables
are referenced... (Tables etc could be aliased in so many ways with many layers present...)


there are

Information_schema.columns /Tables/routines  tables which you can search...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ChrisEGP

ASKER

@HainKurt gets points for being the simplest way to get the results.
@emoreau has the best answer because the results from RedGate's SQL Search are much easier to pick through.  It looks like I'll be using this goodie a lot.

Thanks all!