Get the Differences between two Databases (Preferably Similar)

Run the following sql query for each database.

SELECT ST.NAME 'TABLE'

            ,SC.NAME 'COLUMN'

           ,T.NAME 'COLUMN TYPE'

            ,SC.MAX_LENGTH 'LENGTH', SC.is_nullable

,object_definition(SC.default_object_id) AS defaultVal

             

FROM SYS.TABLES ST

INNER JOIN SYS.COLUMNS SC ON ST.OBJECT_ID = SC.OBJECT_ID

INNER JOIN SYS.TYPES T ON SC.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID

            AND SC.SYSTEM_TYPE_ID = T.USER_TYPE_ID

LEFT JOIN SYS.EXTENDED_PROPERTIES EX ON EX.MINOR_ID = SC.COLUMN_ID

            AND EX.MAJOR_ID = ST.OBJECT_ID  order by ST.NAME,SC.NAME

Save the outputs as  .CSV files.

Build an EXE file for the following code.

Put the two CSV files and the exe file in a folder

Run the EXE file 

A TXT file will be created with the Differences (If any).

-----------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.IO;

namespace DBCompare
{
    class Column
    {
        public string Name;
        public string DataType;
        public string Length;
        public string Nullable;
        public string DefaultValue;
    }
    class Table
    {
        public string Name;
        public Dictionary<string, Column> Columns = new Dictionary<string, Column>();

    }
    class Database
    {
        public string Name;
        public Dictionary<string, Table> Tables = new Dictionary<string, Table>();

    }


    class Program
    {
        static Database ReadFromFile(string File)
        {
            StreamReader sr = new StreamReader(File);
            Database db= new Database();
            db.Name = File;
            string line="";
            while((line=sr.ReadLine())!=null)
            {
                string[] breakup = line.Split(',');
                Table table=null;
                Column column = null;
                if (db.Tables.ContainsKey(breakup[0].Trim()))
                {
                    table = db.Tables[breakup[0].Trim()];
                }
                else
                {
                    table = new Table();
                    table.Name = breakup[0].Trim();
                    db.Tables.Add(breakup[0].Trim(),table);
                }
                if (table.Columns.ContainsKey(breakup[1].Trim()))
                {
                    column = table.Columns[breakup[1].Trim()];
                }
                else
                {
                    column = new Column();
                    column.Name = breakup[1].Trim();
                    column.DataType = breakup[2].Trim();
                    column.Length = breakup[3].Trim();
                    column.Nullable = breakup[4].Trim();
                    column.DefaultValue = breakup[5].Trim();
                    table.Columns.Add(breakup[1].Trim(), column);
                }

            }
            sr.Close();
            return db;
        }

        static void Compare(Database db1, Database db2, StreamWriter sw)
        {
            sw.WriteLine("MISSING IN " + db2.Name);
            sw.WriteLine("----------------------------------------------------------------------------------------------------------------");
            sw.WriteLine("");
            foreach (string table in db1.Tables.Keys)
            {
                if (!db2.Tables.ContainsKey(table))
                {
                    sw.WriteLine("MISSING  TABLE: " + table);
                }
                else
                {
                    foreach (string column in db1.Tables[table].Columns.Keys)
                    {
                        if (!db2.Tables[table].Columns.ContainsKey(column))
                        {
                            sw.WriteLine("MISSING COLUMN: " + column + " IN TABLE: " + table);
                        }
                        else
                        {
                            Column c1 = db1.Tables[table].Columns[column];
                            Column c2 = db2.Tables[table].Columns[column];
                            if (!c1.DataType.Equals(c2.DataType))
                            {
                                sw.WriteLine("DATATYPE MISMATCH COLUMN: " + column + " IN TABLE: " + table + " {" + c1.DataType + " AND " + c2.DataType + " }");
                            }
                            if (!c1.Length.Equals(c2.Length))
                            {
                                sw.WriteLine("DATALENGTH MISMATCH COLUMN: " + column + " IN TABLE: " + table + " {" + c1.Length + " AND " + c2.Length + " }");
                            }
                            if (!c1.Nullable.Equals(c2.Nullable))
                            {
                                sw.WriteLine("NULLABLE MISMATCH COLUMN: " + column + " IN TABLE: " + table );
                            }
                            if (!c1.DefaultValue.Equals(c2.DefaultValue))
                            {
                                sw.WriteLine("DefaultValue [" + c1.DefaultValue + " || " + c2.DefaultValue + "] MISMATCH COLUMN: " + column + " IN TABLE: " + table);
                            }
                        }
                    }

                }
            }
            sw.WriteLine("");
            sw.WriteLine("");
        }
        static void Main(string[] args)
        {
            DirectoryInfo dir = new DirectoryInfo(".");
            FileInfo[] files = dir.GetFiles("*.csv");

            string firstFile = "";
            string secondFile = "";

            if (files.Count() == 0)
            {
                Console.WriteLine("No csv files found. Please add two csv files to compare.");
            }
            else if (files.Count() > 2)
            {
                Console.WriteLine("More than two csv files were found. Please keep only two csv files to compare in the folder.");
            }
            else if (files.Count() == 1)
            {
                Console.WriteLine("Only one csv file was found. Please add one more csv file to compare in the folder.");
            }
            else
            {
                if (files[0].Name != null && files[0].Name != "")
                {
                    firstFile = files[0].Name;
                }

                if (files[1].Name != null && files[1].Name != "")
                {
                    secondFile = files[1].Name;
                }



                //Database oldDB = ReadFromFile(args[0]);
                //Database newDB = ReadFromFile(args[1]);

                if (firstFile != "" && secondFile != "")
                {
                    Database oldDB = ReadFromFile(firstFile);
                    Database newDB = ReadFromFile(secondFile);

                    StreamWriter sw = new StreamWriter("[" + DateTime.Now.ToShortDateString() + "][" + firstFile.Split('.')[0] + "==" + secondFile.Split('.')[0] + "].txt");

                    Compare(oldDB, newDB, sw);
                    Compare(newDB, oldDB, sw);
                    sw.Close();

                    Console.WriteLine("Success\n Output File generated with the name 'CompareReport[" + DateTime.Now.ToShortDateString() + "][" + firstFile.Split('.')[0] + "==" + secondFile.Split('.')[0] + "].txt'");
                }
                else
                {
                    Console.WriteLine("File name was not found.");
                }
               

               
            }
          

            Console.ReadKey();
        }
    }
}