views:

24

answers:

2

My current client is struggling with their release process. It's a bit complex and a little uncontrolled. I'm trying to fix the process problems, but in the meantime it would be helpful if I could put together a utility that could scan their scripts, look for dependencies, and generate the order in which those scripts need to run.

Rather than reinvent the wheel I thought that I'd try to find the code that SQL Server uses for filling sys.sql_dependencies(1) (now that it's actually pretty accurate in current versions).

Is this code available somewhere as a hidden system stored procedure/trigger or is it some inaccessible binary code somewhere?

Just to be clear, I'm looking for the code that fills the system tables, not code that accesses them to determine dependencies. In other words, the code that runs when I do a "CREATE PROCEDURE ..."

If there's open source code or a third-party application that already has this functionality then I'd be interested in that as well.



(1) Ok, technically, sys.sql_dependencies is a view over sys.sysmultiobjrefs so ultimately I'm looking for the code that fills that table.

+1  A: 

I believe it's pretty inaccessible.

When sys.dependencies fails me, I tried Red-Gate's Dependency Tracker, and was very happy with it. Not open-source, but a very nice tool nonetheless.

Dave Markle
Thanks for the suggestion. Unfortunately, it looks like the Red Gate tool only works with existing SQL objects. I want to be able to look for dependencies in scripts before actually running the script(s). It seems like a tough task, so I'm hoping someone has already done the work for me :)
Tom H.
+1  A: 

Another third party tool that can scan source code files for dependencies is Apex SQL Clean

This will show you in which script an existing database object is referenced but not help with any analysis of the scripts themselves (e.g. that if a CREATE statement is in one script this should run first before another script that references it)

Martin Smith
Thanks for the link. Doesn't do quite what I want, but it's something to remember for the future.
Tom H.