Retrieve Column Schema with GetSchemaTable

Table of Contents

The Microsoft KB example code or class library documentation do not explain how to efficiently return a table’s schema. For example, this code follows the documentation:

DataTable dataTable = null;
using (IDbConnection dbConnection = (IDbConnection)database.Unwrap()) {
  dbConnection.Open();
  using (IDbCommand dbCommand = dbConnection.CreateCommand()) {
    dbCommand.CommandType = CommandType.Text;
    dbCommand.CommandText = "SELECT * FROM SomeLargeTable;";
    using (IDataReader dataReader = dbCommand.ExecuteReader(CommandBehavior.KeyInfo)) {
      dataTable = dataReader.GetSchemaTable();
      dataReader.Close();
    }
  }
  dbConnection.Close();
}

// look at table schema stored in dataTable

Results in the following SQL executed on SQL Server:

SET FMTONLY OFF; SET NO_BROWSETABLE ON;
SELECT * FROM SomeLargeTable; SET NO_BROWSETABLE OFF;

If you add | CommandBehavior.SchemaOnly to the ExecuteReader method call:

SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;
SELECT * FROM SomeLargeTable; SET NO_BROWSETABLE OFF; SET FMTONLY OFF;

According to the documentation:

CommandBehavior.SchemaOnly #

The query returns column information only. When using SchemaOnly, the .NET Framework Data Provider for SQL Server precedes the statement being executed with SET FMTONLY ON. — .NET Docs¹

FMTONLY ON #

Returns only metadata to the client. Can be used to test the format of the response without actually running the query. — SQL Docs²

As expected, I had a significant performance increase with (CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly). Querying 100,000,000 rows to retrieve table schema is inefficient, to say the least.