sql2plantuml.fsx

#r @"C:\Users\bsermons\.nuget\packages\microsoft.netcore.app\2.0.0\ref\netcoreapp2.0\netstandard.dll"

module SqlGraph =

    open System
    open System.Data
    open System.Data.SqlClient

    let connStr = "Server=<<server>>;Database=<<database>>;Trusted_Connection=True;"

    let fquery = fun table -> sprintf @"SELECT
  object_name(parent_object_id) ParentTableName,
  object_name(referenced_object_id) RefTableName,
  name 
FROM sys.foreign_keys
WHERE parent_object_id = object_id('%s')" table

    type table =
        { name: string;
          cols: column list;
          refs: string list }
    and column =
        { name: string;
          coltype: string;
          nullable: bool }


    let printDt (dt:DataTable) =
        for row in dt.Rows do
            for col in dt.Columns do
                printfn "%s=%s" col.ColumnName (row.[col.ColumnName].ToString())
            printfn "\n"

    let getRefs tableName (conn:SqlConnection) =
        let cmd = conn.CreateCommand()
        cmd.CommandText <- fquery tableName
        cmd.CommandType <- CommandType.Text

        let dt = new DataTable()
        dt.Load(cmd.ExecuteReader())

        dt.Select()
        |> Seq.map (fun row -> row.["RefTableName"].ToString())
        |> Seq.toList

    let genDiagrams (tables: table seq) =
        let mutable reflinks = []

        seq {
            yield "@startuml\n"
            yield "hide circle"
            yield "skinparam linetype ortho"

            for table in tables do
              yield (sprintf "\nentity \"%s\" {" table.name)

              for col in table.cols do
                yield (sprintf "  %s%s : %s" (if col.nullable then "" else "*") col.name col.coltype)

              yield "}\n"

              for ref in table.refs do
                reflinks <- (sprintf "%s ||--|| %s" table.name ref) :: reflinks


            for ref in reflinks do
              yield ref

            yield "\n@enduml"
            }
        |> String.concat "\n"

    let mapCol (row:DataRow) =
        { name = row.["COLUMN_NAME"].ToString();
          coltype = row.["DATA_TYPE"].ToString();
          nullable = (row.["IS_NULLABLE"].ToString()) = "YES" }


    let run () =
        use conn = new SqlConnection(connStr)
        conn.Open()
        let columnsDt = conn.GetSchema("Columns")
        let tables =
            columnsDt.Select()
            |> Seq.groupBy (fun row -> row.["TABLE_NAME"].ToString())
            |> Seq.map (fun (tableName, rows) ->
                        { name = tableName;
                          cols = Seq.map mapCol rows |> Seq.toList
                          refs = getRefs tableName conn})

        genDiagrams(tables)

    run()

Script to use sql schema information and dump it to a plantuml diagram.