Solved

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

Posted on 2011-02-15
5
578 Views
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.

Thanks.
0
Comment
Question by:ChrisEGP
5 Comments
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 150 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%'
0
 
LVL 50

Expert Comment

by:Lowfatspread
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...
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 150 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+'%'
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 100 total points
ID: 34900312
0
 

Author Closing Comment

by:ChrisEGP
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!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parse field in SQL View 15 97
SQL Query stumper 3 36
MSSQL: Replace text (typo) 7 31
Query / Window function ? 3 16
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

910 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now