How Do I Follow A T-SQL Transaction?

Question

Are there any programs that will allow you to follow a sql transaction through to it's end? For instance, say I've inherited a rather complex sql database with a data dictionary. The data dictionary is pretty good, but not as good as say, SQL Doc. I've taken a look at Red Gate's Dependency Tracker and, while that does a very good job of putting things together (triggers, stored procedures, tables, views, etc.), it still does nothing for following a transaction through it's various tracks.

What I'd like is software that will allow me to enter a transaction and based on everything in my db, tree it out visually to let me see what's happening during the transaction. Does that make sense or do I need to elaborate?

Edited to elaborate: While the answers below were very good, it's not exactly what I'm looking for. The front end of the sql database was built with PowerBuilder. I can use SqlSpy during frontend data input and that helps to follow transactions through, but it's all the t-sql and as you can imagine, reams of output. I'm looking for something that would do the same thing, but lay it out visually with the ability to tree out the detail if I wanted to.

Answer 1

The SQL Profiler will allow you to trace a transaction through, and see exactly what is going on. It is very flexible and allows you to be shown only the events you are interested in, but it won't show it in the tree format I think you're asking about.

The profiler is installed as part of the SQL Server Client Tools (along with Enterprise Manager and Query Analyser).

Answer 2

I guess if this way is doable. You can set up a test environment and run the transaction. Once transaction is submitted, compare the database in test environment with the prodution database.

You can use some tool to do this comparation. Like OpenDBDiff or you can look for other tool by googling.

Answer 3

I haven't used this yet, it appears to do what you're asking: apply IDE-style debugging tools to T-SQL statements. I woul be interested to see how it works out.

Answer 4

If you open a connection in Visual Studio, you can run sprocs and queries in debug mode. i.e. you can go step by step over a query with intellisense and see the values in the variables.