ADO.NET 3.5 Cookbook, 2nd Edition

11.7. Retrieving 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 Person.Contact table in the AdventureWorks sample database. A new DataTable is created from this information.

The C# code in Program.cs in the project RetrieveSqlServerColumnMetadata is shown in Example 11-7.

Example 11-7. File: Program.cs for RetrieveSqlServerColumnMetadata solution

using System; using System.Data; using System.Data.SqlClient; namespace RetrieveSqlServerColumnMetadata { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; // Create the T-SQL statement to retrieve only the column schema. string sqlText = "SET FMTONLY ON;" + "SELECT * FROM Person.Contact;" + "SET FMTONLY OFF;"; // Use a DataAdapter to fill the DataTable. SqlDataAdapter da = new SqlDataAdapter(sqlText, sqlConnectString); da.MissingSchemaAction = MissingSchemaAction.AddWithKey; DataTable dt = new DataTable( ); da.Fill(dt); // Output partial column metadata to console foreach (DataColumn col in dt.Columns) Console.WriteLine( "Name={0}; DataType={1}; MaxLength={2}; AllowDBNull={3}", col.ColumnName, col.DataType, col.MaxLength, col.AllowDBNull); Console.WriteLine("\nPress any key ...