Recipe 10.4 Determining the Length of Columns in a SQL Server Table

Problem

The FillSchema( ) method of the DataAdapter returns the correct length in the MaxLength property for string columns in a SQL Server database, but it returns -1 for the length of all other fields. You need to get the length of columns other than string type columns.

Solution

Use the system stored procedure sp_help.

The sample code executes a batch query to return all rows from both the Orders and Order Details tables in the Northwind sample database. The extended stored procedure sp_help is used to get the length, precision, and scale of all columns in both tables.

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

Example 10-4. File: ColumnSchemaForm.cs

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

//  . . . 

StringBuilder schemaInfo = new StringBuilder( );

// Create a batch query to retrieve order and details.
String sqlText = "select OrderID, CustomerID, EmployeeID, OrderDate, " +
    "RequiredDate, ShippedDate, ShipVia, Freight, ShipName, " +
    " ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry " +
    "FROM Orders;" +
    "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " +
    "FROM [Order Details];";

// Create the connection.
SqlConnection conn = new SqlConnection(
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);

// Create DataAdapter.
SqlDataAdapter da = new SqlDataAdapter(sqlText, conn);

// Add table mappings.
da.TableMappings.Add("Table", "Orders");
da.TableMappings.Add("Table1", "Order Details");

// Create the DataSet.
DataSet ds = new DataSet( );
// Fill the schema and data.
da.FillSchema(ds, SchemaType.Mapped);
da.Fill(ds);

// Iterate over the table collection in the DataSet.
foreach(DataTable dt in ds.Tables)
{
    schemaInfo.Append("TABLE: " + dt.TableName + Environment.NewLine);

    // Create the command to retrieve column information.
    cmd = new SqlCommand("sp_help", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@objname", SqlDbType.NVarChar, 776);
    cmd.Parameters[0].Value = dt.TableName;

    conn.Open( );
    // Create the DataReader from the command.
    SqlDataReader dr = cmd.ExecuteReader( );
    // Get the second result set containing column information.
    dr.NextResult( );

    Hashtable colInfo = new Hashtable( );
    // Iterate over the second result to retrieve column information.
    while(dr.Read( ))
    {
        colInfo.Add(dr["Column_name"].ToString( ),
            "Length = " + dr["Length"] +
            "; Precision = " + dr["Prec"] +
            "; Scale = " + dr["Scale"]);
    }
    dr.Close( );
    conn.Close( );

    // Iterate over the column collection in the table.
    foreach(DataColumn col in dt.Columns)
    {
        // Get column information.
        schemaInfo.Append("\tCOLUMN: " + col.ColumnName +
            Environment.NewLine);
        schemaInfo.Append("\tAllowDBNull: " + col.AllowDBNull +
            Environment.NewLine);
        schemaInfo.Append("\tAutoIncrement: " + col.AutoIncrement +
            Environment.NewLine);
        schemaInfo.Append("\tDataType: " + col.DataType +
            Environment.NewLine);
        schemaInfo.Append("\tMaxLength: " + col.MaxLength +
            Environment.NewLine);
        schemaInfo.Append("\tUnique: " + col.Unique +
            Environment.NewLine);
        schemaInfo.Append("\tOther: " + colInfo[col.ColumnName] +
            Environment.NewLine);
        schemaInfo.Append(Environment.NewLine);
    }
    schemaInfo.Append(Environment.NewLine);
}

resultTextBox.Text = schemaInfo.ToString( );

Discussion

The system stored procedure sp_help returns, in addition to the length of all fields, the precision and scale of columns as appropriate.

The sp_help system stored procedure takes one optional parameter. When this parameter is the name of a table object, the result set returns data including the length, precision, and scale of each column in columns named Length, Prec, and Scale.

The column length returned by the sp_help is in bytes, meaning that Unicode data types (nchar, nvarchar, and ntext) report double the value of the MaxLength property of the column object. In the example, the ShipCountry column of the Orders table reports MaxLength = 15 (stores a string up to 15 characters long) and Length = 30 (requires 30 characters to store the 15 Unicode characters).

For both the Orders and Order Details tables, the solution adds column length, precision, and scale data for each row in the sp_help result set to a Hashtable keyed on the column name. The code then iterates over the collection of columns in the table and displays the length, precision, and scale for each from the Hashtable. Information from the FillSchema( ) method of the DataAdapter is also included. The data type and nullable properties are available using both sp_help and FillSchema( ).

For more information about the sp_help system stored procedure, refer to Microsoft SQL Server Books Online.

The GetSchemaTable( ) method of the DataReader also returns all column lengths. The method returns a DataTable containing column metadata for a DataReader, where the ColumnSize column contains the lengths. For more information about the GetSchemaTable( ) method, see the discussion for Recipe 5.3.