Determine a SQL Server Database Column’s Default from C#

Question

I need to find the default value of a column in a SQL Server 2005 or 2008 database from .Net.

My first attempt was to execute the stored procedure:

sp_help @objname

Where @objname is the table. Then in the seventh table returned (the constraints table), I was going to use the value in constraint_keys for the default constraints. When I run this query from SQL Server Management Studio it behaves as intended, but when I run it from C# code, the column constraint_keys was null (although all the other columns were populated).

My second attempt was to use:

SELECT name, [text]
FROM syscomments com
    INNER JOIN syscolumns col ON com.id = col.cdefault
WHERE col.id = object_id(@Table)
    AND col.cdefault > 0

Which also works fine in SQL Server Management Studio. When I run it from .Net, however, it returns no rows.

Additional:

Example .Net code (using Enterprise Libraries):

    private DataTable GetDefaults(string tablename string database)
    {            
        var db = DatabaseEL.Create(database);

        string sql = @"
            SELECT name, [text]
            FROM syscomments com
                INNER JOIN syscolumns col ON com.id = col.cdefault
            WHERE col.id = object_id(@Table)
                AND col.cdefault > 0";

        using (var command = db.GetSqlStringCommand(sql))
        {
            db.AddInParameter(command, "@Table", DbType.String, tablename);

            return db.ExecuteDataTable(command);
        }
    }

Note that I have now also tried each of the following from the linked similar question:

SELECT 
name, object_definition(default_object_id) AS default_value
FROM sys.columns
WHERE object_id = object_id(@Table)
AND default_object_id != 0

and

SELECT
sc.name AS column_name, 
    sm.text AS default_value
FROM sys.sysobjects so
    JOIN sys.syscolumns sc ON sc.id = so.id
    LEFT JOIN sys.syscomments SM ON sm.id = sc.cdefault
WHERE so.xtype = 'U' 
AND SO.name = @Table
AND sm.text IS NOT NULL

They both worked in SSMS, but in .Net, the former had nulls in the default_value column, and the latter had no rows.

Sounds like a duplicate of stackoverflow.com/q/3817885/559467. That gives you the value in a SQL statement, which can be run from C#. If it does not have a default value, it will not return anything. — D.N.
– D.N., Commented Feb 23, 2011 at 17:43
Could you post the .Net code ? I doesn't make sense that it works from Management studio and not from .Net. — Sem Vanmeenen
– Sem Vanmeenen, Commented Feb 23, 2011 at 17:46

Paul · Accepted Answer · 2011-02-24 10:00:21Z

5

The answer seems to be to grant the user access to the db_ddladmin role. While db_datareader and db_datawriter are enough to run the necessary queries, they all return NULLs for default values if the user has no access to db_ddladmin.

The reason it worked for me from SQL Server Management Studio, of course, was because I was logged in as an administrative user.

answered Feb 24, 2011 at 10:00

Paul

16.9k14 gold badges44 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andy White · Accepted Answer · 2011-02-23 18:02:16Z

1

One other SQL query you could try is:

select * from information_schema.columns
where table_catalog = 'MyDatabase'
and table_schema = 'dbo' -- or your schema
and table_name = 'MyTable';

The COLUMN_HASDEFAULT and COLUMN_DEFAULT values should give you the info you're looking for.

answered Feb 23, 2011 at 18:02

Andy White

88.8k48 gold badges178 silver badges212 bronze badges

2 Comments

Paul Over a year ago

<code>select * from information_schema.columns</code> returns no rows in SSMS for me in SQL Server 2005.

Andy White Over a year ago

Hmm, interesting. Not sure what would cause that, but I guess this method won't work for you in that case.

Tom Brothers · Accepted Answer · 2011-02-23 18:02:22Z

1

You can use the GetSchema method from the SqlConnection.

enter image description here

answered Feb 23, 2011 at 18:02

Tom Brothers

6,0071 gold badge24 silver badges17 bronze badges

2 Comments

Dustin Davis Over a year ago

nice, this seems a bit better than my method.

Paul Over a year ago

That’s elegant, though still doesn’t work for me. The table is populated with the columns, except the COLUMN_DEFAULT column which is entirely null.

Dustin Davis · Accepted Answer · 2011-02-23 17:54:34Z

0

Use SqlCommand to do it. try:

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
        cmd.CommandText = "MyTable";
        cmd.CommandType = System.Data.CommandType.TableDirect;
        cmd.ExecuteReader(System.Data.CommandBehavior.SchemaOnly);

answered Feb 23, 2011 at 17:54

Dustin Davis

14.6k14 gold badges66 silver badges123 bronze badges

2 Comments

Paul Over a year ago

I don’t think that’s supported in .Net 3.5. I got the exception: "The CommandType enumeration value, 512, is not supported by the .Net Framework SqlClient Data Provider.".

Dustin Davis Over a year ago

Try just a sql query then cmd.CommandText = "select top 1 * from MyTable"; cmd.CommandType = Text;

marc_s · Accepted Answer · 2011-02-24 05:28:17Z

0

With this straight ADO.NET code, you can definitely retrieve the information needed, no problem:

public class ColumnAndDefault
{
    public string ColumnName { get; set; }
    public string DefaultDefinition { get; set; }
}

public class GetDefaultColumnValues
{
    public List<ColumnAndDefault> GetDefaultsForTable(string tablename, string connectionString)
    {
        List<ColumnAndDefault> results = new List<ColumnAndDefault>();

        string query =
            string.Format("SELECT name, object_definition(default_object_id) " +
            "FROM sys.columns " +
            "WHERE default_object_id <> 0 AND object_id = OBJECT_ID('{0}')", tablename);

        using(SqlConnection _con = new SqlConnection(connectionString))
        using(SqlCommand _cmd = new SqlCommand(query, _con))
        {
            _con.Open();

            using(SqlDataReader rdr = _cmd.ExecuteReader())
            {
                while(rdr.Read())
                {
                    if(!rdr.IsDBNull(0) && !rdr.IsDBNull(1))
                    {
                        string colName = rdr.GetString(0);
                        string defValue = rdr.GetString(1);

                        results.Add(
                           new ColumnAndDefault { ColumnName = colName, 
                                                  DefaultDefinition = defValue });
                    }
                }

                rdr.Close();
            }

            _con.Close();
        }


        return results;
    }
}

You can call this from your app like this:

static void Main(string[] args)
{
   // grab that from a config file in your real app!
   string connectionString = "server=(local);database=YourDB;Integrated Security=SSPI;";

   GetDefaultColumnValues getter = new GetDefaultColumnValues();

   List<ColumnAndDefault> columnDefaults = 
      getter.GetDefaultsForTable("YourTableNameHere", connectionString);
}

edited Feb 24, 2011 at 5:28

answered Feb 23, 2011 at 21:57

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

3 Comments

Paul Over a year ago

This didn’t work either. Got a SqlNullValueException on the line <code>string defValue = rdr.GetString(1);</code>.

marc_s Over a year ago

@Paul: there's definitely something fishy in your system then..... anyway: updated my answer to check for NULL and avoid stepping into the NULL case - try again, please.

Paul Over a year ago

Yes, I agree. I think it is something peculiar in my system. I might have to resort to turning off triggers, inserting an empty row in a transaction, selecting the default values, and rolling back :-S

Collectives™ on Stack Overflow

Determine a SQL Server Database Column’s Default from C#

5 Answers 5

Comments

2 Comments

2 Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

2 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related