从c#确定SQL Server数据库列的默认值

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

我需要从。net找到SQL Server 2005或2008数据库中列的默认值。

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

其中@objname是表。然后在返回的第7个表(约束表)中，我将使用constraint_keys中的值作为默认约束。当我从SQL Server Management Studio运行这个查询时，它的行为符合预期，但是当我从c#代码运行它时，列constraint_keys为null(尽管填充了所有其他列)。

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.

在SQL Server Management Studio中也可以很好地工作。然而，当我从。net运行它时，它不会返回任何行。

Additional:

附加:

Example .Net code (using Enterprise Libraries):

示例。net代码(使用企业库):

    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.

它们都在SSMS中工作，但是在。net中，前者在default_value列中有null，而后者没有行。

5 个解决方案

#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.

答案似乎是授予用户对db_ddladmin角色的访问权。虽然db_datareader和db_datawriter足够运行必要的查询，但是如果用户没有访问db_ddladmin的权限，它们都会返回默认值的null。

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

从SQL Server Management Studio中为我工作的原因当然是我作为一个管理用户登录了。

#2

One other SQL query you could try is:

您可以尝试的另一个SQL查询是:

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.

COLUMN_HASDEFAULT和COLUMN_DEFAULT值应该提供您正在寻找的信息。

#3

You can use the GetSchema method from the SqlConnection.

您可以使用来自SqlConnection的GetSchema方法。

#4

Use SqlCommand to do it. try:

使用SqlCommand完成。试一试:

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

#5

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

直ADO。NET代码，您绝对可以检索所需的信息，没有问题:

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