search

fsprojects / SQLProvider

A general F# SQL database erasing type provider, supporting LINQ queries, schema exploration, individuals, CRUD operations and much more besides.
https://fsprojects.github.io/SQLProvider
Other
564 stars 144 forks source link

Querying for metadata from Information_Schema #712

Open bisen2 opened 3 years ago

bisen2 commented 3 years ago

I was wondering if their is a convenient way to use this library to pull metadata from a database. In pure SQL I would generally do this by querying the Information_Schema view. For example, to get a list of columns in the table MyTableName, I would do:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA
WHERE TABLE_NAME = 'MyTableName'

The type provider does not appear to expose the Information_Schema view, so I was curious if there is standard way to obtain database metadata through this API. Thanks!

Thorium commented 3 years ago

Each of the providers are using their own methods to get the schemas. But it's not exposed outside. I'm just curious what is the use-case for this? I'm not even sure if typically the runtime connection string user even has permissions to query the schema.

bisen2 commented 3 years ago

Generally this is just a quick and dirty method to become familiar with a new database. For example, if I have a database holding some time series data that I am unfamiliar with, I might want to pull the data from each column and plot it against the time data to get an idea of general trends over time. I have only ever done this through SSMS or Azure Data Studio, so you may be right that a connection string user doesn't have access to this information. It is not a big deal if the answer is simply "Nope, can't do it." I just figured I would check in and see if this was possible.