Recipe 10.12 Getting SQL Server Column Metadata Without Returning Data

Problem

You need to retrieve the column metadata from a SQL Server command or stored procedure without returning any data.

Solution

Use the SET FMTONLY ON statement.

The sample code creates and executes a query statement to retrieve only column metadata from the Orders table in the Northwind sample database. A new DataTable is created from this information.

The C# code is shown in Example 10-12.

Example 10-12. File: ColumnSchemaSPForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

//  . . . 

// Create the SQL statement to retrieve only the column schema.
String cmdText = "SET FMTONLY ON;" +
    "SELECT * FROM Orders;" +
    "SET FMTONLY OFF;";

// Use a DataAdapter to fill the DataTable.
SqlConnection conn = new SqlConnection(
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
SqlCommand cmd = new SqlCommand(cmdText, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable( );
da.Fill(dt);

// Bind the default view of the table to the grid.
dataGrid.DataSource = dt.DefaultView;

Discussion

Recipe 10.9 discusses the SQL SET statement.

When SET FMTONLY is ON, no rows are processed or sent to a client when a SQL statement or stored procedure is executed; only metadata is returned to the client. The DataTable created is identical to one that would have been created if the SQL command used a WHERE clause that returned an empty result set.

For more information about the SET FMTONLY statement, see the topic "SET" in Microsoft SQL Server Books Online.