Geek Answers Handbook

How to get DataColumn.DefaultValue from Sql table?

Is there a way to determine the default value of a column from an Sql database using ado.net?

I tried using the SqlDataAdapter 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 check the DataColumns in a DataTable , I can determine if the column is AutoIncrement , and can determine if it allows null using the AllowDBNull property. However, DefaultValue (for columns that I know have a default value) is always null .

I thought:

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

but DefaultValue not included in the schema.

So ... how can I get the DefaultValue column?

+4
source share
2 answers

Use this query to poll INFORMATION_SCHEMA for the information you are 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'

Mark

+4
source

Not really. This is because a default value can be specified during insertion of a record (for example, GETDATE() or NEW_ID() ). Therefore, the value cannot be determined in advance.

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

Having said that, simple simple meanings are easy to deduce from such an expression.

+2
source

More articles:


All Articles