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.

Answer 1

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.

Answer 2

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.

Answer 3

You can use the GetSchema method from the SqlConnection.

Answer 4

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

Answer 5

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);
}