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