Can I change table structure in a transaction and then roll it back if there is an error?

Question

I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements if something goes wrong.

Is this possible, or is it only data that can be rolled back?

Answer 1

Yes, this is possible.

Most DDL statements can be rolled back in SQL Server (There are a few exceptions such as CREATE DATABASE)

Answer 2

   BEGIN TRANSACTION
      BEGIN TRY
        ALTER TABL1...
        ALTER TABLE2
        COMMIT
      END TRY
      BEGIN CATCH
         ROLLBACK
      END CATCH

Answer 3

You need to look into transactions and exception handling in T-SQL. Check out the last two examples on this page: http://msdn.microsoft.com/en-us/library/ms175976.aspx