question

Janie avatar image
Janie asked

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.

database-objects
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
I'm slow. Need to keep an eye out for that blue bar I guess.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Hah - that blue bar has got me a fair few times! +1 though because as you say, there are no guarantees...
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
All good points - +1
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

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

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
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.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
I agree, that's why I said 'also' !!! mmm, dynamic SQL - no way around that other than to avoid it :D
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
True, but sometimes you can't avoid it and some other times it really is the best option.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.