How do I retrieve DataColumn.DefaultValue from a Sql Table?

Question

Is there a way to determine a column's defaultvalue from the Sql Database using ado.net?

I tried using the SqlDataAdapter's FillSchema method:

 using (SqlDataAdapter adapter = new SqlDataAdapter()) {
    adapter.SelectCommand = myConnection.CreateCommand();
    adapter.SelectCommand.CommandType = CommandType.Text;
    adapter.SelectCommand.CommandText = "SELECT * FROM myTable";
    DataTable table = new DataTable();
    adapter.Fill(table);
    adapter.FillSchema(table, SchemaType.Mapped);
 }

When I inspect the DataColumns in the DataTable, I can determine if a column is an AutoIncrement, and can determine if it allows nulls using the AllowDBNull property. However, DefaultValue (for columns that I know have a default value) is always null.

I considered:

DataTable schemaTable = null;
using (SqlDataReader reader = adapter.SelectCommand.ExecuteReader(CommandBehavior.SchemaOnly)) {
   schemaTable = reader.GetSchemaTable();
   reader.Close();
}

but DefaultValue is not included in the schema.

So...how can I get a column's DefaultValue?

marc_s · Accepted Answer · 2009-10-16 20:40:21Z

4

Use this query to interrogate the INFORMATION_SCHEMA for the info you're looking for:

SELECT 
   TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM 
   INFORMATION_SCHEMA.COLUMNS
WHERE 
   TABLE_NAME = 'your table name' AND
   COLUMN_NAME = 'your column name'

Marc

answered Oct 16, 2009 at 20:40

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

mjv Over a year ago

I think this implies an SQL Server (MSSQL), 2005(?) or above server, right?

marc_s Over a year ago

No, any DB that supports the INFORMATION_SCHEMA should be supported. That's not just SQL Server - quite a few others support this as well. That's the big benefit of using INFORMATION_SCHEMA over SQL Server specific "sys.*" or "sysobjects" catalog views.

marc_s Over a year ago

As far as I know, at least MySQL, PostgreSQL and to a degree Oracle support INFORMATION_SCHEMA (not sure about IBM DB2)

mjv Over a year ago

@Marc Good to know. I always considered these metadata objects as implementation specific. Thks.

Ruben Over a year ago

Note that COLUMN_DEFAULT is not the actual value, but the expression to generate the default value. See my answer for more details.

|

Ruben · Accepted Answer · 2009-10-16 21:50:23Z

2

Not really. This is because the default value can be determined at the time a record is inserted (like a GETDATE() or NEW_ID()). So the value cannot be determined in advance.

The COLUMN_DEFAULT column of INFORMATION_SCHEMA.COLUMNS gives you not the actual default value, but a string representation of the code SQL Server and the likes will execute to generate the default value. See http://msdn.microsoft.com/en-us/library/ms188348.aspx.

Having said that, simple constant values can easily be deduced from such an expression.

answered Oct 16, 2009 at 21:50

Ruben

15.6k2 gold badges37 silver badges45 bronze badges

Collectives™ on Stack Overflow

How do I retrieve DataColumn.DefaultValue from a Sql Table?

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related