7 Kasım 2017

İ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