Tuesday, October 12, 2010

Standardizing Object Names - Schema Compare

In a recent build of our schema comparison and synchronization tool xSQL Object we introduced a simple “feature” that is aimed at generating consistent statements and fix name mismatches that occur especially with objects migrated from SQL Server 2000. The idea is that regardless of what the name of the object looks like in the definition of the object in the source database when xSQL Object generates the change script it will use [owner].[object_name]. For example, you may have created a new stored procedure as CREATE PROCEDURE myStoredProcedure … , in the sync script generated by xSQL Object this statement will be CREATE PROCEDURE [owner].[myStoredProcedure].

The “problem” some of the users are running into is that after they execute the synchronization script they expect the databases to be identical however the object definitions are not going to be identical because of those discrepancies in the object name inside the definition of the object (that is only if the names in the source database are not defined as [owner].[objectname]).

While we like the idea of automatically "correcting" the object names (by the way SQL Server Management Studio does the same thing when you script an object) you (the user) may want the synchronization process to reflect exactly what the source database is – in other words you don’t want xSQL Object to standardize the names. In that case you can turn this feature off: find and open xSQL.SQLObjectCompare.UI.exe.config with Notepad and then add the following key under the <appsettings> section: <add key="PerformNameValidation" value="0">

No comments: