public DataTable GetFKeys(string dbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
        {
            using (DbScriptAccessBase dac = GetDbScriptAccess())
            {
                switch (dbProviderCd)
                {
                case "M":
                    return(dac.GetData(@"
select so1.name as fkName
        , so2.name as tbName 
        , soref.name as refTbName 
from sysreferences sr 
inner join sysobjects so1 on sr.constid = so1.id  
inner join sysobjects so2 on sr.fkeyid = so2.id
inner join sysobjects soref on soref.id = rkeyid   
ORDER BY so1.name"
                                       , IsFrSource, CSrc, CTar));

                case "S":
                    return(dac.GetData("select so1.name as fkName, so2.name as tbName from sysreferences sr inner join sysobjects so1 on sr.constrid = so1.id  inner join sysobjects so2 on sr.tableid = so2.id ORDER BY so1.name", IsFrSource, CSrc, CTar));

                default:
                    ApplicationAssert.CheckCondition(false, "DbScript.GetFKeys()", "Data Tier", "Data Provider Code \"" + dbProviderCd + "\" not recognized. Please rectify and try again.");
                    return(null);
                }
            }
        }
 public DataTable GetSps(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
 {
     using (DbScriptAccessBase dac = GetDbScriptAccess())
     {
         if (SrcDbProviderCd == "M" && TarDbProviderCd == "M")                   // FN: Functions returning scalar; TF: Functions returning result set.; IF inline table function
         {
             return(dac.GetData("select name, type from sysobjects where type IN ('P','FN','TF','IF') and substring(name,1,3) <> 'dt_' order by case when type = 'P' then type else 'F' end, name", IsFrSource, CSrc, CTar));
         }
         else
         {
             return(dac.GetData("select name, type from sysobjects where type='P' and substring(name,1,3) <> 'dt_'", IsFrSource, CSrc, CTar));
         }
     }
 }
 public string EncryptSProcedures(string SrcDbProviderCd, string TarDbProviderCd, string ss, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
 {
     if (TarDbProviderCd == "S")
     {
         StringBuilder sbCrea = new StringBuilder(ss);
         StringBuilder sbProc = new StringBuilder("");
         DataTable     dtSp;
         DataTable     dt = GetSps(SrcDbProviderCd, TarDbProviderCd, IsFrSource, CSrc, CTar);
         foreach (DataRow dr in dt.Rows)
         {
             using (DbScriptAccessBase dac = GetDbScriptAccess())
             {
                 dtSp = dac.GetData("EXEC sp_helptext " + dr[0].ToString(), IsFrSource, CSrc, CTar);
             }
             foreach (DataRow dr2 in dtSp.Rows)
             {
                 sbProc.Append(dr2[0].ToString());
             }
             if (!sbProc.ToString().Equals(""))
             {
                 sbProc.Remove(0, sbProc.Length);
                 sbCrea.Append("sp_hidetext " + dr[0].ToString() + " \r\nGO\r\n");
             }
         }
         return(sbCrea.ToString());
     }
     else                // TarDbProviderCd == "M"
     {
         return(Regex.Replace(ss, @"(?i)([^'])(/[*])\s*WITH\s+ENCRYPTION\s*([*]/)", "$1WITH ENCRYPTION"));
     }
 }
        public DataTable GetTables(string dbProviderCd, bool IsFrSource, bool IsInExempt, bool IsDataExempt, CurrSrc CSrc, CurrTar CTar)
        {
            string sInClause = "";
            string sTyClause = "type = 'U'";
            string sExempt   = sTablesExempt;

            if (bNewApp && !IsDataExempt)
            {
                sExempt = string.Empty;
            }                                                                           // bcp for new robot, app, etc.
            // always output full list unless it is about data. table structure MUST BE HANDLED at script generation level for excempt
            if (sExempt != string.Empty && IsDataExempt)
            {
                if (IsInExempt)                 //Both tables and views for bcp only:
                {
                    sInClause = " AND so.name in " + sExempt; sTyClause = "type in ('U','V')";
                }
                else
                {
                    sInClause = " AND so.name not in " + sExempt;
                }
            }
            using (DbScriptAccessBase dac = GetDbScriptAccess())
            {
                return(dac.GetData(@"
                        SELECT so.name AS tbName
                            ,(SELECT 1 FROM dbo.syscolumns WHERE id=so.id AND (status & 128) = 128) AS hasIdentity 
                        FROM dbo.sysobjects so 
                        WHERE " + sTyClause + " AND name <> 'dtproperties'" + sInClause
                                   + " ORDER BY so.name"
                                   , IsFrSource, CSrc, CTar));
            }
        }
        public DataTable GetViews(string dbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
        {
            using (DbScriptAccessBase dac = GetDbScriptAccess())
            {
                switch (dbProviderCd)
                {
                case "M":
                    return(dac.GetData("SELECT name FROM sysobjects WHERE type='V' AND OBJECTPROPERTY(id,'IsMSShipped') = 0 ORDER BY name", IsFrSource, CSrc, CTar));

                case "S":
                    return(dac.GetData("SELECT name FROM sysobjects WHERE type='V' ORDER BY name", IsFrSource, CSrc, CTar));

                default:
                    ApplicationAssert.CheckCondition(false, "DbScript.GetViews()", "Data Tier", "Data Provider Code \"" + dbProviderCd + "\" not recognized. Please rectify and try again.");
                    return(null);
                }
            }
        }
        public string ScriptCreaSp(string SpName, string SpType, string SrcDbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
        {
            StringBuilder sbCrea = new StringBuilder("");
            DataTable     dtSp;

            using (DbScriptAccessBase dac = GetDbScriptAccess())
            {
                try
                {
                    if (SrcDbProviderCd == "S")
                    {
                        dtSp = dac.GetDataSet("EXEC sp_helptext " + SpName, IsFrSource, CSrc, CTar).Tables[1];
                    }
                    else
                    {
                        dtSp = dac.GetData("EXEC sp_helptext " + SpName, IsFrSource, CSrc, CTar);
                    }
                }
                catch (Exception e)
                {
                    if (e.Message.IndexOf("hidden") < 0)
                    {
                        throw new Exception(e.Message);
                    }
                    else
                    {
                        dtSp = null;
                    }
                }
            }
            if (dtSp != null)
            {
                foreach (DataRow dr2 in dtSp.Rows)
                {
                    sbCrea.Append(dr2[0].ToString());
                }
            }
            return(Regex.Replace(sbCrea.ToString(), @"(?i)([a-zA-Z0-9])(\.{2})([a-zA-Z])", "$1.dbo.$3"));
        }
        public string ScriptView(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, CurrSrc CSrc, CurrTar CTar)
        {
            StringBuilder sbCrea = new StringBuilder("");
            StringBuilder sbView = new StringBuilder("");
            DataTable     dtVw;
            DataTable     dt;

            dt = GetViews(SrcDbProviderCd, IsFrSource, CSrc, CTar);
            foreach (DataRow dr in dt.Rows)
            {
                using (DbScriptAccessBase dac = GetDbScriptAccess())
                {
                    dtVw = dac.GetData("EXEC sp_helptext " + dr[0].ToString(), IsFrSource, CSrc, CTar);
                }
                foreach (DataRow dr2 in dtVw.Rows)
                {
                    sbView.Append(dr2[0].ToString());
                }
                if (!sbView.ToString().Equals(""))
                {
                    Regex rx = new Regex("(CREATE VIEW)(\\s+.*)((\\[)?" + dr[0].ToString() + "(\\])?)(.*\\s+AS)", RegexOptions.IgnoreCase);
                    //sbCrea.Append("if exists (select * from dbo.sysobjects where id = object_id(N'dbo." + dr[0].ToString() + "') and OBJECTPROPERTY(id, N'IsView') = 1)\r\n");
                    //sbCrea.Append("drop view dbo." + dr[0].ToString() + "\r\n");
                    //sbCrea.Append("GO\r\n");
                    sbCrea.Append("if not exists (select * from dbo.sysobjects where id = object_id(N'dbo." + dr[0].ToString() + "') and OBJECTPROPERTY(id, N'IsView') = 1)\r\n");
                    //sbCrea.Append("drop view dbo." + dr[0].ToString() + "\r\n");
                    sbCrea.Append("EXEC('CREATE VIEW dbo." + dr[0].ToString() + " AS SELECT DUMMY=1')\r\n");
                    sbCrea.Append("GO\r\n");
                    string ss = sbView.ToString().Trim(new char[] { ' ', '\r', '\n' });
                    ss = rx.Replace(ss, (m) => {
                        return("ALTER VIEW" + m.Groups[2].Value + m.Groups[3].Value + m.Groups[6]);
                    });
                    sbCrea.Append(ss);
                    sbView.Remove(0, sbView.Length);
                    sbCrea.Append("\r\nGO\r\n");
                }
            }
            return(sbCrea.ToString());
        }
        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 ScriptClearDb(string SrcDbProviderCd, string TarDbProviderCd, bool bTable, bool bData, bool bIndex, bool bView, bool bSp, CurrSrc CSrc, CurrTar CTar)
        {
            StringBuilder sb = new StringBuilder("");
            DataTable     dt;

            if (bSp)
            {
                dt = GetSps(SrcDbProviderCd, TarDbProviderCd, false, CSrc, CTar);                 //Drop  Target Stored Procedures
                foreach (DataRow dr in dt.Rows)
                {
                    sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr[0].ToString() + "') AND type='" + dr[1].ToString().Trim() + "')\r\n");
                    if (dr[1].ToString() == "FN")
                    {
                        sb.Append("DROP FUNCTION dbo." + dr[0].ToString() + "\r\n");
                    }
                    else
                    {
                        sb.Append("DROP PROCEDURE dbo." + dr[0].ToString() + "\r\n");
                    }
                    sb.Append("GO\r\n");
                }
            }
            if (bTable || bView)
            {
                dt = GetViews(TarDbProviderCd, false, CSrc, CTar);                 //Drop Target Views
                foreach (DataRow dr in dt.Rows)
                {
                    sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr[0].ToString() + "') AND type='V')\r\n");
                    sb.Append("DROP VIEW dbo." + dr[0].ToString() + "\r\nGO\r\n");
                }
            }
            if (bTable || bData || bIndex)
            {
                dt = GetFKeys(TarDbProviderCd, false, CSrc, CTar);                 //Drop Target FKs
                string FKType;
                if (TarDbProviderCd.Equals("M"))
                {
                    FKType = "F";
                }
                else
                {
                    FKType = "RI";
                };
                foreach (DataRow dr in dt.Rows)
                {
                    sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr["fkName"].ToString() + "') and type='" + FKType + "')\r\n");
                    sb.Append("ALTER TABLE dbo." + dr["tbName"].ToString() + " DROP CONSTRAINT " + dr["fkName"].ToString() + " \r\nGO\r\n");
                }
            }
            if (bTable || bIndex)
            {
                dt = GetTables(TarDbProviderCd, false, false, false, CSrc, CTar);                       //Drop Target Index
                DataTable dtIx;
                foreach (DataRow dr in dt.Rows)
                {
                    using (DbScriptAccessBase dac = GetDbScriptAccess())
                    {
                        dtIx = dac.GetData("EXEC sp_helpindex " + dr["tbName"].ToString(), false, CSrc, CTar);
                    }
                    foreach (DataRow drIx in dtIx.Rows)
                    {
                        if (drIx[0].ToString().Substring(0, 3) != "PK_")                        // No primary key.
                        {
                            sb.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 = '" + dr["tbName"].ToString() + "')\r\n");
                            sb.Append("DROP INDEX " + dr["tbName"].ToString() + "." + drIx[0].ToString() + " \r\nGO\r\n");
                        }
                    }
                }
            }
            if (bData)
            {
                dt = GetTables(TarDbProviderCd, false, false, false, CSrc, CTar);
                foreach (DataRow dr in dt.Rows)
                {
                    sb.Append("DELETE FROM dbo." + dr["tbName"].ToString() + "\r\nGO\r\n");
                }
            }
            if (bTable)
            {
                dt = GetTables(TarDbProviderCd, false, false, false, CSrc, CTar);                 //Drop target Tables
                foreach (DataRow dr in dt.Rows)
                {
                    sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr[0].ToString() + "') AND type='U')\r\n");
                    sb.Append("DROP TABLE dbo." + dr[0].ToString() + "\r\nGO\r\n");
                }
            }
            return(sb.ToString());
        }
        public string ScriptCreateTables(string SrcDbProviderCd, string TarDbProviderCd, bool IsFrSource, bool allBut, CurrSrc CSrc, CurrTar CTar)
        {
            StringBuilder       sb = new StringBuilder("");
            DataTable           dt;
            DataTable           dtIx;
            Func <string, bool> conditional = (tblName) =>
            {
                if (allBut)
                {
                    return(exceptTables.Contains(tblName));
                }
                else
                {
                    return(!exceptTables.Contains(tblName));
                }
            };

            dt = GetViews(SrcDbProviderCd, IsFrSource, CSrc, CTar); //Drop Views
            foreach (DataRow dr in dt.Rows)
            {
                sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr[0].ToString() + "') AND type='V')\r\n");
                sb.Append("DROP VIEW dbo." + dr[0].ToString() + "\r\nGO\r\n");
            }
            dt = GetFKeys(SrcDbProviderCd, IsFrSource, CSrc, CTar);             //Drop FKs
            string FKType = "F"; if (TarDbProviderCd.Equals("S"))

            {
                FKType = "RI";
            }
            ;

            foreach (DataRow dr in dt.Rows)
            {
                // it is the ref table that needs to be check for FK constraint which points to the original table(where the constraint needs to be removed) !!!
                if (!conditional(dr["refTbName"].ToString()))
                {
                    sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr["fkName"].ToString() + "') and type='" + FKType + "')\r\n");
                    sb.Append("ALTER TABLE dbo." + dr["tbName"].ToString() + " DROP CONSTRAINT " + dr["fkName"].ToString() + " \r\nGO\r\n");
                }
            }
            dt = GetTables(SrcDbProviderCd, IsFrSource, false, false, CSrc, CTar);
            foreach (DataRow dr in dt.Rows)
            {
                // SQL Server generated, skip
                if (dr["tbName"].ToString().Contains("sysdiagrams"))
                {
                    continue;
                }

                if (conditional(dr["tbName"].ToString()))
                {
                    // re-create table only if it is not there
                    sb.Append("IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr["tbName"].ToString() + "') and type='" + "U" + "')\r\n");
                    sb.Append("BEGIN\r\n");
                }

                using (DbScriptAccessBase dac = GetDbScriptAccess())
                {
                    dtIx = dac.GetData("EXEC sp_helpindex " + dr["tbName"].ToString(), IsFrSource, CSrc, CTar);
                }
                foreach (DataRow drIx in dtIx.Rows)
                {
                    if (drIx[0].ToString().Substring(0, 3) != "PK_")                    // No primary key.
                    {
                        //why is this necessary ? dropping table would drop index anyway
                        sb.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 = '" + dr["tbName"].ToString() + "')\r\n");
                        sb.Append("DROP INDEX " + dr["tbName"].ToString() + "." + drIx[0].ToString() + " \r\n");
                    }
                }

                sb.Append("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo." + dr[0].ToString() + "') AND type='U')\r\n");
                sb.Append("DROP TABLE dbo." + dr[0].ToString() + "\r\n");

                sb.Append(ScriptCreateTable(dr["tbName"].ToString(), CSrc).Replace("\r\nGO\r\n", "\r\n"));

                if (conditional(dr["tbName"].ToString()))
                {
                    sb.Append("END\r\n");
                }
                sb.Append("\r\nGO\r\n");
            }
            return(sb.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());
        }