gistfile1.txt

open System.Data
open System.Data.SqlClient

let nonQuery (connectionString) query : unit = 
    use conn = new SqlConnection(connectionString)
    use cmd = new SqlCommand()

    cmd.CommandText <- query
    cmd.CommandType <- CommandType.Text
    cmd.Connection <- conn

    conn.Open()

    cmd.ExecuteNonQuery() |> ignore
    conn.Close()

let getRecords (connectionString) query : list<Map<string, obj>>= 
    use conn = new SqlConnection(connectionString)
    use cmd = new SqlCommand()

    cmd.CommandText <- query
    cmd.CommandType <- CommandType.Text
    cmd.Connection <- conn

    conn.Open()

    use reader = cmd.ExecuteReader()

    let columns =
        [0..(reader.FieldCount - 1)]
        |> List.map(fun i ->
            i, reader.GetName(i).ToLower())

    let mutable rows = List.empty
    if reader.HasRows then
        while reader.Read() do
            let row =
                columns
                |> List.map(fun (i, n) ->
                    n, reader.Item i)
                |> Map.ofList
            rows <- rows @ [row]
    conn.Close()
    rows

let spoke1Con = "Server=office5.birddogsw.local;Database=spoke1;User Id=sa;Password=joy527;"
let hubCon = "Server=office5.birddogsw.local;Database=hub;User Id=sa;Password=joy527;"

let spoke1Get = getRecords (spoke1Con) 
let hubGet = getRecords (hubCon)
let spoke1NonQuery = nonQuery (spoke1Con) 
let hubNonQuery = nonQuery (hubCon) 

let getTables get = 
    get "select table_name from information_schema.tables where table_schema = 'dbo' AND table_type = 'BASE TABLE'"
    |> List.map(fun (x : Map<string, obj>) -> (x |> Map.find "table_name") :?> string)
let getColumns name get = 
    get ("select column_name from information_schema.columns where table_name = '" + name + "'")
    |> List.map(fun (x : Map<string, obj>) -> (x |> Map.find "column_name") :?> string)

let spoke1Tables = getTables spoke1Get
let hubTables = getTables hubGet

let tablesInBoth = Set.intersect  (Set.ofList spoke1Tables) (Set.ofList hubTables) |> List.ofSeq

let compareTable tableName =
    let spoke1Table = getColumns tableName spoke1Get |> Set.ofList
    let hubTable = getColumns tableName hubGet |> Set.ofList

    let notInSpoke1 = Set.difference hubTable spoke1Table
    let notInHub = Set.difference spoke1Table hubTable
    notInSpoke1 |> List.ofSeq, notInHub |> List.ofSeq

let cleanTable tableName =
    let removeColumn non columnName : unit =
        let query = sprintf "ALTER TABLE %s DROP COLUMN %s" tableName columnName
        non query

    let notInSpoke1, notInHub = compareTable tableName
    notInSpoke1 |> List.iter(removeColumn hubNonQuery)
    notInHub |> List.iter(removeColumn spoke1NonQuery)

let differentTables =
    tablesInBoth
    |> List.filter(fun table ->
        let diff1, diff2 = compareTable table
        diff1.Length <> 0 || diff2.Length <> 0)

differentTables |> List.iter cleanTable