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!

The resulting DataTable has a Columns collection where each item has a DefaultValue property. Isn't it filling that out properly? — Joachim Isaksson, Apr 25 '12 at 11:20
Not sure if it will help you, but I wrote a [SQL toolset](http://code.google.com/p/bsn-modulestore/) which can create an abstract syntax tree from all tables including their default values (look around the source starting [here](http://code.google.com/p/bsn-modulestore/source/browse/bsn.ModuleStore/Sql/DatabaseInventory.cs) if you're interested in how this is done). Licensed as LGPL. — Lucero, Apr 25 '12 at 11:22

score 7 · Answer 1 · answered May 01 '12 at 05:33

7

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';

answered May 01 '12 at 05:33

Adil

3,248
1
22
27

score 1 · Answer 2 · answered May 04 '12 at 20:38

1

Try the following query:

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

answered May 04 '12 at 20:38

mannu2050

403
3
11

Nudier Mena · Answer 3 · 2012-06-22T03:02:27.327

you should try something like this to retrieve tables schema.

public partial class Form1 : Form
{
    //create connectionString variable
    const string conString = @"Data Source=.\SQLEXPRESS; Initial Catalog=DBTest; Integrated Security=SSPI";

    SqlConnection cn = null;
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        this.getTableSchema();
    }

 //function to get the schemas from the Tables in MSSQLSERVER
    private void getTableSchema()
    {
        try
        {

            cn = new SqlConnection(conString);
            cn.Open();

            //call the getSchema Method of the SqlConnection Object passing in as a parameter the schema to retrieve
             DataTable dt = cn.GetSchema("tables");

           //Binded the retrieved data to a DataGridView to show the results.
            this.dataGridView1.DataSource = dt;


        }
        catch (Exception)
        {

            throw;
        }
    }


}

EDIT: Close quote on conString

score 0 · Answer 4 · answered May 06 '12 at 19:10

There is no way you can do that by using FillSchema. For details check link below http://msdn.microsoft.com/en-us/library/229sz0y5.aspx

INFORMATION_SCHEMA is the place where you should look. INFORMATION_SCHEMA contains many system views which can show you details of database structure. for example

INFORMATION_SCHEMA.TABLES : shows you list of tables in the database INFORMATION_SCHEMA.COLUMNS : shows you list of Columns and their attributes in all tables of the database. Please look at following location for more detail.

http://msdn.microsoft.com/en-us/library/ms186778.aspx

To get default value using query you can use following query:

SELECT *
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'YourTableName'

How to retrieve column default value from MS SQL data table

4 Answers4

Linked