Db Comparer

Ottimo tool free per fare confronti tra le strutture di database sql server

http://dbcomparer.com

in alternativa potete usare questa query

declare @db1 varchar(25), @db2 varchar(25), @sql1 varchar(5000), @sql2 varchar(5000), @sql3 varchar(5000)
    set @db1 = 'db1name'
    set @db2 = 'db2name'
    set @sql1 = '(select O1.name Tabella, C1.colId, C1.name Campo, T1.name Tipo, C1.length Lunghezza, 
                        C1.xPrec Precisione, C1.xScale Scale, C1.isNullable 
                    from ' + @db1 + '.dbo.syscolumns C1 
                    join ' + @db1 + '.dbo.sysobjects O1 on C1.id = O1.id 
                    join ' + @db1 + '.dbo.systypes T1 on C1.xtype = T1.xusertype) DB1 '
    set @sql2 = '(select O1.name Tabella, C1.ColId, C1.name Campo, T1.name Tipo, C1.length Lunghezza, 
                        C1.xPrec Precisione, C1.xScale Scale, C1.isNullable 
                    from ' + @db2 + '.dbo.syscolumns C1 
                    join ' + @db2 + '.dbo.sysobjects O1 on C1.id = O1.id 
                    join ' + @db2 + '.dbo.systypes T1 on C1.xtype = T1.xusertype) DB2 '
    set @sql3 = 'select DB1.* , ''' 
        + @db1 
        + ' <--------> ' 
        + @db2 + ''', DB2.* from ' 
        + @sql1 
        + ' full outer join ' 
        + @sql2 
        + ' on Db1.Tabella = Db2.tabella and Db1.Campo = Db2.Campo 
        where Db1.Tabella is null or DB2.Tabella is null or Db1.Tipo <> DB2.Tipo 
            or DB1.Lunghezza <> DB2.Lunghezza or DB1.Precisione <> DB2.Precisione 
            or DB1.Scale <> DB2.Scale or DB1.isNUllable <> DB2.isNullable 
        order by case when Db1.Tabella is null then DB2.Tabella else DB1.Tabella end, 
            case when isnull(DB1.Colid,1000) < isnull(Db2.Colid,1000) then DB1.ColId else DB2.ColId end'
    exec (@sql3)

posted @ mercoledì 28 aprile 2010 14:03

Print
Comments have been closed on this topic.
«dicembre»
domlunmarmergiovensab
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678