Obtaining Schema Information from a Database

  • Article

You can obtain schema information from your data source using any of the .NET Framework data providers. Schema information in a data source includes databases or catalogs available from the data source, tables and views in a database, constraints that exist for tables at the data source, and so on.

The .NET Framework Data Provider for SQL Server and .NET Framework Data Provider for ODBC expose schema information through functionality provided by your specific data source such as stored procedures and informational views. For information about views and stored procedures available through Microsoft SQL Server, see the Transact-SQL reference located in the MSDN library at https://msdn.microsoft.com/library.

The .NET Framework Data Provider for OLE DB exposes schema information using the GetOleDbSchemaTable method of the OleDbConnection object. GetOleDbSchemaTable takes as arguments an OleDbSchemaGuid that identifies which schema information to return, and an array of restrictions on those returned columns. GetOleDbSchemaTable returns a DataTable populated with the schema information.

To obtain schema information from a data source the .NET Framework Data Provider for ODBC uses the same method as the .NET Framework Data Provider for SQL Server.

The following code example returns the list of tables in the Northwind database. The .NET Framework Data Provider for SQL Server example selects the tables from an informational view provided by Microsoft SQL Server and populates a DataTable using a DataAdapter. The .NET Framework Data Provider for OLE DB example uses GetOleDbSchemaTable to return a DataTable with the schema information.

SqlClient

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

Dim schemaDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES " & _
                                                    "WHERE TABLE_TYPE = 'BASE TABLE' " & _
                                                    "ORDER BY TABLE_TYPE", _
                                                    nwindConn)

Dim schemaTable As DataTable = New DataTable
schemaDA.Fill(schemaTable)
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlDataAdapter schemaDA = new SqlDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES " +
                                             "WHERE TABLE_TYPE = 'BASE TABLE' " +
                                             "ORDER BY TABLE_TYPE", 
                                             nwindConn);

DataTable schemaTable = new DataTable();
schemaDA.Fill(schemaTable);

OleDb

Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

nwindConn.Open()
Dim schemaTable As DataTable = nwindConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                                                             New Object() {Nothing, Nothing, Nothing, "TABLE"})
nwindConn.Close()
[C#]
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

nwindConn.Open();
DataTable schemaTable = nwindConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                      new object[] {null, null, null, "TABLE"});
nwindConn.Close();

See Also

Using .NET Framework Data Providers to Access Data | DataTable Class | OleDbConnection.GetOleDbSchemaTable Method | OleDbSchemaGuid Class