Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2011-02-15
Medium Priority
Last Modified: 2012-05-11
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.

Question by:ChrisEGP
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 59

Assisted Solution

HainKurt earned 600 total points
ID: 34899669
try this

select * from sys.all_sql_modules m, sys.all_objects o
where m.object_id=o.object_id
and m.definition like '%Questions%'
LVL 50

Expert Comment

ID: 34899677
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...
LVL 59

Assisted Solution

HainKurt earned 600 total points
ID: 34899693
something like this:

declare @myTable varchar(100) = 'myTable';
declare @myColumn varchar(100) = 'myColumn';

select * from sys.all_sql_modules m, sys.all_objects o
where m.object_id=o.object_id
and m.definition like '%'+@myTable+'%'
and m.definition like '%'+@myColumn+'%'
LVL 70

Accepted Solution

Éric Moreau earned 400 total points
ID: 34900312

Author Closing Comment

ID: 34934618
@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!

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question