Toad World® Forums

Schema Compare - Generate Script - Changing Table can result in Dropping Table


#1

While testing the schema compare we produced a scenario where some columns were changed (i.e. data type, constraint added/modified). Regardless of what we did the “Generate Script” from the Schema compare generated a DROP TABLE statement. Followed by a CREATE TABLE statement. This is unusable/unacceptable in Production environments. I recall when I used Toad for Oracle they handled this differently. Additionally, Microsoft’s SSDT utility also handles this differently.

ANYTIME a table needs dropped, at least, the following should happen:

CREATE A TRANSACTION

Create new temp table based on source definition (include all constraints, FKs, indexes, etc)

Insert data from production table to new temp table

Drop production table

Rename new temp table to same as production table name

END TRANSACTION

If there are any issues with moving the data the script should stop within the transaction and allow the database user to modify or rollback the changes. Toad doesn’t need to account for all the various scenarios that could cause this to fail, but you could create a structure for us that we can work within without asking us to parse tens to hundreds of pages of Generate Script looking for DROP TABLE and replacing it with our own approach.

-Rudy


#2

Hi Rudy,

Thank you very much.

As I know , when dropping a table, toad will do some steps in a transaction:

Rename the table.

Create a new table.

Insert data.

Drop old table.

Example Script like below. Please kindly advise is these you expect?

SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS OFF

SET ANSI_PADDING, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, XACT_ABORT ON

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

GO

BEGIN TRANSACTION

GO

EXECUTE [sp_rename]

@objname = N’[dbo].[Id]’,

@newname = N’tmp_6ac670deae3745aba43b87d2fa292b7b’,

@objtype = ‘OBJECT’

GO

GO

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Id] (

[Column_2] int IDENTITY(1, 1)

) ON [testPartition] ([Column_2])

WITH(DATA_COMPRESSION = NONE)

GO

GO

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

CREATE STATISTICS [_WA_Sys_00000001_531856C7]

ON [dbo].[Id] ([Column_2])

GO

GO

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

SET IDENTITY_INSERT [dbo].[Id] ON

GO

INSERT INTO [dbo].[Id] (

[Column_2])

SELECT

[Column_2]

FROM [dbo].[tmp_6ac670deae3745aba43b87d2fa292b7b]

GO

GO

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

SET IDENTITY_INSERT [dbo].[Id] OFF

GO

DROP TABLE [dbo].[tmp_6ac670deae3745aba43b87d2fa292b7b]

GO

wom

GO

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

IF @@TRANCOUNT>0 COMMIT TRANSACTION

GO

SET NOEXEC OFF


#3

We’re missing context of what changed under this scenario, but assuming a pure vanilla scenario here, I can report that this looks appropriate.

Also, special care should be made here when considering the order of operations. For example, there may be a column rename AND a datatype change. It would be beneficial to perform the column rename BEFORE this move script is executed.