Simple SQL Server Diff Tool - C# Snipplr Social Repository

Revision: 30392

at August 13, 2010 02:12 by jmcd

Updated Code

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;

namespace Sdiff
{
    internal class Program
    {
        /// <param name = "args">arg[0] old schema connection string, arg[1] target schema connection string</param>
        /// <summary>
        ///   Tells you what to do with old schema to get it into the shape of the new
        /// </summary>
        private static void Main(string[] args)
        {
            var diffs = new List<Diff>();

            var oldContext = new IsDataContext(args[0]);
            var newContext = new IsDataContext(args[1]);

            var newTableNames = newContext.Tables.Select(x => x.Name).ToList();
            var oldTableNames = oldContext.Tables.Select(x => x.Name).ToList();

            AddDiffs(diffs, oldTableNames.Except(newTableNames), DiffKind.TableDelete);
            AddDiffs(diffs, newTableNames.Except(oldTableNames), DiffKind.TableCreate);

            var commonTableNames = newTableNames.Intersect(oldTableNames);
            foreach (var tableName in commonTableNames)
            {
                var newColumns = newContext.Columns.Where(x => x.TableName == tableName).ToList();
                var oldColumns = oldContext.Columns.Where(x => x.TableName == tableName).ToList();

                var newColumnNames = newColumns.Select(x => x.FullName);
                var oldColumnNames = oldColumns.Select(x => x.FullName);

                AddDiffs(diffs, oldColumnNames.Except(newColumnNames), DiffKind.ColumnDelete);
                AddDiffs(diffs, newColumnNames.Except(oldColumnNames), DiffKind.ColumnCreate);

                var commonColumnNames = newColumnNames.Intersect(oldColumnNames);
                foreach (var commonColumnName in commonColumnNames)
                {
                    var newDataType = newColumns.Where(x => x.FullName == commonColumnName).Select(x => x.DataType).Single();
                    var oldDataType = oldColumns.Where(x => x.FullName == commonColumnName).Select(x => x.DataType).Single();
                    if (oldDataType != newDataType) diffs.Add(new Diff(commonColumnName + " " + oldDataType + " -> " + newDataType, DiffKind.ColumnDataTypeChange));
                }
            }

            WriteSection(diffs.Where(x => x.Kind == DiffKind.TableDelete), "Tables to delete");
            WriteSection(diffs.Where(x => x.Kind == DiffKind.TableCreate), "Tables to create");
            WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnDelete), "Columns to delete");
            WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnCreate), "Columns to create");
            WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnDataTypeChange), "Columns to modify");

            Console.ReadKey();
        }

        private static void AddDiffs(List<Diff> diffs, IEnumerable<string> names, DiffKind diffKind)
        {
            diffs.AddRange(names.Select(name => new Diff(name, diffKind)));
        }


        private static void WriteSection(IEnumerable<Diff> diffs, string title)
        {
            if (!diffs.Any()) return;
            Console.WriteLine();
            Console.WriteLine(title);
            Console.WriteLine(string.Empty.PadLeft(title.Length, '-'));
            foreach (var name in diffs.Select(x => x.Name).OrderBy(x => x))
            {
                Console.WriteLine(name);
            }
        }

        private class Diff
        {
            public Diff(string name, DiffKind kind)
            {
                Name = name;
                Kind = kind;
            }

            public string Name { get; private set; }
            public DiffKind Kind { get; private set; }
        }

        internal enum DiffKind
        {
            TableDelete,
            TableCreate,
            ColumnDelete,
            ColumnCreate,
            ColumnDataTypeChange
        }
    }


    public class IsDataContext : DataContext
    {
        public Table<Column> Columns;
        public Table<Table> Tables;
        public IsDataContext(string connection) : base(connection) {}
    }

    [Table(Name = "INFORMATION_SCHEMA.TABLES")]
    public class Table
    {
        [Column(Name = "TABLE_NAME")] public string Name;
    }

    [Table(Name = "INFORMATION_SCHEMA.COLUMNS")]
    public class Column
    {
        [Column(Name = "CHARACTER_MAXIMUM_LENGTH")] public int? CharacterMaximumLength;
        [Column(Name = "DATA_TYPE")] public string DataTypeName;
        [Column(Name = "COLUMN_NAME")] public string Name;
        [Column(Name = "TABLE_NAME")] public string TableName;

        public string DataType
        {
            get
            {
                if (!CharacterMaximumLength.HasValue) return DataTypeName;
                if (CharacterMaximumLength.Value == -1) return DataTypeName + "(MAX)";
                return DataTypeName + "(" + CharacterMaximumLength.Value + ")";
            }
        }

        public string FullName { get { return TableName + "." + Name; } }
    }
}

Revision: 30391

at August 13, 2010 02:11 by jmcd

Updated Code

/// <param name="args">arg[0] old schema connection string, arg[1] target schema connection string</param>
/// <summary>Tells you what to do with old schema to get it into the shape of the new</summary>
internal class Program
{
    private static void Main(string[] args)
    {
        var diffs = new List<Diff>();

        var oldContext = new IsDataContext(args[0]);
        var newContext = new IsDataContext(args[1]);

        var newTableNames = newContext.Tables.Select(x => x.Name).ToList();
        var oldTableNames = oldContext.Tables.Select(x => x.Name).ToList();

        AddDiffs(diffs, oldTableNames.Except(newTableNames), DiffKind.TableDelete);
        AddDiffs(diffs, newTableNames.Except(oldTableNames), DiffKind.TableCreate);

        var commonTableNames = newTableNames.Intersect(oldTableNames);
        foreach (var tableName in commonTableNames)
        {
            var newColumns = newContext.Columns.Where(x => x.TableName == tableName).ToList();
            var oldColumns = oldContext.Columns.Where(x => x.TableName == tableName).ToList();

            var newColumnNames = newColumns.Select(x => x.FullName);
            var oldColumnNames = oldColumns.Select(x => x.FullName);

            AddDiffs(diffs, oldColumnNames.Except(newColumnNames), DiffKind.ColumnDelete);
            AddDiffs(diffs, newColumnNames.Except(oldColumnNames), DiffKind.ColumnCreate);

            var commonColumnNames = newColumnNames.Intersect(oldColumnNames);
            foreach (var commonColumnName in commonColumnNames)
            {
                var newDataType = newColumns.Where(x => x.FullName == commonColumnName).Select(x => x.DataType).Single();
                var oldDataType = oldColumns.Where(x => x.FullName == commonColumnName).Select(x => x.DataType).Single();
                if (oldDataType != newDataType) diffs.Add(new Diff(commonColumnName + " " + oldDataType + " -> " + newDataType, DiffKind.ColumnDataTypeChange));
            }
        }

        WriteSection(diffs.Where(x => x.Kind == DiffKind.TableDelete), "Tables to delete");
        WriteSection(diffs.Where(x => x.Kind == DiffKind.TableCreate), "Tables to create");
        WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnDelete), "Columns to delete");
        WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnCreate), "Columns to create");
        WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnDataTypeChange), "Columns to modify");

    }

    private static void AddDiffs(List<Diff> diffs, IEnumerable<string> names, DiffKind diffKind)
    {
        diffs.AddRange(names.Select(name => new Diff(name, diffKind)));
    }


    private static void WriteSection(IEnumerable<Diff> diffs, string title)
    {
        if (!diffs.Any()) return;
        Console.WriteLine();
        Console.WriteLine(title);
        Console.WriteLine(string.Empty.PadLeft(title.Length, '-'));
        foreach (var name in diffs.Select(x => x.Name).OrderBy(x => x))
        {
            Console.WriteLine(name);
        }
    }

    private class Diff
    {
        public Diff(string name, DiffKind kind)
        {
            Name = name;
            Kind = kind;
        }

        public string Name { get; private set; }
        public DiffKind Kind { get; private set; }
    }

    internal enum DiffKind
    {
        TableDelete,
        TableCreate,
        ColumnDelete,
        ColumnCreate,
        ColumnDataTypeChange
    }
}


public class IsDataContext : DataContext
{
    public Table<Column> Columns;
    public Table<Table> Tables;
    public IsDataContext(string connection) : base(connection) {}
}

[Table(Name = "INFORMATION_SCHEMA.TABLES")]
public class Table
{
    [Column(Name = "TABLE_NAME")] public string Name;
}

[Table(Name = "INFORMATION_SCHEMA.COLUMNS")]
public class Column
{
    [Column(Name = "CHARACTER_MAXIMUM_LENGTH")] public int? CharacterMaximumLength;
    [Column(Name = "DATA_TYPE")] public string DataTypeName;
    [Column(Name = "COLUMN_NAME")] public string Name;
    [Column(Name = "TABLE_NAME")] public string TableName;

    public string DataType
    {
        get
        {
            if (!CharacterMaximumLength.HasValue) return DataTypeName;
            if (CharacterMaximumLength.Value == -1) return DataTypeName + "(MAX)";
            return DataTypeName + "(" + CharacterMaximumLength.Value + ")";
        }
    }

    public string FullName { get { return TableName + "." + Name; } }
}

Revision: 30390

at August 13, 2010 02:02 by jmcd

Updated Code

internal class Program
{
    private static void Main(string[] args)
    {
        var diffs = new List<Diff>();

        var oldContext = new IsDataContext(args[0]);
        var newContext = new IsDataContext(args[1]);

        var newTableNames = newContext.Tables.Select(x => x.Name).ToList();
        var oldTableNames = oldContext.Tables.Select(x => x.Name).ToList();

        AddDiffs(diffs, oldTableNames.Except(newTableNames), DiffKind.TableDelete);
        AddDiffs(diffs, newTableNames.Except(oldTableNames), DiffKind.TableCreate);

        var commonTableNames = newTableNames.Intersect(oldTableNames);
        foreach (var tableName in commonTableNames)
        {
            var newColumns = newContext.Columns.Where(x => x.TableName == tableName).ToList();
            var oldColumns = oldContext.Columns.Where(x => x.TableName == tableName).ToList();

            var newColumnNames = newColumns.Select(x => x.FullName);
            var oldColumnNames = oldColumns.Select(x => x.FullName);

            AddDiffs(diffs, oldColumnNames.Except(newColumnNames), DiffKind.ColumnDelete);
            AddDiffs(diffs, newColumnNames.Except(oldColumnNames), DiffKind.ColumnCreate);

            var commonColumnNames = newColumnNames.Intersect(oldColumnNames);
            foreach (var commonColumnName in commonColumnNames)
            {
                var newDataType = newColumns.Where(x => x.FullName == commonColumnName).Select(x => x.DataType).Single();
                var oldDataType = oldColumns.Where(x => x.FullName == commonColumnName).Select(x => x.DataType).Single();
                if (oldDataType != newDataType) diffs.Add(new Diff(commonColumnName + " " + oldDataType + " -> " + newDataType, DiffKind.ColumnDataTypeChange));
            }
        }

        WriteSection(diffs.Where(x => x.Kind == DiffKind.TableDelete), "Tables to delete");
        WriteSection(diffs.Where(x => x.Kind == DiffKind.TableCreate), "Tables to create");
        WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnDelete), "Columns to delete");
        WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnCreate), "Columns to create");
        WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnDataTypeChange), "Columns to modify");

    }

    private static void AddDiffs(List<Diff> diffs, IEnumerable<string> names, DiffKind diffKind)
    {
        diffs.AddRange(names.Select(name => new Diff(name, diffKind)));
    }


    private static void WriteSection(IEnumerable<Diff> diffs, string title)
    {
        if (!diffs.Any()) return;
        Console.WriteLine();
        Console.WriteLine(title);
        Console.WriteLine(string.Empty.PadLeft(title.Length, '-'));
        foreach (var name in diffs.Select(x => x.Name).OrderBy(x => x))
        {
            Console.WriteLine(name);
        }
    }

    private class Diff
    {
        public Diff(string name, DiffKind kind)
        {
            Name = name;
            Kind = kind;
        }

        public string Name { get; private set; }
        public DiffKind Kind { get; private set; }
    }

    internal enum DiffKind
    {
        TableDelete,
        TableCreate,
        ColumnDelete,
        ColumnCreate,
        ColumnDataTypeChange
    }
}


public class IsDataContext : DataContext
{
    public Table<Column> Columns;
    public Table<Table> Tables;
    public IsDataContext(string connection) : base(connection) {}
}

[Table(Name = "INFORMATION_SCHEMA.TABLES")]
public class Table
{
    [Column(Name = "TABLE_NAME")] public string Name;
}

[Table(Name = "INFORMATION_SCHEMA.COLUMNS")]
public class Column
{
    [Column(Name = "CHARACTER_MAXIMUM_LENGTH")] public int? CharacterMaximumLength;
    [Column(Name = "DATA_TYPE")] public string DataTypeName;
    [Column(Name = "COLUMN_NAME")] public string Name;
    [Column(Name = "TABLE_NAME")] public string TableName;

    public string DataType
    {
        get
        {
            if (!CharacterMaximumLength.HasValue) return DataTypeName;
            if (CharacterMaximumLength.Value == -1) return DataTypeName + "(MAX)";
            return DataTypeName + "(" + CharacterMaximumLength.Value + ")";
        }
    }

    public string FullName { get { return TableName + "." + Name; } }
}

Revision: 30389

at August 13, 2010 02:02 by jmcd

Initial Code

internal class Program
{
    private static void Main(string[] args)
    {
        var diffs = new List<Diff>();

        var oldContext = new IsDataContext(args[0]);
        var newContext = new IsDataContext(args[1]);

        var newTableNames = newContext.Tables.Select(x => x.Name).ToList();
        var oldTableNames = oldContext.Tables.Select(x => x.Name).ToList();

        AddDiffs(diffs, oldTableNames.Except(newTableNames), DiffKind.TableDelete);
        AddDiffs(diffs, newTableNames.Except(oldTableNames), DiffKind.TableCreate);

        var commonTableNames = newTableNames.Intersect(oldTableNames);
        foreach (var tableName in commonTableNames)
        {
            var newColumns = newContext.Columns.Where(x => x.TableName == tableName).ToList();
            var oldColumns = oldContext.Columns.Where(x => x.TableName == tableName).ToList();

            var newColumnNames = newColumns.Select(x => x.FullName);
            var oldColumnNames = oldColumns.Select(x => x.FullName);

            AddDiffs(diffs, oldColumnNames.Except(newColumnNames), DiffKind.ColumnDelete);
            AddDiffs(diffs, newColumnNames.Except(oldColumnNames), DiffKind.ColumnCreate);

            var commonColumnNames = newColumnNames.Intersect(oldColumnNames);
            foreach (var commonColumnName in commonColumnNames)
            {
                var newDataType = newColumns.Where(x => x.FullName == commonColumnName).Select(x => x.DataType).Single();
                var oldDataType = oldColumns.Where(x => x.FullName == commonColumnName).Select(x => x.DataType).Single();
                if (oldDataType != newDataType) diffs.Add(new Diff(commonColumnName + " " + oldDataType + " -> " + newDataType, DiffKind.ColumnDataTypeChange));
            }
        }

        WriteSection(diffs.Where(x => x.Kind == DiffKind.TableDelete), "Tables to delete");
        WriteSection(diffs.Where(x => x.Kind == DiffKind.TableCreate), "Tables to create");
        WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnDelete), "Columns to delete");
        WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnCreate), "Columns to create");
        WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnDataTypeChange), "Columns to modify");

        Console.ReadKey();
    }

    private static void AddDiffs(List<Diff> diffs, IEnumerable<string> names, DiffKind diffKind)
    {
        diffs.AddRange(names.Select(name => new Diff(name, diffKind)));
    }


    private static void WriteSection(IEnumerable<Diff> diffs, string title)
    {
        if (!diffs.Any()) return;
        Console.WriteLine();
        Console.WriteLine(title);
        Console.WriteLine(string.Empty.PadLeft(title.Length, '-'));
        foreach (var name in diffs.Select(x => x.Name).OrderBy(x => x))
        {
            Console.WriteLine(name);
        }
    }

    private class Diff
    {
        public Diff(string name, DiffKind kind)
        {
            Name = name;
            Kind = kind;
        }

        public string Name { get; private set; }
        public DiffKind Kind { get; private set; }
    }

    internal enum DiffKind
    {
        TableDelete,
        TableCreate,
        ColumnDelete,
        ColumnCreate,
        ColumnDataTypeChange
    }
}


public class IsDataContext : DataContext
{
    public Table<Column> Columns;
    public Table<Table> Tables;
    public IsDataContext(string connection) : base(connection) {}
}

[Table(Name = "INFORMATION_SCHEMA.TABLES")]
public class Table
{
    [Column(Name = "TABLE_NAME")] public string Name;
}

[Table(Name = "INFORMATION_SCHEMA.COLUMNS")]
public class Column
{
    [Column(Name = "CHARACTER_MAXIMUM_LENGTH")] public int? CharacterMaximumLength;
    [Column(Name = "DATA_TYPE")] public string DataTypeName;
    [Column(Name = "COLUMN_NAME")] public string Name;
    [Column(Name = "TABLE_NAME")] public string TableName;

    public string DataType
    {
        get
        {
            if (!CharacterMaximumLength.HasValue) return DataTypeName;
            if (CharacterMaximumLength.Value == -1) return DataTypeName + "(MAX)";
            return DataTypeName + "(" + CharacterMaximumLength.Value + ")";
        }
    }

    public string FullName { get { return TableName + "." + Name; } }
}

Initial URL

Initial Description

Initial Title

Simple SQL Server Diff Tool

Initial Tags

sql, server, c

Initial Language

C#

Choose a language for easy browsing: