How to retrieve column default value from MS SQL data table

Question

I am using DataAdapter.FillSchema to retrieve tables' schema from MS SQL. Unfortunately this doesn't return the default value for the columns. Is there a way to retrieve this value as part of the schema in a fast and efficient way as I need to examine hundreds of tables?

Thanks!

Answer 1

Default value is determined at the time of row insertion only.

As an alternative, you can utilize Information_schema

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
FROM AdventureWorks2012.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Person';

Answer 2

Try the following query:

SELECT object_definition(default_object_id) AS definition
FROM   sys.columns
WHERE  name      ='ColumnName'
AND    object_id = object_id('TableName')