Technical Article

Find All Code with Rollback Tran but No Begin Tran

,

This simple script identifies all objects which have a rollback transaction statement without a requisite begin transaction statement. If a rollback statement is issued without a begin statement, then the code will generate an error. Triggers are not included in the list since they can have a rollback without a begin.

This script can be easily modified to be used for any other code search where something exists in the code but another thing doesn't. With other modifications, it can be used for other purposes, such as finding all tables which are not being acessed in any other piece of code.

I hope this helps!

select 
so.name,
so.type
from sysobjects so with (nolock)
where
so.type <> 'TR'-- Technically, triggers are allowed to have rollbacks w/o begins
and exists (-- all objects with a rollback statement
select *
from syscomments sc with (nolock)
where 
sc.text like '%rollback%'
and so.id = sc.id
)
and not exists (-- all objects without a begin transaction statement
select *
from syscomments sc with (nolock)
where 
sc.text like '%begin tran%'
and so.id = sc.id
)
order by so.name

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating