Where Used - SQL Server 2005

In SAP, there is an option called "Where Used" that when clicked on will show all procedures, applications, programs, tables, etc. where a selected object is used. Is there an equivalent to this in SQL Server 2005? The "show dependencies" option is not accurate and I need to know EVERYWHERE a specified object is used. Any help is greatly appreciated.

more ▼

asked Apr 16, 2010 at 10:40 AM in Default

avatar image

22 2 2 3

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

You have got a couple of options. There was a good article a couple of days ago on the main site - see here. Also, there are third party tools to do the job, the best example being Red Gate's SQL Dependency Tracker.

more ▼

answered Apr 16, 2010 at 10:50 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

I'm slow. Need to keep an eye out for that blue bar I guess.

Apr 16, 2010 at 10:52 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

There is no real guaranteed way to do this within SQL Server. There are third party tools available that will get you close to an accurate image of the dependencies, but they frequently can't account for procedures & other code that builds access to tables, views, whatever, through ad hoc mechanisms.

more ▼

answered Apr 16, 2010 at 10:51 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

Hah - that blue bar has got me a fair few times! +1 though because as you say, there are no guarantees...

Apr 16, 2010 at 11:03 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

I second Matt's suggestion for Red Gate's SQL Dependency Tracker. It is quite good. But as Grant points out nothing is absolutely accurate.

The reason for this is dynamic SQL. There is no automated way of determining the dependencies of dynamic SQL outside of run time, since the dependencies may very well change each time.

Also, at the risk of stating the obvious now, remember that the dependency trackers also cannot look at all the things outside the database that may depend on that object. Applications that rely on the database are not inherently visible to any dependency tracker, nor are stored procedures that may reside on a linked server and call the objects through the link, etc.

more ▼

answered Apr 16, 2010 at 01:28 PM

avatar image

15.6k 22 55 38

All good points - +1

Apr 16, 2010 at 05:01 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

Everyone is naming SQL Dependancy Tracker from Red Gate but there is also now a free tool called SQL Search from Red Gate that searches the text of object in a database (including comments I think) so it could bring up references to your object that SQL Dependency Tracker might miss. Download the free tool here: http://www.red-gate.com/products/SQL_Search/index.htm

more ▼

answered Apr 17, 2010 at 11:06 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

That is a good suggestion, but I would generally use that in addition to, not in place of, the Dependency Tracker. Also remember that depending on how the SP is written even that can miss Dynamic SQL and it will not help at all in telling you what outside applications are calling those objects.

Apr 17, 2010 at 06:18 PM TimothyAWiseman

I agree, that's why I said 'also' !!! mmm, dynamic SQL - no way around that other than to avoid it :D

Apr 17, 2010 at 06:22 PM Fatherjack ♦♦

True, but sometimes you can't avoid it and some other times it really is the best option.

Apr 18, 2010 at 01:54 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 16, 2010 at 10:40 AM

Seen: 1443 times

Last Updated: Apr 16, 2010 at 10:40 AM

Copyright 2018 Redgate Software. Privacy Policy