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!

Peat

The resulting DataTable has a Columns collection where each item has a DefaultValue property. Isn't it filling that out properly? – Elsewhere

Not sure if it will help you, but I wrote a SQL toolset which can create an abstract syntax tree from all tables including their default values (look around the source starting here if you're interested in how this is done). Licensed as LGPL. – Lisle

That's the problem: that property is not filled. – Peat

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

Frater

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

Evetteevey

Answer 3

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

Coachman

Answer 4

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'

Amylopectin