/// <summary> /// /// </summary> /// <param name="database"></param> /// <param name="table"></param> void dropTableIndex(EntityDB.IDatabaseService database, string table, IndexInfo[] dontDelIndexes) { table = table.ToLower(); List <string> toDelIndexes = new List <string>(); using (var sp_helpResult = database.SelectDataSet("sp_help [" + table + "]")) { foreach (var dtable in sp_helpResult.Tables) { if (dtable.Columns.Any(m => m.ColumnName == "index_keys")) { foreach (WayDataRow drow in dtable.Rows) { string existColumnString = drow["index_keys"].ToString(); string indexName = drow["index_name"].ToString(); string index_description = drow["index_description"].ToString(); if (index_description.Contains("primary key") == false) { if (dontDelIndexes.Count(m => string.Equals(m.Name, indexName, StringComparison.CurrentCultureIgnoreCase)) == 0) { toDelIndexes.Add(indexName); } } } break; } } } foreach (string indexName in toDelIndexes) { database.ExecSqlString("drop index " + indexName + " on [" + table + "]"); } }
void deletecolumn(EntityDB.IDatabaseService database, string table, string column) { table = table.ToLower(); column = column.ToLower(); #region delete using (var sp_helpResult = database.SelectDataSet("sp_help [" + table + "]")) { foreach (var dtable in sp_helpResult.Tables) { if (dtable.Columns.Any(m => m.ColumnName == "constraint_name")) { var query = dtable.Rows.Where(m => m["constraint_keys"].ToSafeString().ToLower() == column.ToLower()); if (query.Count() > 0) { database.ExecSqlString("alter table [" + table + "] drop constraint " + query.First()["constraint_name"]); } break; } } //删除默认值 foreach (var dtable in sp_helpResult.Tables) { if (dtable.Columns.Any(m => m.ColumnName == "constraint_name")) { var query = from m in dtable.Rows where ((string)m["constraint_type"]).ToLower().EndsWith(" " + column.ToLower()) && ((string)m["constraint_type"]).ToLower().StartsWith("default on ") select m; if (query.Count() > 0) { database.ExecSqlString("alter table [" + table + "] drop constraint " + query.First()["constraint_name"]); } break; } } } #endregion database.ExecSqlString("ALTER TABLE [" + table + "] DROP COLUMN [" + column + "]"); }
/// <summary> /// 删除table的所有包含字段的索引 /// </summary> /// <param name="database"></param> /// <param name="table"></param> /// <param name="columnNames">包含的字段</param> void dropTableAllUniqueIndexWithColumns(EntityDB.IDatabaseService database, string table, List <string> columnNames) { table = table.ToLower(); List <string> toDelIndexes = new List <string>(); using (var sp_helpResult = database.SelectDataSet("sp_help [" + table + "]")) { foreach (var dtable in sp_helpResult.Tables) { if (dtable.Columns.Any(m => m.ColumnName == "index_keys")) { foreach (var drow in dtable.Rows) { string existColumnString = drow["index_keys"].ToString(); string indexName = drow["index_name"].ToString(); if (indexName.StartsWith("IDX_")) { foreach (string column in columnNames) { if (Regex.IsMatch(existColumnString, @"\b(" + column + @")\b", RegexOptions.IgnoreCase)) { toDelIndexes.Add(indexName); break; } } } } break; } } } foreach (string indexName in toDelIndexes) { database.ExecSqlString("drop index " + indexName + " on [" + table + "]"); } }
/// <summary> /// 返回没有变化的索引 /// </summary> /// <param name="database"></param> /// <param name="tablename"></param> /// <param name="idxConfigs"></param> /// <returns></returns> List <IndexInfo> checkIfIdxChanged(EntityDB.IDatabaseService database, string tablename, IndexInfo[] idxConfigs) { tablename = tablename.ToLower(); List <IndexInfo> result = new List <IndexInfo>(); List <IndexInfo> existKeys = new List <IndexInfo>(); using (var sp_helpResult = database.SelectDataSet("sp_help [" + tablename + "]")) { foreach (var dtable in sp_helpResult.Tables) { if (dtable.Columns.Any(m => m.ColumnName == "index_keys")) { foreach (WayDataRow drow in dtable.Rows) { string existColumnString = drow["index_keys"].ToString(); string indexName = drow["index_name"].ToString(); string index_description = drow["index_description"].ToString(); if (index_description.Contains("primary key") == false) { //去除空格 string flag = existColumnString.Split(',').ToSplitString(); string dbname = flag.Split(',').OrderBy(m => m).ToArray().ToSplitString().ToLower(); //再排序,不要在去除空格之前排序 existKeys.Add(new IndexInfo { Name = indexName, IsUnique = index_description.Contains("unique"), IsClustered = index_description.Contains("clustered") && !index_description.Contains("nonclustered"), ColumnNames = new string[] { dbname }, }); } else { if (idxConfigs.Count(m => m.IsClustered) > 0) { //去除主键 database.ExecSqlString("alter table [" + tablename + "] drop constraint " + indexName); //设为主键 string flag = existColumnString.Split(',').ToSplitString(); string ppname = flag.Split(',').OrderBy(m => m).ToArray().ToSplitString(",", "[{0}]").ToLower(); database.ExecSqlString("alter table [" + tablename + "] add constraint " + indexName + " primary key NONCLUSTERED (" + ppname + ")"); } } } break; } } } foreach (var nowConfigItem in idxConfigs) { string myname = nowConfigItem.ColumnNames.OrderBy(m => m).ToArray().ToSplitString().ToLower(); var fined = existKeys.FirstOrDefault(m => m.IsUnique == nowConfigItem.IsUnique && m.IsClustered == nowConfigItem.IsClustered && m.ColumnNames[0].ToLower() == myname); if (fined != null) { nowConfigItem.Name = fined.Name; result.Add(nowConfigItem); } } return(result); }