private List <Function> getFunctions(SqlConnection connection) { // Logger DateTime start = DateTime.Now; logger.Info("[ getFunctions ] - start time : " + start.ToString()); List <Function> rtn = new List <Function>(); // get function names List <DBFunctions> dbfunctions = new List <DBFunctions>(); string command = "select name,text from sys.objects a join sys.syscomments b on a.object_id = b.id where type='FN'"; SqlDataReader dr = SqlServer.ExcuteSqlCommandReader(command, connection); while (dr.Read()) { List <DBFunctions> befireFunction = dbfunctions.Where(a => a.name == dr[0].ToString()).ToList(); if (befireFunction.Count() > 0) { befireFunction[0].statement += dr[1].ToString(); } else { dbfunctions.Add(new DBFunctions() { name = dr[0].ToString(), statement = dr[1].ToString() }); } } foreach (DBFunctions dbfunction in dbfunctions) { rtn.Add(new Function(connection.Database, dbfunction.name, dbfunction.statement, true)); } // Logger DateTime end = DateTime.Now; logger.Info("[ getFunctions ] - end time : " + end.ToString() + " ; spend time : " + (end - start).ToString() + "\n"); return(rtn); }
/// <summary> /// Init the different columns information in the tables list /// </summary> /// <param name="conn1"></param> /// <param name="conn2"></param> /// <param name="tables"></param> private void initDiffColumns(SqlConnection conn1, SqlConnection conn2, List <Table> tables) { // Logger DateTime start = DateTime.Now; logger.Info("[ initDiffColumns ] - start time : " + start.ToString()); if (tables.Count() == 0) { return; } List <DBColumns> dbColumns1 = new List <DBColumns>(); List <DBColumns> dbColumns2 = new List <DBColumns>(); // Excute sql command to get all the columns information of all tables string command = "SELECT TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_PRECISION_RADIX,NUMERIC_SCALE FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='" + tables[0].name + "' "; for (int i = 1; i < tables.Count(); i++) { command += " OR TABLE_NAME='" + tables[i].name + "' "; } SqlDataReader dr1 = SqlServer.ExcuteSqlCommandReader(command, conn1); SqlDataReader dr2 = SqlServer.ExcuteSqlCommandReader(command, conn2); while (dr1.Read()) { dbColumns1.Add(new DBColumns() { tbName = dr1[0].ToString(), colName = dr1[1].ToString(), colDefault = dr1[2].ToString(), isNullable = dr1[3].ToString(), dataType = dr1[4].ToString(), charMaxLen = dr1[5].ToString(), charOctetLen = dr1[6].ToString(), numericPrecision = dr1[7].ToString(), numericPrecisionRadix = dr1[8].ToString(), numericScale = dr1[9].ToString() }); } while (dr2.Read()) { dbColumns2.Add(new DBColumns() { tbName = dr2[0].ToString(), colName = dr2[1].ToString(), colDefault = dr2[2].ToString(), isNullable = dr2[3].ToString(), dataType = dr2[4].ToString(), charMaxLen = dr2[5].ToString(), charOctetLen = dr2[6].ToString(), numericPrecision = dr2[7].ToString(), numericPrecisionRadix = dr2[8].ToString(), numericScale = dr2[9].ToString() }); } foreach (Table table in tables) { string tbname = table.name; List <DBColumns> columns1 = dbColumns1.Where(a => a.tbName == tbname).ToList(); List <DBColumns> columns2 = dbColumns2.Where(a => a.tbName == tbname).ToList(); table.columns = compareColumns(conn1.Database, conn2.Database, columns1, columns2); } // Logger DateTime end = DateTime.Now; logger.Info("[ initDiffColumns ] - end time : " + end.ToString() + " ; spend time : " + (end - start).ToString() + "\n"); return; }
/// <summary> /// Compare all the table information between two databases /// </summary> /// <param name="conn1"></param> /// <param name="conn2"></param> /// <returns></returns> public List <Table> compareTables(SqlConnection conn1, SqlConnection conn2) { // Logger DateTime start = DateTime.Now; logger.Info("[ compareTables ] - start time : " + start.ToString()); List <Table> rtnTables = new List <Table>(); // Excute the sql command to get all the table names in a database string command = "SELECT SysObjects.name AS Tablename FROM sysobjects WHERE xtype = 'U'"; SqlDataReader dr1 = SqlServer.ExcuteSqlCommandReader(command, conn1); SqlDataReader dr2 = SqlServer.ExcuteSqlCommandReader(command, conn2); List <string> tables1 = new List <string>(); List <string> tables2 = new List <string>(); while (dr1.Read()) { tables1.Add(dr1[0].ToString()); } while (dr2.Read()) { tables2.Add(dr2[0].ToString()); } // Get the unique table only in database1 or database2 foreach (string item in tables1.Except(tables2)) { Table table = new Table(item, false, conn1.Database); rtnTables.Add(table); } foreach (string item in tables2.Except(tables1)) { Table table = new Table(item, false, conn2.Database); rtnTables.Add(table); } // Get the coeixt tables in database1 and database2 List <Table> candidateTables = new List <Table>(); foreach (string item in tables1.Intersect(tables2)) { Table table = new Table(item, true, ""); candidateTables.Add(table); } initDiffColumns(conn1, conn2, candidateTables); initIndexes(conn1, conn2, candidateTables); initKeys(conn1, conn2, candidateTables); //table.indexes = compareIndexs(conn1, conn2, item); //table.keys = compareKeys(conn1, conn2, item); foreach (Table table in candidateTables) { if (table.columns.Count() != 0 || table.indexes.Count() != 0 || table.keys.Count() != 0) { rtnTables.Add(table); } } // Logger DateTime end = DateTime.Now; logger.Info("[ compareTables ] - end time : " + end.ToString() + " ; spend time : " + (end - start).ToString() + "\n"); return(rtnTables); }