Geek asks and answers

How to get default column value from MS SQL data table

I use DataAdapter.FillSchemato extract table schema from MS SQL. Unfortunately, this does not return the default value for columns. Is there a way to get this value as part of the schema in a quick and efficient way, since I need to examine hundreds of tables?

Thank!

+3
source share
4 answers

The default value is determined only when inserting a row.

Alternatively you can use Information_schema

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

you should try something like this to get the table 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 from conString

0
source

:

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

, FillSchema. . . http://msdn.microsoft.com/en-us/library/229sz0y5.aspx

INFORMATION_SCHEMA is the place where you should look. INFORMATION_SCHEMA contains many system views that can show you the details of the database structure. eg

INFORMATION_SCHEMA.TABLES: shows a list of tables in the database INFORMATION_SCHEMA.COLUMNS: shows a list of columns and their attributes in all tables in the database. Please see the following location for more details.

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

To get the default value using a query, you can use the following query:

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

More articles:


All Articles