1

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.

Mark
  • 1,633
  • 1
  • 11
  • 26

2 Answers2

3

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
s952163
  • 6,276
  • 4
  • 23
  • 47
  • How would I do it using method 1? – Mark Sep 27 '16 at 07:43
  • @JacksonSentzke that's a good question. I actually thought about editing my post afterwards as I boldly claimed it was possible in any which way. I suspect that accessing some of the special sprocs in SQL Server is not that easy, or rather I don't know how. :-) So you would either execute a similar SQL statement or hope that some API call exposes the necessary data. Is there a particular reason why you have preference for that method? – s952163 Sep 27 '16 at 08:08
  • It's just the way I'm doing this project. I've only been doing F# for a month or so and I've only used SQLDataConnection to get data so far. – Mark Sep 27 '16 at 08:14
  • SqlClient is an interesting library, I do suggest you nuget it and explore it. I will take a look at SQLDataConnection, and you might get lucky. – s952163 Sep 27 '16 at 08:18
  • I had a few issues with your answer but I've managed to get it to work. Thank You – Mark Sep 27 '16 at 08:32
  • Also, Would I be able to modify this line, "select * from Information_schema.Columns where table_name = 'YourTableName'" to, "select * from Information_schema.Tables where database_name = 'YourDatabaseName'" So I could get a list of the tables in a database? – Mark Sep 27 '16 at 08:34
  • done. I also changed the tableName to a parameter so it can be reused. – s952163 Sep 27 '16 at 09:00
  • I get a strange error. The type provider 'FSharp.Data.SqlCommandProvider' reported an error: Reference to database and/or server name in 'databasename.Information_Schema.Tables' is not supported in this version of SQL Server. I've typed in the database name the same as it appears in the SQL Server Object Explorer. – Mark Sep 27 '16 at 10:05
  • Try it w/o the databasename part. Also there are quite a few sql queries to get this info, search for some and try it out in SQL Management Studio (or whatever tool you use to access the db). Btw, what is the version of SQL Server you are using? – s952163 Sep 27 '16 at 10:10
  • I'm not sure. I'm working with an Azure hosted SQL Server and I don't have access to those details. – Mark Sep 27 '16 at 10:34
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/124289/discussion-between-s952163-and-jackson-sentzke). – s952163 Sep 27 '16 at 11:55
1

SQL Server

exec sp_help 'tablename'

Lost_In_The_Sauce
  • 25
  • 8
  • 2
    I can't use this, I can only interact with the database through F#. – Mark Sep 26 '16 at 16:14
  • 1
    @JacksonSentzke : You can, of course, call stored procedures from F#... – ildjarn Sep 26 '16 at 19:14
  • unfortunately for this stored procedure it's not straight forward because it returns two results, the general info about the table, then the column info. – s952163 Sep 27 '16 at 02:36