Notlarım: İki Veri Tabanının Tablolarını Karşılaştırmak

Karşılaştırma yapılacak her iki database için aşağıdaki SQL komutu ile sütunlar çağrılır.

Direk SQL Üzerinden Karşılaştırma Yapmak İçin

-- TÜM TABLOLAR İÇİN


SELECT * FROM [ASILDATABASE].INFORMATION_SCHEMA.COLUMNS G left outer join 
[KONTROLDATABASE].INFORMATION_SCHEMA.COLUMNS A on A.TABLE_NAME=G.TABLE_NAME and A.COLUMN_NAME=G.COLUMN_NAME  
where A.COLUMN_NAME is null




-- SADECE BİR TABLO İÇİN


declare @SourceTable varchar(100)
declare @ControlTable varchar(100)

set @SourceTable = 'GERCEKTABLO'
set @ControlTable = 'KONTROLEDILENTABLO'

SELECT 'ALTER TABLE '+@ControlTable+' ADD '+ G.COLUMN_NAME + ' '+DATA_TYPE 
  + CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN '' ELSE '('+CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH)+')' END  AS ALTERQUERY, *
FROM [SOURCEDATABASE].INFORMATION_SCHEMA.COLUMNS  G WHERE G.TABLE_NAME=@SourceTable
AND NOT EXISTS ( SELECT * FROM [CONTROLDATABASE].INFORMATION_SCHEMA.COLUMNS A WHERE A.TABLE_NAME=@ControlTable AND A.COLUMN_NAME=G.COLUMN_NAME)

ya da program üzerinden.


SELECT TABLE_CATALOG,TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_DEFAULT, NUMERIC_PRECISION, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_CATALOG,TABLE_NAME,COLUMN_NAME

Var olan sütunlar aşağıdaki metod ile karşılaştırılır.

public static List<string> HasErrors(this Model.Column thisColumn,

 List<Model.Column> otherColumns)

        {

            var errors = new List<string>();

            var otherColumn = new Model.Column();

            try

            {

                otherColumn = otherColumns.FirstOrDefault 

                        (q => q.TableName == thisColumn.TableName && 

                            q.ColumnName == thisColumn.ColumnName);

                if (otherColumn == null)

                {

                    errors.Add(String.Format("{0}.{1}.{2} doesn't 

                         exist in the other database",  

                         thisColumn.TableCatalog, thisColumn.TableName,

                        thisColumn.ColumnName));

                }

            }

            catch (Exception ex)

            {

                errors.Add(ex.Message);

            }

            if (errors.Count == 0)

            {

                if (thisColumn.DataType != otherColumn.DataType)

                    errors.Add(String.Format("{0}.{1}.{2} DataType 

                            is not same as {3}.{4}.{5}",

                           thisColumn.TableCatalog, thisColumn.TableName,

                           thisColumn.ColumnName, otherColumn.TableCatalog,

                           otherColumn.TableName, otherColumn.ColumnName));

                if (thisColumn.CharacterMaximumLength != otherColumn 

                                 .CharacterMaximumLength)

                    errors.Add(String.Format("{0}.{1}.{2} 

                             CharacterMaximumLength is not same as {3}.{4}.{5}",

                             thisColumn.TableCatalog, thisColumn.TableName,  

                             thisColumn.ColumnName, otherColumn.TableCatalog,  

                             otherColumn.TableName, otherColumn.ColumnName));

                if (thisColumn.IsNullable != otherColumn.IsNullable)

                    errors.Add(String.Format("{0}.{1}.{2} IsNullable 

                    is not same as {3}.{4}.{5}", thisColumn.TableCatalog,  

                    thisColumn.TableName, thisColumn.ColumnName, 

                    otherColumn.TableCatalog, otherColumn.TableName,  

                    otherColumn.ColumnName));

                if (thisColumn.ColumnDefault != otherColumn.ColumnDefault)

                    errors.Add(String.Format("{0}.{1}.{2} ColumnDefault 

                    is not same as {3}.{4}.{5}", thisColumn.TableCatalog,

                     thisColumn.TableName, thisColumn.ColumnName, 

                      otherColumn.TableCatalog, otherColumn.TableName,  

                     otherColumn.ColumnName));

                if (thisColumn.NumericPrecision != otherColumn.NumericPrecision)

                    errors.Add(String.Format("{0}.{1}.{2} NumericPrecision 

                    is not same as {3}.{4}.{5}", thisColumn.TableCatalog,  

                    thisColumn.TableName, thisColumn.ColumnName, 

                    otherColumn.TableCatalog, otherColumn.TableName,

                    otherColumn.ColumnName));

                if (thisColumn.CollationName != otherColumn.CollationName)

                    errors.Add(String.Format("{0}.{1}.{2} CollationName is not

                    same as {3}.{4}.{5}", thisColumn.TableCatalog,  

                    thisColumn.TableName, thisColumn.ColumnName,  

                    otherColumn.TableCatalog, otherColumn.TableName,  

                    otherColumn.ColumnName));

            }

            if (errors.Count == 0)

                otherColumns.Remove(otherColumn);

            return errors;            

}

Kodlar aşağıdaki linktedir

https://github.com/AmagiTech/DatabaseComparer

7 Kasım 2017

İki Veri Tabanının Tablolarını Karşılaştırmak