You can use certain data definition language commands in transactions by setting the ddl in tran database option to true. If ddl in tran is true in a particular database, you can issue commands such as create table, grant, and alter table inside transactions in that database. If ddl in tran is true in the model database, you can issue the commands inside transactions in all databases created after ddl in tran was set to true in model. To check the current settings of ddl in tran, use sp_helpdb.
WARNING! Be careful when using data definition commands. The only scenario in which using data definition language commands inside transactions is justified is in create schema. Data definition language commands hold locks on system tables such as sysobjects. If you use data definition language commands inside transactions, keep the transactions short.
Avoid using data definition language commands on tempdb within transactions; doing so can slow performance to a halt. Always leave ddl in tran set to false in tempdb.
To set ddl in tran to true, enter:
sp_dboption database_name,"ddl in tran", true
Then execute the checkpoint command in that database.
The first parameter specifies the name of the database in which to set the option. You must be using the master database to execute sp_dboption. Any user can execute sp_dboption with no parameters to display the current option settings. To set options, however, you must be either a System Administrator or the Database Owner.
These commands are allowed inside a transaction only if the ddl in tran option to sp_dboption is set to true:
create default
create index
create procedure
create rule
create schema
create table
create trigger
create view
drop default
drop index
drop procedure
drop rule
drop table
drop trigger
drop view
grant
revoke
You cannot use system procedures that change the master database or create temporary tables inside transactions.
Do not use the following commands inside a transaction:
alter database
alter table...partition
alter table...unpartition
create database
disk init
dump database
dump transaction
drop database
load transaction
load database
reconfigure
select into
update statistics
truncate table
