F# Get Database column names and data types

Question

I'm working with 2 databases, both contain the same tables, but there's some differences in the data.

I'm wondering if there's a way to get the names and data types of the columns in an sql table and then add the names of each and data types to a list for later analysis.

Answer 1

You have three (four if you throw in ADO.NET) choices to access your database:

1. The stock SQLDataConnection Type Provider shipped with F# 3, which can be used with SQLServer. It works with SQLServer, and if you prefer to work with LINQ, this is an easy to way to access it.
2. A more up-to-date and versatile SqlDataProvider Type Provider that works with a variety of databases. If your database is anything but SQLServer, this is the type provider to use.
3. And the SqlClient Type Provider that will give you access to type safe SQL. If you are on SQLServer and well versed in T-SQL this is the way to go.

You can use all three to get the relevant data. Here's one way by using an SQL query via SqlCommandProvider (Method 3):

#r @"..\packages\FSharp.Data.SqlClient.1.8.2\lib\net40\FSharp.Data.SqlClient.dll"

open FSharp.Data
open System

[<Literal>]
let connectionString = @"Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=C:\Users\username\Documents\test.mdf;Integrated Security=True;Connect Timeout=10"    

let cmd = new SqlCommandProvider<"select * from Information_schema.Columns where table_name = @tableName",connectionString>(connectionString)
let out = cmd.Execute(tableName="yourTableName")
out
    |> Seq.map (fun x -> (x.COLUMN_NAME,x.DATA_TYPE)) 
    |> Seq.toList

val it : (Option * Option) list = [(Some "AutoUpdated", Some "bigint"); (Some "UpdatedDate", Some "datetime"); (Some "DataDate", Some "datetime"); (Some "RandomStuff", Some "float"); ...]

You can use x.COLUMN_NAME.Value if you want to get rid of the option types.

To get all tables in a database (this is different from all tables on the server). You only need to replace DB_NAME with your database name (or you might just skip if it's a localdb):

let cmd2 = new SqlCommandProvider<"select TABLE_NAME from [DB_NAME].Information_Schema.Tables where table_type = 'BASE TABLE'",connectionString>(connectionString)
let out2 = cmd2.Execute()
out2 |> Seq.toList