Recipe 10.3 Retrieving Column Default Values from SQL Server

Problem

The DataColumn object exposes a Default property. While the FillSchema( ) method of the DataAdapter returns schema information, it does not include the default values for columns. You want to retrieve the default values of columns in a SQL Server table.

Solution

Use system stored procedures.

The sample code executes the system stored procedure sp_helpconstraint to get constraint information for the columns in the Orders table in the Northwind sample database. Column default values are identified and retrieved from the result set.

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

Example 10-3. File: ColumnDefaultsForm.cs

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

//  . . . 

StringBuilder result = new StringBuilder( );

// Fill the Orders table with schema and data.
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders",
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
DataTable ordersTable = new DataTable(ORDERS_TABLE);
da.FillSchema(ordersTable, SchemaType.Source);
da.Fill(ordersTable);

SqlConnection conn = new SqlConnection(
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);

// Command for system stored procedure returning constraints
SqlCommand cmd = new SqlCommand("sp_helpconstraint", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@objname",SqlDbType.NVarChar,776);
cmd.Parameters[0].Value = "Orders";
cmd.Parameters.Add("@nomsg",SqlDbType.VarChar,5);
cmd.Parameters[1].Value = "nomsg";

// Create a DataReader from the stored procedure.
conn.Open( );
SqlDataReader dr = cmd.ExecuteReader( );

// Iterate over the constraints records in the DataReader.
while(dr.Read( ))
{
    // Select the default value constraints only.
    String constraintType = dr["constraint_type"].ToString( );
    if (constraintType.StartsWith("DEFAULT"))
    {
        String constraintKeys = dr["constraint_keys"].ToString( );
        // Only strips single quotes for numeric default types
        // add necessary handling as required for nonnumeric defaults
        String defaultValue =
            constraintKeys.Substring(1, constraintKeys.Length - 2);

        String colName = constraintType.Substring(
            (constraintType.LastIndexOf("column") + 7));

        ordersTable.Columns[colName].DefaultValue = defaultValue;

        result.Append("Column: " + colName + "   Default: " +
            defaultValue + Environment.NewLine);
    }
}
dr.Close( );
conn.Close( );

resultTextBox.Text = result.ToString( );

Discussion

The default value for a column in SQL Server is stored as a DEFAULT constraint. The system stored procedure sp_helpconstraint returns information about all constraints on a table. The procedure takes one mandatory parameter that specifies the table for which to return the constraint information.

The first column that the stored procedure returns is called constraint_type. As its name suggests, it specifies the type of constraint using the following pattern {constraint_type} [on column {column_name}]. For example, the default constraint on the Freight column in the Orders table in the Northwind sample database has a constraint type of DEFAULT on column Freight.

In the solution, a result set is created from the system stored procedure sp_helpconstraint specifying the Orders table. The constraint_type column is examined for each row to determine whether it begins with the word DEFAULT indicating a default constraint. For default constraints, the column name is the string following the word column in the constraint_type column.

Once the default constraints have been identified, the overloaded constraint_keys column contains the default value for the column. The default value is surrounded by parentheses as well as delimiters for nonnumeric fields—for example, single quotes by default in SQL Server for dates and strings, and an additional prefix N in the case of Unicode strings. These delimiters need to be stripped from the value before it can be assigned to the DefaultValue property for the column.

For more information about the sp_helpconstraint system stored procedure, see Microsoft SQL Server Books Online.