public string ScriptIndexFK(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, bool allBut, CurrSrc CSrc, CurrTar CTar) { // not really foreign key but ALL index definitions of a table StringBuilder sbDrop = new StringBuilder(""); StringBuilder sbCrea = new StringBuilder(""); string strIx; string strFK; DataTable dtIx; DataTable dtFK; DataTable dt; Func <string, bool> conditional = (tblName) => { // always unconditional because of the way defined // for this is always called with allBut thus anything not in the but needs to have the index refreshed(change in def by developer) // for the BUT(i.e. data + table) the table would be removed and recreate thus index would needs to be recreated return(false); //if (allBut) //{ // return exceptTables.Contains(tblName); //} //else //{ // return !exceptTables.Contains(tblName); //} }; dt = GetTables(SrcDbProviderCd, IsFrSource, false, false, CSrc, CTar); foreach (DataRow dr2 in dt.Rows) { int idxCount = 0; int fkIdxCount = 0; using (DbScriptAccessBase dac = GetDbScriptAccess()) { dtIx = dac.GetData("EXEC sp_helpindex " + dr2["tbName"].ToString(), IsFrSource, CSrc, CTar); } bool inConditionalBlock = false; bool hasContent = false; string includeColumns = null; string filter = null; foreach (DataRow drIx in dtIx.Rows) { if (drIx[0].ToString().Substring(0, 3) != "PK_" && !dr2["tbName"].ToString().Contains("sysdiagrams") // SQL Server generated, not always available on target ) // No primary key. { using (DbScriptAccessBase dac = GetDbScriptAccess()) { DataTable dtInclude = dac.GetData(@"SELECT IndexName = i.Name, ColName = c.Name, TableName = t.name, FilterDef = i.filter_definition FROM sys.indexes i INNER JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id INNER JOIN sys.tables t on t.object_id = c.object_id WHERE ic.is_included_column = 1 AND t.name = '" + dr2["tbName"].ToString() + @"' AND i.name = '" + drIx[0].ToString() + "'", IsFrSource, CSrc, CTar); includeColumns = string.Join(",", dtInclude.AsEnumerable().Select(dr => dr["ColName"]).ToArray()); filter = dtInclude.AsEnumerable() .Where(dr => !string.IsNullOrEmpty(dr["FilterDef"].ToString())) .Select(dr => dr["FilterDef"].ToString()).FirstOrDefault(); } if (conditional(dr2["tbName"].ToString()) && !inConditionalBlock) { // re-create table only if it is not there sbCrea.Append("IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr2["tbName"].ToString() + "') and type='" + "U" + "')\r\n"); sbCrea.Append("BEGIN\r\n"); inConditionalBlock = true; } hasContent = true; sbDrop.Append("IF EXISTS (SELECT i.name FROM sysindexes i INNER JOIN sysobjects o ON i.id = o.id WHERE i.name = '" + drIx[0].ToString() + "' AND o.name = '" + dr2["tbName"].ToString() + "')\r\n"); if (drIx[1].ToString().LastIndexOf("unique") > 0 && 1 != 1) { // some unique key was created as constraint and not index which should be drop as contstrain // though no way to tell from development thus disabled // can only be done manually and re-run to re-create as unique index !!! sbDrop.Append(" ALTER TABLE dbo." + dr2["tbName"].ToString() + " DROP CONSTRAINT " + drIx[0].ToString() + " \r\n\r\n"); } else { sbDrop.Append(" DROP INDEX " + dr2["tbName"].ToString() + "." + drIx[0].ToString() + " \r\n\r\n"); } strIx = "CREATE "; if (drIx[1].ToString().LastIndexOf("unique") > 0) { strIx += " UNIQUE "; } // there are case where non-primary index is clustered ! if (drIx[1].ToString().LastIndexOf("nonclustered") < 0) { strIx += " CLUSTERED "; } strIx += "INDEX " + drIx[0].ToString() + " ON " + dr2["tbName"].ToString() + "("; strIx += drIx[2].ToString() + ")" /* add covering columns */ + (string.IsNullOrEmpty(includeColumns) ? "" : " INCLUDE (" + includeColumns + ")") /* add filter clause */ + (string.IsNullOrEmpty(filter) ? "" : " WHERE (" + filter + ")") + "\r\n"; //replace (-) if (idxCount > 0) { sbCrea.Append("GO\r\n\r\n"); idxCount = 0; } sbCrea.Append(Regex.Replace(sbDrop.Append(strIx).ToString(), "[(]-[)]", " DESC")); idxCount += 1; sbDrop.Remove(0, sbDrop.Length); //clear the drop statement } } using (DbScriptAccessBase dac = GetDbScriptAccess()) { dtFK = dac.GetFKInfo(dr2["tbName"].ToString(), IsFrSource, CSrc, CTar); } foreach (DataRow drFK in dtFK.Rows) { if (conditional(dr2["tbName"].ToString()) && !inConditionalBlock) { // re-create table only if it is not there sbCrea.Append("IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr2["tbName"].ToString() + "') and type='" + "U" + "')\r\n"); sbCrea.Append("BEGIN\r\n"); inConditionalBlock = true; } hasContent = true; sbDrop.Append("if exists (select * from dbo.sysobjects where id = object_id(N'dbo." + drFK["cName"].ToString() + "') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)\r\n"); sbDrop.Append("ALTER TABLE dbo." + dr2["tbName"].ToString() + " DROP CONSTRAINT " + drFK["cName"].ToString() + " \r\n\r\n"); strFK = "\r\nALTER TABLE " + dr2["tbName"].ToString() + " ADD\nCONSTRAINT " + drFK["cName"].ToString() + " FOREIGN KEY\r\n("; for (int i = 1; i <= (int)drFK["cColCount"]; i++) { strFK += "\r\n" + drFK["cKeyCol" + i.ToString().Trim()].ToString() + ","; } strFK = strFK.Substring(0, strFK.Length - 1); strFK += ")\r\n REFERENCES " + drFK["cRefTable"].ToString() + "\r\n("; for (int i = 1; i <= (int)drFK["cColCount"]; i++) { strFK += "\r\n" + drFK["cRefCol" + i.ToString().Trim()].ToString() + ","; } strFK = strFK.Substring(0, strFK.Length - 1); strFK = sbDrop.Append(strFK).ToString(); sbDrop.Remove(0, sbDrop.Length); //clear the drop statement strFK += ")\r\n"; if (idxCount > 0) { sbCrea.Append("GO\r\n\r\n"); idxCount = 0; } else if (fkIdxCount > 0) { sbCrea.Append("GO\r\n\r\n"); fkIdxCount = 0; } sbCrea.Append(strFK); fkIdxCount += 1; } if (conditional(dr2["tbName"].ToString()) && inConditionalBlock) { // re-create table only if it is not there sbCrea.Append("END\r\n"); } if (hasContent) { sbCrea.Append("GO\r\n\r\n"); } } return(sbCrea.ToString()); }
public string ScriptIndexFK(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, bool allBut, CurrSrc CSrc, CurrTar CTar) { StringBuilder sbDrop = new StringBuilder(""); StringBuilder sbCrea = new StringBuilder(""); string strIx; string strFK; DataTable dtIx; DataTable dtFK; DataTable dt; Func <string, bool> conditional = (tblName) => { // always unconditional because of the way defined // for this is always called with allBut thus anything not in the but needs to have the index refreshed(change in def by developer) // for the BUT(i.e. data + table) the table would be removed and recreate thus index would needs to be recreated return(false); //if (allBut) //{ // return exceptTables.Contains(tblName); //} //else //{ // return !exceptTables.Contains(tblName); //} }; dt = GetTables(SrcDbProviderCd, IsFrSource, false, false, CSrc, CTar); foreach (DataRow dr2 in dt.Rows) { using (DbScriptAccessBase dac = GetDbScriptAccess()) { dtIx = dac.GetData("EXEC sp_helpindex " + dr2["tbName"].ToString(), IsFrSource, CSrc, CTar); } bool inConditionalBlock = false; bool hasContent = false; foreach (DataRow drIx in dtIx.Rows) { if (drIx[0].ToString().Substring(0, 3) != "PK_" && !dr2["tbName"].ToString().Contains("sysdiagrams") // SQL Server generated, not always available on target ) // No primary key. { if (conditional(dr2["tbName"].ToString()) && !inConditionalBlock) { // re-create table only if it is not there sbCrea.Append("IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr2["tbName"].ToString() + "') and type='" + "U" + "')\r\n"); sbCrea.Append("BEGIN\r\n"); inConditionalBlock = true; } hasContent = true; sbDrop.Append("IF EXISTS (SELECT i.name FROM sysindexes i INNER JOIN sysobjects o ON i.id = o.id WHERE i.name = '" + drIx[0].ToString() + "' AND o.name = '" + dr2["tbName"].ToString() + "')\r\n"); sbDrop.Append(" DROP INDEX " + dr2["tbName"].ToString() + "." + drIx[0].ToString() + " \r\n\r\n"); strIx = "CREATE "; if (drIx[1].ToString().LastIndexOf("unique") > 0) { strIx += " UNIQUE "; } strIx += "INDEX " + drIx[0].ToString() + " ON " + dr2["tbName"].ToString() + "("; strIx += drIx[2].ToString() + ")\r\n"; //replace (-) sbCrea.Append(Regex.Replace(sbDrop.Append(strIx).ToString(), "[(]-[)]", " DESC")); sbDrop.Remove(0, sbDrop.Length); //clear the drop statement } } using (DbScriptAccessBase dac = GetDbScriptAccess()) { dtFK = dac.GetFKInfo(dr2["tbName"].ToString(), IsFrSource, CSrc, CTar); } foreach (DataRow drFK in dtFK.Rows) { if (conditional(dr2["tbName"].ToString()) && !inConditionalBlock) { // re-create table only if it is not there sbCrea.Append("IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr2["tbName"].ToString() + "') and type='" + "U" + "')\r\n"); sbCrea.Append("BEGIN\r\n"); inConditionalBlock = true; } hasContent = true; sbDrop.Append("if exists (select * from dbo.sysobjects where id = object_id(N'dbo." + drFK["cName"].ToString() + "') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)\r\n"); sbDrop.Append("ALTER TABLE dbo." + dr2["tbName"].ToString() + " DROP CONSTRAINT " + drFK["cName"].ToString() + " \r\n\r\n"); strFK = "\r\nALTER TABLE " + dr2["tbName"].ToString() + " ADD\nCONSTRAINT " + drFK["cName"].ToString() + " FOREIGN KEY\r\n("; for (int i = 1; i <= (int)drFK["cColCount"]; i++) { strFK += "\r\n" + drFK["cKeyCol" + i.ToString().Trim()].ToString() + ","; } strFK = strFK.Substring(0, strFK.Length - 1); strFK += ")\r\n REFERENCES " + drFK["cRefTable"].ToString() + "\r\n("; for (int i = 1; i <= (int)drFK["cColCount"]; i++) { strFK += "\r\n" + drFK["cRefCol" + i.ToString().Trim()].ToString() + ","; } strFK = strFK.Substring(0, strFK.Length - 1); strFK = sbDrop.Append(strFK).ToString(); sbDrop.Remove(0, sbDrop.Length); //clear the drop statement strFK += ")\r\n"; sbCrea.Append(strFK); } if (conditional(dr2["tbName"].ToString()) && inConditionalBlock) { // re-create table only if it is not there sbCrea.Append("END\r\n"); } if (hasContent) { sbCrea.Append("GO\r\n\r\n"); } } return(sbCrea.ToString()); }