SQL Compare does not support DB attributes?

samjones11

(Is there another tool that does this?)

My scripts are usually like this:

USE [master]
GO

CREATE DATABASE [FOO] ON  PRIMARY 

ALTER DATABASE [FOO] SET ANSI_NULL_DEFAULT OFF 

-- About 1-2 dozen ALTER DATABASE statements 



USE [MyShipRush]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BAR]

-- etc for all the CREATE statements

I observe in SQL COMPARE that the ALTER TABLE statements are not supported.

Is there another tool / method for this?

How can I fully compare two DB's (schema and SQL DB attributes) ?

Thanks!

Brian Donahue

Thanks for your post.

SQL Compare does alter tables when whenever possible, however, there are about five circumstances that will necessitate a rebuild of the table.

SQL Compare does not put in statements to create databases, as it is designed to adjust existing rather than create new databases. SQL Packager is a deployment tool that can automatically create the databases for you.

samjones11

Thanks. I am new to the redgate kit, so am still getting adjusted...

Yesterday I found myself trying three or four tools (I have the demo), trying to figure out which one to use for a certain task...

David Atkinson

We have had requests for SQL Compare to allow the deployment to a new database, but it's not in the current release, unfortunately.

With regards to databas-level attributes, we do not compare these, but we are considering allowing these to be set by allowing user-specifiable pre- and post-deployment scripts, associated with a project. The pre script would run after the CREATE DATABASE but before the synchronization. The post script would run after the synchronization. Would this solve your problem?

An alternative approach for us would be to allow SQL Compare to compare these attributes in the same way as it does for schema objects.

David Atkinson
Red Gate Software

samjones11

First: Context

I am brand new to redgate tools.

I am an old (ancient) SQL Server hand (since v4.21....).


Response:

I envision the first screen of SQL Compare (where you set source & target) to have some addl options:

( ) Compare objects only (tables, indexes, sprocs, etc)

( ) Compare objects and db attributes (above + db properties)

[ ] Skip the nice comparison screen and just give me the script


ADDITIONALLY, I would like the script to include verbose comments. For example, say you have an index named FOO in both db's, but the index definition differs.

I would like AT THE TOP of the script a comment block to effect:

** Existing objects that are being changed by this script
<list>

** New objects being created by this script
<list>


Then, next to the DROP/CREATE INDEX (or ALTER, or however you implement it), a comment:

** This index is being redefined based on the definition in the source <source name> as of <date/time>


That is my feedback!