How to Roll Back Create Table and Alter Table Statements in Major SQL Databases

Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?

http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis provides an overview of this issue from PostgreSQL's perspective.

Is DDL transactional according to this document?

PostgreSQL - yes
MySQL - no; DDL causes an implicit commit
Oracle Database 11g Release 2 and above - by default, no, but an alternative called edition-based redefinition exists
Older versions of Oracle - no; DDL causes an implicit commit
SQL Server - yes
Sybase Adaptive Server - yes
DB2 - yes
Informix - yes
Firebird (Interbase) - yes

SQLite also appears to have transactional DDL as well. I was able to ROLLBACK a CREATE TABLE statement in SQLite. Its CREATE TABLE documentation does not mention any special transactional 'gotchas'.

Can i undo ALTER table in - MySQL?

No, you can't roll back ALTER TABLE statements.

https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html

Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.

(emphasis mine)

As Chris Peters comments above, you can do the reverse ALTER TABLE operation, and then clean up your data using UPDATE statements.

Snowflake - want to revert back to previous version of table before I replaced it

The solution has already been provided by Francesco & Nick but here is a little more detail in the form of an example... Bascially you need to rename the current table and undrop the old one. It's like a fist-in-last-out stack where the current table sits at the top and you need to pop it off (rename it) to get to the previous one:

I've got some comments in the SQL for you to follow though:

-- Create the initial table
create table dont_drop_me(col1 varchar, col2 varchar)
;

-- Insert 4 rows of some sample data
insert overwrite into dont_drop_me values
('col1_row1', 'col2_row1'),
('col1_row2', 'col2_row2'),
('col1_row3', 'col2_row3'),
('col1_row4', 'col2_row4')
;

-- Replace the table (by accident?). New table has an extra column to prove changes.
create or replace table dont_drop_me(col1 varchar, col2 varchar, col3 varchar);

-- Now the new table contains no data but has 1 extra column
select * from dont_drop_me;
-- +----+----+----+
-- |COL1|COL2|COL3|
-- +----+----+----+

-- View what tables are on the history. The top row is the current table, the second
-- row is the first table that we replaced
show tables history like 'dont_drop_me';
-- +------------------------------+------------+-------------+-----------+-----+-------+----------+----+-----+--------+--------------+------------------------------+--------------------+---------------+-------------------+----------------------------+-------------------------+-----------+
-- |created_on                    |name        |database_name|schema_name|kind |comment|cluster_by|rows|bytes|owner   |retention_time|dropped_on                    |automatic_clustering|change_tracking|search_optimization|search_optimization_progress|search_optimization_bytes|is_external|
-- +------------------------------+------------+-------------+-----------+-----+-------+----------+----+-----+--------+--------------+------------------------------+--------------------+---------------+-------------------+----------------------------+-------------------------+-----------+
-- |2021-02-26 04:56:23.948 -08:00|DONT_DROP_ME|SIMON_DB     |PUBLIC     |TABLE|       |          |0   |0    |SYSADMIN|1             |NULL                          |OFF                 |OFF            |OFF                |NULL                        |NULL                     |N          |
-- |2021-02-26 04:56:19.610 -08:00|DONT_DROP_ME|SIMON_DB     |PUBLIC     |TABLE|       |          |4   |1024 |SYSADMIN|1             |2021-02-26 04:56:24.073 -08:00|OFF                 |OFF            |OFF                |NULL                        |NULL                     |N          |
-- +------------------------------+------------+-------------+-----------+-----+-------+----------+----+-----+--------+--------------+------------------------------+--------------------+---------------+-------------------+----------------------------+-------------------------+-----------+

-- We need to rename existing object to move it off the top of the stack so that we can recover the first one
alter table dont_drop_me rename to renamed_dont_drop_me;

-- Now view what tables are in the history again. You can see that the first table created has moved to the top of the stack
show tables history like 'dont_drop_me';
-- +------------------------------+------------+-------------+-----------+-----+-------+----------+----+-----+--------+--------------+------------------------------+--------------------+---------------+-------------------+----------------------------+-------------------------+-----------+
-- |created_on                    |name        |database_name|schema_name|kind |comment|cluster_by|rows|bytes|owner   |retention_time|dropped_on                    |automatic_clustering|change_tracking|search_optimization|search_optimization_progress|search_optimization_bytes|is_external|
-- +------------------------------+------------+-------------+-----------+-----+-------+----------+----+-----+--------+--------------+------------------------------+--------------------+---------------+-------------------+----------------------------+-------------------------+-----------+
-- |2021-02-26 04:56:19.610 -08:00|DONT_DROP_ME|SIMON_DB     |PUBLIC     |TABLE|       |          |4   |1024 |SYSADMIN|1             |2021-02-26 04:56:24.073 -08:00|OFF                 |OFF            |OFF                |NULL                        |NULL                     |N          |
-- +------------------------------+------------+-------------+-----------+-----+-------+----------+----+-----+--------+--------------+------------------------------+--------------------+---------------+-------------------+----------------------------+-------------------------+-----------+

-- Now undrop the table and prove that it is the old one (the one with 4 rows and 2 columns)
undrop table dont_drop_me;
select * from dont_drop_me;
-- +---------+---------+
-- |COL1     |COL2     |
-- +---------+---------+
-- |col1_row1|col2_row1|
-- |col1_row2|col2_row2|
-- |col1_row3|col2_row3|
-- |col1_row4|col2_row4|
-- +---------+---------+

Is it possible to rollback create_all in sqlalchemy?

Use Postgresql or SQL Server. MySQL, Oracle do not support transactional DDL. Recent versions of SQLite do appear to support transactional DDL. The Python sqlite3 driver however does not.
simple recipe:
```
with engine.begin() as conn:
    metadata.create_all(conn)
```
if you raise an exception inside the "with:", the transaction will be rolled back.

want to see it yourself, OK:

from sqlalchemy import inspect   # need to be running 0.8 for this

with engine.connect() as conn:
   trans = conn.begin()
   metadata.create_all(conn)
   inspector = inspect(conn)
   table_names = inspector.get_table_names()
   trans.rollback()
   inspector = inspect(conn)
   rolled_back_table_names = inspector.get_table_names()

to gain an understanding of transactional scope, I recommend you read http://docs.sqlalchemy.org/en/latest/core/connections.html

How to perform ALTER DATABASE within a TRANSACTION In SqlServer

So, we're trying to arrange for a database to be returned to multi_user mode if our connection drops. Here's one way that works, but is as ugly as sin.

First, we set things up appropriately:

create database RevertTest
go
use master
go
create table RevertLock (L int not null)
go
declare @rc int
declare @job_id uniqueidentifier

exec @rc = msdb..sp_add_job @job_name='RevertSingleUser',
                            @description='Revert the RevertTest database to multi_user mode',
                            @delete_level=3,
                            @job_id = @job_id OUTPUT

if @rc != 0 goto Failed

exec @rc = msdb..sp_add_jobstep @job_id = @job_id,
                                @step_name = 'Wait to revert',
                                @command = '
WHILE EXISTS (SELECT * FROM RevertLock)
    WAITFOR DELAY ''00:00:01''

ALTER DATABASE RevertTest set multi_user

DROP TABLE RevertLock'
if @rc != 0 goto Failed

declare @nowish datetime
declare @StartDate int
declare @StartTime int

set @nowish = DATEADD(minute,30,GETDATE())
select @StartDate = DATEPART(year,@nowish) * 10000 + DATEPART(month,@nowish) * 100 + DATEPART(day,@nowish),
        @StartTime = DATEPART(hour,@nowish) * 10000 + DATEPART(minute,@nowish) * 100 + DATEPART(second,@nowish)

exec @rc = msdb..sp_add_jobschedule @job_id = @job_id,
                                    @name='Failsafe',
                                    @freq_type=1,
                                    @active_start_date = @StartDate,
                                    @active_start_time = @StartTime
if @rc != 0 goto Failed

exec @rc = msdb..sp_add_jobserver @job_id = @job_id
if @rc != 0 goto Failed

print 'Good to go!'
goto Fin
Failed:
print 'No good - couldn''t establish rollback plan'
Fin:

Basically, we create a job that tidies up after us. We schedule the job to start running in half an hours time, but that's just to protect us from a small race.

We now run our actual script to do the work that we want it to:

use RevertTest
go
alter database RevertTest set single_user with rollback immediate
go
begin transaction
go
insert into master..RevertLock(L) values (1)
go
exec msdb..sp_start_job @job_name='RevertSingleUser'
go
WAITFOR DELAY '01:00:00'

If you run this script, you'll be able to observe that the database has entered single-user mode - the WAITFOR DELAY at the end is just to simulate us "doing work" - whatever it is that you want to do within the database whilst it's in single-user mode. If you stop this query running and disconnect this query window, within a second you should see that the database has returned to multi_user mode.

To finish your script successfully, just make the last task (before COMMIT) to be to delete from the RevertLock table. Just as with the disconnection, the revert job¹ will take care of switching the DB back into multi_user and then cleaning up after itself.

¹The job is actually slightly deceptive. It won't actually sit looping and checking the table in master - since your transaction has an exclusive lock on it due to the INSERT. It instead sits and patiently waits to acquire a suitable lock, which only happens when your transaction commits or rolls back.

How to roll back DDL statements such CREATE TABLE and DROP TABLE while using JDBC driver with MySQL DBMS?

As Paul Spiegel and Arnaud said, DDL statements cannot be rolled back in MySQL. This is true even for MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

For better or worse, DDL statements commit any pending transactions, if they are local and cannot be used in XA transactions (because that would cause one participant to commit data without the transaction coordinator issuing the command to do so).

SQL Server 'Invalid column name' in transaction

SQL Server tries to compile all statements in the batch. If the table doesn't exist compilation of the statement is deferred but there is no deferred compilation for missing columns.

You can use

BEGIN TRY
BEGIN TRANSACTION
  ALTER TABLE [Items] ADD tagID [uniqueidentifier] NULL
  EXEC('
  MERGE INTO
    Items AS target
  USING
    Tags AS t ON t.tag = target.tag
  WHEN MATCHED THEN
    UPDATE SET target.tagID = t.id;
 ')
COMMIT
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION
END CATCH
GO

To push the usage of the column into a child batch compiled after the column is created. It still belongs to the same transaction opened in the parent scope.