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