删除数据库表中字段的描述
#region
/// <summary>
/// 删除数据库表中字段的描述
/// </summary>
public static void DelMS_Description()
{
string str = GetTables_SystemTable();
string[] st = str.Substring(1).Split(',');
if (st.Length > 0)
{
for (int i = 0; i < st.Length; i++)
{
string strsql = "select name from syscolumns where id=object_id('" + st[i] + "')";
DataSet ds = DbSQL.Query(strsql);
if (ds.Tables[0].Rows.Count > 0)
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
string colName = ds.Tables[0].Rows[j]["name"].ToString();
string strs = "SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '"+st[i]+"', 'column', '" + colName + "')";
DataSet dsx = Main.GetList(strs);
if (dsx.Tables[0].Rows.Count > 0)
{
SqlParameter[] parameters = {
new SqlParameter("@name","MS_Description"),
new SqlParameter("@level0type", "user"),
new SqlParameter("@level0name", "dbo"),
new SqlParameter("@level1type", "table"),
new SqlParameter("@level1name", st[i] ),
new SqlParameter("@level2type", "column"),
new SqlParameter("@level2name", colName)
};
DbSQL.RunProcedure("sp_dropextendedproperty", parameters);
}
}
}
}
}
}
private static string GetTables_SystemTable()
{
string st = string.Empty;
//打开连接
string strConnectionString = ConfigurationManager.ConnectionStrings["Logistic"].ConnectionString;
using (SqlConnection sqlcn = new SqlConnection(strConnectionString))
{
sqlcn.Open();
//使用信息架构视图
SqlCommand sqlcmd = new SqlCommand("SELECT OBJECT_NAME (id) FROM sysobjects WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0", sqlcn);
SqlDataReader dr = sqlcmd.ExecuteReader();
while (dr.Read())
{
st += "," + dr.GetString(0);
}
}
return st;
}
#endregion
