Add procedure name to tasks, control procs and triggers for easier troubleshooting

When an application has a lot of triggers, nested procedures or tasks within tasks troubleshooting can become rather tricky. Luckily SQL provides some tools for this, namely the profiler and extended eventes. However, it is still rather time consuming to figure out which triggers and procedures are running during execution of a task or update statement.

I know there is already the template naming in the procedures, however this does not give you the task name or the type of object it is and therefor is not very useful during troubleshooting a task that gives an error.

Therefor it would be very helpful to add the stored procedure or trigger name directly after set nocount on and also show the type (trigger or stored procedure).



something like this:



-- Do not count affected rows for performance

set nocount on

-- type: trigger

-- name: player_position_td



Then during troubleshooting you can capture all nocount on events, "set nocount on" and comments directly afterwards can be captured by the profiler. This way you can easily see the path SQL is taking during execution of for example a task.



If needed i can show an example of this and how it will improve troubleshooting.