I have one database
and I create new database by copy everything from first db
I then alter some column on new database
and now I would like to compare two database
How can I do it ?
There is the manul way which I am sure you know of because like most of use we don't keep track of the changes that we make until time comes to roll the thing out.
There is a great good called RedGate SQL Compare (http://red-gate.com/products/SQL_Compare/index.htm) the tools are sort of expensive but well worth the money. Also if you are interested Microsoft is developing a product call Visual Studio for Database Developers that can be found on the MSDN site. Which esentially does the same thing and can be found on MSDN subscription download too.
|||I would write a script looking at syscolumns and sysobjects from both DB's and look where they do not match, this should get you changes to the DB.|||That is basically what Red Hat does, but it takes it a step furthor and generate the change script for you. I personally think that is worth 250.00|||
FYI, here is a simple script I'm using:
/*--Compare the differences in table schemas between 2 databases
--*/
/*--sample to call this stored procedure
exec p_comparestructure 'pubs','northwind'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_comparestructure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_comparestructure]
GO
create proc p_comparestructure
@.dbname1 varchar(250), --the name of the database to be comapred
@.dbname2 varchar(250) --the name of the database to be comapred
as
create table #tb1(TableName1 nvarchar(250),ColumnName nvarchar(250),Ordinal int,Iden bit,PrimaryKey bit,Type nvarchar(250),
Bytes int,Length int,BitsAfterDecimalPoint int,AllowNulls bit,DefaultValue nvarchar(500),ColumnDescription nvarchar(500))
create table #tb2(TableName2 nvarchar(250),ColumnName nvarchar(250),Ordinal int,Iden bit,PrimaryKey bit,Type nvarchar(250),
Bytes int,Length int,BitsAfterDecimalPoint int,AllowNulls bit,DefaultValue nvarchar(500),ColumnDescription nvarchar(500))
--Get the schema of database1
exec('insert into #tb1 SELECT
TableName=d.name,ColumnName=a.name,Ordinal=a.colid,
Iden=case when a.status=0x80 then 1 else 0 end,
PrimaryKey=case when exists(SELECT 1 FROM'+@.dbname1+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM'+@.dbname1+'..sysindexes WHERE indid in(
SELECT indid FROM'+@.dbname1+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
Type=b.name,Bytes=a.length,Length=a.prec,BitsAfterDecimalPoint=a.scale, AllowNulls=a.isnullable,
DefaultValue=isnull(e.text,''''''),ColumnDescription=isnull(g.[value],'''''')
FROM'+@.dbname1+'..syscolumns a
left join'+@.dbname1+'..systypes b on a.xtype=b.xusertype
inner join'+@.dbname1+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties''
left join'+@.dbname1+'..syscomments e on a.cdefault=e.id
left join'+@.dbname1+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--Get the schema of database2
exec('insert into #tb2 SELECT
TableName=d.name,ColumnName=a.name,Ordinal=a.colid,
Iden=case when a.status=0x80 then 1 else 0 end,
PrimaryKey=case when exists(SELECT 1 FROM'+@.dbname2+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM'+@.dbname2+'..sysindexes WHERE indid in(
SELECT indid FROM'+@.dbname2+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
Type=b.name,Bytes=a.length,Length=a.prec,BitsAfterDecimalPoint=a.scale, AllowNulls=a.isnullable,
DefaultValue=isnull(e.text,''''''),ColumnDescription=isnull(g.[value],'''''')
FROM'+@.dbname2+'..syscolumns a
left join'+@.dbname2+'..systypes b on a.xtype=b.xusertype
inner join'+@.dbname2+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties''
left join'+@.dbname2+'..syscomments e on a.cdefault=e.id
left join'+@.dbname2+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--and not exists(select 1 from #tb2 where TableName2=a.TableName1)
select Result=case when a.TableName1 is null and b.Ordinal=1 then 'Table：'+b.TableName2+' absent in'+@.dbname1
when b.TableName2 is null and a.Ordinal=1 then 'Table :'+a.TableName1+' absent in'+@.dbname2
when a.ColumnName is null and exists(select 1 from #tb1 where TableName1=b.TableName2) then @.dbname1+' ['+b.TableName2+'] doesn''t have column：'+b.ColumnName
when b.ColumnName is null and exists(select 1 from #tb2 where TableName2=a.TableName1) then @.dbname2+' ['+a.TableName1+'] doesn''t have column：'+a.ColumnName
when a.Iden<>b.Iden then 'Different identities'
when a.PrimaryKey<>b.PrimaryKey then 'Different Primary Key constraints'
when a.Type<>b.Type then 'Different column data types'
when a.Bytes<>b.Bytes then 'Bytes'
when a.Length<>b.Length then 'Different Lengths'
when a.BitsAfterDecimalPoint<>b.BitsAfterDecimalPoint then 'Different in BitsAfterDecimalPoint'
when a.AllowNulls<>b.AllowNulls then 'Different in AllowNulls options'
when a.DefaultValue<>b.DefaultValue then 'Different default values'
when a.ColumnDescription<>b.ColumnDescription then 'Different Column Descriptions'
else '' end,
*
from #tb1 a
full join #tb2 b on a.TableName1=b.TableName2 and a.ColumnName=b.ColumnName
where a.TableName1 is null or a.ColumnName is null or b.TableName2 is null or b.ColumnName is null
or a.Iden<>b.Iden or a.PrimaryKey<>b.PrimaryKey or a.Type<>b.Type
or a.Bytes<>b.Bytes or a.Length<>b.Length or a.BitsAfterDecimalPoint<>b.BitsAfterDecimalPoint
or a.AllowNulls<>b.AllowNulls or a.DefaultValue<>b.DefaultValue or a.ColumnDescription<>b.ColumnDescription
order by isnull(a.TableName1,b.TableName2),isnull(a.Ordinal,b.Ordinal)--isnull(a.ColumnName,b.ColumnName)
go
