User-Defined Dependency Tracking

dhathewa

I don't think this is redundant, I checked around the forum and help topics and found no info on this item.

Some of our stored procedures do EXECs on strings to manipulate data. There are usually db objects referenced by these EXECs. Dependency Tracker doesn't track these "invisible" dependencies but we do need to track them.

Is it possible to manually add a "User-Defined" dependency to an object? Then, this item's dependencies could be tracked and the dependencies of items dependent on those items could be automatically added.

David Atkinson

Dynamic SQL isn't supported in SQL Dependency Tracker, and there's no way to add a user-defined dependency.

Have you considered using SQL Search to find these types of dependencies?

David Atkinson
Red Gate Software

dhathewa

I'm unfamiliar with SQL Search but searching and writing down dependencies... well, I could do almost that well with a couple of queries against sysdepends and a spreadsheet and then searching and dressing my spreadsheet up with manually added entries, no?

Or I could just do the basic dependencies into a spreadsheet, search for EXECs and send that list of stored procs offshore to get a list of further dependencies at $15/hour.

OK... I get that there's no feature in the product to do this, which is a huge disappointment, but there are certainly other dbas out there that have dynamic SQL. "Have you considered using SQL Search?" is a disappointing answer.

David Atkinson

Thank you for your feedback. I'm sorry it wasn't the answer you were looking for, but the fact is that this isn't a current capability of the tool, and I would be lying if I said it was coming soon.

Please don't dismiss SQL Search as an option just yet. There's really little reason not to give it a go given that it's a free tool.

http://www.red-gate.com/products/SQL_Search/

Kind regards,

David

dhathewa

OK... let's try a workaround. For each table, X, that may be referenced by dynamic SQL, I include something like this in the body of the proc:

declare @testfail int
set @testfail = 0
if @testfail = 1 select top 1 * from X where 0 = 1

It seems to provide the dependency tracking information I need but it won't ever return anything and doesn't seem to change the way the proc runs.

Does anyone think there is a disadvantage to this that I've overlooked? There's probably a performance hit but when compared to the performance of assembling and executing a string, I doubt that it's a big deal.