March 24, 2008 at 7:55 pm
Hi all,
I am new to this forum and SQl server. I am trying to compare two databases and trying to find out the effective tool to do this. I found this tool apexsqldiff, is there any other better tool to compare schemas?
Not sure if I can do with SSMS
THanks much!
March 24, 2008 at 8:06 pm
SQL Compare by REDGATE is very useful.
http://www.red-gate.com/products/SQL_Compare/index.htm?gclid=CJmSspWWp5ICFQx7PAodrDVPRA
March 24, 2008 at 8:34 pm
I agree about SQLCompare by RedGate...
However, you have to ask why you want to do this. I find the typical application is to find the differences between a Development and a Production database and that some ill-conceived notion of promoting the differences is somehow better than a true configuration management effort. Do what you want, but I've seen too many Production databases get really screwed up by promoting differences.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 24, 2008 at 9:10 pm
I work for Red Gate and their tool has great reviews. Apex's tool is good as well, download both, pick the one you like.
Red Gate has Data Compare as well to compare data if you need that.
And as Jeff mentioned, think about what you're really trying to do and be careful about synchronizing changes.
March 25, 2008 at 10:39 pm
hi
i have created my own custom tools for database comparision as well as table comparision(only tables data).
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CompareDatabases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CompareDatabases]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[CompareDatabases](
@servername1 VARCHAR(50),
@DBName1 VARCHAR(50),
@Uid1 VARCHAR(50),
@Pwd1 VARCHAR(50),
@ServerName2 VARCHAR(50),
@DBName2 VARCHAR(50),
@Uid2 VARCHAR(50),
@Pwd2 VARCHAR(50))
/*
-- ServerName1 is first server name.
-- ServerName2 is second server name.
-- DBName1 is first database name.
-- DBName2 is the second database name.
-- TableName1, TableName2 are the tables or views to compare.
-- @Uid1,@Pwd1,@Uid2,@Pwd2 are the user names and passwords of corresponding servers.
-- EXEC CompareDatabases @servername1,@DBName1,@Uid1,@Pwd1,@ServerName2,@DBName2,@Uid2,@Pwd2
*/
AS
BEGIN
DECLARE @ColumnName VARCHAR(5000)
DECLARE @ColumnList VARCHAR(8000)
DECLARE @TempQuery VARCHAR(8000)
DECLARE @TempNamesQuery VARCHAR(8000)
DECLARE @TableName SYSNAME
DECLARE @Count INT
DECLARE @i INT
DECLARE @NoOfRecords INT
DECLARE @WhereClause1 VARCHAR(1000)
DECLARE @WhereClause2 VARCHAR(1000)
SET @NoOfRecords = 0
SET @WhereClause1 = '1=1'
SET @WhereClause2 = '1=1'
DELETE FROM TableStatus
IF NOT EXISTS (SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '#TempTableNames%')
BEGIN
CREATE TABLE [#TempTableNames] ([NUM] [INT] IDENTITY(1,1),[NAME][VARCHAR](500))
END
ELSE
BEGIN
DROP TABLE [#TempTableNames]
END
SET @TempNamesQuery = 'INSERT INTO #TempTableNames SELECT DISTINCT SYSOBJECTS.NAME FROM SYSOBJECTS,SYSCOLUMNS WHERE SYSOBJECTS.ID = SYSCOLUMNS.ID AND SYSOBJECTS.XTYPE = ''U'''
EXEC(@TempNamesQuery)
DECLARE tnames_cursor CURSOR
FOR
SELECT [NAME] FROM #TempTableNames
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @TableName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF NOT EXISTS (SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '#TempColumnsTable%')
CREATE TABLE [#TempColumnsTable] ([NUM] [INT] IDENTITY(1,1),[NAME] [VARCHAR] (500))
SET @TempQuery = 'INSERT INTO #TempColumnsTable(NAME) SELECT NAME FROM '+ @DBName1 + '..SYSCOLUMNS WHERE ID = OBJECT_Id(''' + @TableName + ''') AND COLUMNPROPERTY( OBJECT_ID(''' + @TableName + '''),NAME,''IsIdentity'') <> 1 ORDER BY ColId ASC'
EXEC(@TempQuery)
SET @columnlist = ''
SELECT @Count = COUNT(*) FROM #TempColumnsTable
SET @i = 1
SELECT @ColumnName = [NAME] FROM #TempColumnsTable WHERE NUM = 1
WHILE(@i<=@Count)
BEGIN
IF @columnlist = ''
SET @columnlist = @ColumnName
ELSE
SET @columnlist = @columnlist + ',' + @ColumnName
SET @i = @i + 1
SELECT @ColumnName = [NAME] FROM #TempColumnsTable WHERE NUM = @i
END
IF (@tablename != 'TableStatus')
BEGIN
EXEC CompareDatabaseTables @servername1,@DBName1,@Uid1,@Pwd1,@TableName,@columnlist,@WhereClause1,@ServerName2,@DBName2,@Uid2,@Pwd2,@TableName,@columnlist,@WhereClause2
SELECT @NoOfRecords = COUNT(*) FROM TempData
IF @NoOfRecords != 0
INSERT INTO TableStatus VALUES(@TableName,'FAILED',GETDATE())
ELSE
INSERT INTO TableStatus VALUES(@TableName,'PASSED',GETDATE())
END
DELETE FROM #TempColumnsTable
DBCC CHECKIDENT ('#TempColumnsTable', reseed, 0)
FETCH NEXT FROM tnames_cursor INTO @TableName
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
SELECT * FROM TableStatus
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------------------------------------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CompareDatabaseTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CompareDatabaseTables]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[CompareDatabaseTables](
@servername1 VARCHAR(50),
@DBName1 VARCHAR(50),
@Uid1 VARCHAR(50),
@Pwd1 VARCHAR(50),
@TableName1 VARCHAR(50),
@Table1ColumnList VARCHAR(1000) = '',
@WhereClause1 VARCHAR(1000) = '',
@ServerName2 VARCHAR(50),
@DBName2 VARCHAR(50),
@Uid2 VARCHAR(50),
@Pwd2 VARCHAR(50),
@TableName2 VARCHAR(50),
@Table2ColumnList VARCHAR(1000) = '',
@WhereClause2 VARCHAR(1000) = '')
/*
-- ServerName1 is first server name.
-- ServerName2 is second server name.
-- DBName1 is first database name.
-- DBName2 is the second database name.
-- TableName1, TableName2 are the tables or views to compare.
-- Table1ColumnList and Table2ColumnList is the list of columns to compare from TableName1 with TableName2.
-- List them as comma-separated columns.
-- If Table2ColumnList is not specified, it is assumed to be the same
-- as Table1ColumnList. Otherwise, list the columns of TableName2 in
-- the same order as the columns in TableName1 that you wish to compare.
-- WhereClause1 should contain the conditions to filter the records.
-- If whereClause1 is not specified then it is assumed as blank.
-- whereClause2 should contain the conditions to filter the records.
-- If whereClause2 is not specified then it is assumed as blank.
-- The result is all rows from either table that do NOT match
-- the other table in all columns specified, along with which table that row is from.
-- ServerNames,User Identifications and Passwords should be in single quote.
-- EXEC CompareDatabaseTables @servername1,@DBName1,@Uid1,@Pwd1,@TableName1,@Table1ColumnList,@WhereClause1,@ServerName2,@DBName2,@Uid2,@Pwd2,@TableName2,@Table2ColumnList,@WhereClause2
*/
AS
BEGIN
DECLARE @sqlquery VARCHAR(8000)
DECLARE @SqlQuery1 VARCHAR(8000)
DECLARE @SqlQuery2 VARCHAR(8000)
DECLARE @SqlQuery3 VARCHAR(8000)
DECLARE @SqlQuery4 VARCHAR(8000)
DECLARE @SqlQuery5 VARCHAR(8000)
DECLARE @SqlQuery6 VARCHAR(8000)
DECLARE @SqlQuery7 VARCHAR(8000)
DECLARE @SqlQuery8 VARCHAR(8000)
DECLARE @Server_TableName1 VARCHAR(5000)
DECLARE @Server_TableName2 VARCHAR(5000)
DECLARE @Column VARCHAR(5000)
DECLARE @ColumnList VARCHAR(8000)
DECLARE @TempQuery VARCHAR(8000)
IF @Table2ColumnList = '' SET @Table2ColumnList = @Table1ColumnList
IF @Table1ColumnList = '' SET @Table1ColumnList = @Table2ColumnList
IF @WhereClause1 = '' SET @WhereClause1 = '1=1'
IF @WhereClause2 = '' SET @WhereClause2 = '1=1'
SET @Server_TableName1 = REPLACE(@ServerName1,'''','') + '.' + @TableName1
SET @Server_TableName2 = REPLACE(@ServerName2,'''','') + '.' + @TableName2
SET @SqlQuery2 = ' UNION ALL '
SET @SqlQuery6 = ' HAVING COUNT(*) = 1'
SET @SqlQuery7 = 'SELECT * INTO TempData FROM ('
SET @SqlQuery8 = ') AS B'
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME = 'TempData')
BEGIN
DROP TABLE TempData
END
IF (@Table1ColumnList ='' AND @Table2ColumnList='')
BEGIN
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[#TempColumnsTable]') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1)
DROP TABLE [#TempColumnsTable]
ELSE
CREATE TABLE [#TempColumnsTable] ([NAME] [VARCHAR] (500) NULL)
SET @TempQuery = 'INSERT INTO #TempColumnsTable SELECT NAME FROM ' + @DBName1 + '..SYSCOLUMNS WHERE ID = OBJECT_Id(''' + @TableName1 + ''') AND COLUMNPROPERTY( OBJECT_ID(''' + @TableName1 + '''),NAME,' + '''IsIdentity'''+ ') <> 1 ORDER BY ColId ASC'
EXEC(@TempQuery)
SET @columnlist = ''
SET ROWCOUNT 1
SELECT @column = [NAME] FROM #TempColumnsTable
WHILE(@@ROWCOUNT <> 0)
BEGIN
IF @columnlist = ''
SET @columnlist = @column
ELSE
SET @columnlist = @columnlist + ',' + @column
DELETE FROM #TempColumnsTable WHERE [NAME] = @column
SELECT @column = [NAME] FROM #TempColumnsTable
END
SET ROWCOUNT 0
DROP TABLE #TempColumnsTable
SET @SqlQuery1 = 'SELECT ''' + @Server_TableName1 + ''' AS TableName, ' + @columnlist + ' FROM OPENROWSET(' +
'''SQLOLEDB''' + ',' + @servername1 + ';' + @Uid1 + ';' + @Pwd1 + ',' +
'''' + 'SELECT ' + @columnlist + ' FROM ' + @DBName1 + '..' + @TableName1 + ''''+')' + ' WHERE ' + @WhereClause1
SET @SqlQuery3 =' SELECT ''' + @Server_TableName2 + ''' AS TableName, ' + @columnlist + ' FROM OPENROWSET(' +
'''SQLOLEDB''' + ',' + @ServerName2 + ';' + @Uid2 + ';' + @Pwd2 + ',' +
'''' + 'SELECT ' + @columnlist + ' FROM ' + @DBName2 + '..' + @TableName2 + ''''+')' + ' WHERE ' + @WhereClause2
SET @SqlQuery4 = 'SELECT MAX(TableName) as TableName,' + @columnlist + ' FROM ('
SET @SqlQuery5 = ') A GROUP BY ' + @ColumnList
END
ELSE
BEGIN
SET @SqlQuery1 = 'SELECT ''' + @Server_TableName1 + ''' AS TableName, ' + @Table1ColumnList + ' FROM OPENROWSET(' +
'''SQLOLEDB''' + ',' + @servername1 + ';' + @Uid1 + ';' + @Pwd1 + ',' +
'''' + 'SELECT ' + @Table1ColumnList + ' FROM ' + @DBName1 + '..' + @TableName1 + ''''+')' + ' WHERE ' + @WhereClause1
SET @SqlQuery3 =' SELECT ''' + @Server_TableName2 + ''' AS TableName, ' + @Table2ColumnList + ' FROM OPENROWSET(' +
'''SQLOLEDB''' + ',' + @ServerName2 + ';' + @Uid2 + ';' + @Pwd2 + ',' +
'''' + 'SELECT ' + @Table2ColumnList + ' FROM ' + @DBName2 + '..' + @TableName2 + ''''+')' + ' WHERE ' + @WhereClause2
SET @SqlQuery4 = 'SELECT MAX(TableName) as TableName,' + @Table1ColumnList + ' FROM ('
SET @SqlQuery5 = ') A GROUP BY ' + @Table1ColumnList
END
EXEC (@SqlQuery7 + @SqlQuery4 + @SqlQuery1 + @SqlQuery2 + @SqlQuery3 + @SqlQuery5 + @SqlQuery6 + @SqlQuery8)
-- SELECT * FROM TempData
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------------------------------------------------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TableStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TableStatus]
GO
CREATE TABLE [dbo].[TableStatus] (
[TableName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExcetionDate] [datetime] NULL
) ON [PRIMARY]
GO
-------------------------------------------------------------------------------------------------------------------------
njoy with this code
Mohan
March 27, 2008 at 5:13 am
For the sake of completeness, ApexSQL Diff also does data syncing. Embarcadero has ChangeManager, but this doesn't to my knowledge do data (yet). IIRC I have stumbled across another program or two over the years that does checking, but haven't found one nearly as capable as the main 3.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 27, 2008 at 7:30 am
When I evaluated such products a year or so ago it turned out to be pretty close between SQL Compare and SQL Delta. I eventually chose SQL Delta because I liked the scripting capability it had better than SQL Compare. But we had numerous inconsistencies between production databases due to a lack of proper change management prior to my arrival and I needed to generate scripts to correct that. So the tool you need may well depend on how you intend to use it.
SQL Delta worked well for us. We can now produce a change script from development and be guaranteed it will execute without error on any of our production instances.
March 27, 2008 at 7:53 am
You might want to try DB Side-By-Side (http://www.schematodoc.com). It lets you create a library of xml snapshots of a database's metadata. Run any two snapshots through the comparison utility to quickly identify schema differences. Compare two different databases, or the same database at different points in time.
March 27, 2008 at 8:46 am
Thank you all for the different suggestions. It helped me a lot!
I tried redgate(trial version) and I am happy with it. But I am going to try other methods sometime down the lane.Thanks!
March 28, 2008 at 4:25 am
The cheapest and least hassle option (no installing) to compare the structure not the data is to script both databases with identical options to non-unicode files and use WinDiff to compare the files.
April 8, 2008 at 4:48 pm
Hello all!
This is my first time posting.
I have been working with SQL Examiner and think is great and cheap.
Hope this give you another option.
Thanks everyone here I've learned a lot!!!.
Alejo.
February 14, 2009 at 11:47 am
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply