public static List <Table> ReadSchemaTables(out int leafLevel) { LogUtils.Logs.Log("Begin load schemaTable"); //Get all table and child of itself var query = @"SELECT A.TABLE_NAME, B.CHILD FROM (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE') A LEFT OUTER JOIN (SELECT DISTINCT OBJECT_NAME(referenced_object_id) PARENT, OBJECT_NAME(parent_object_id) CHILD FROM sys.foreign_key_columns) B ON A.TABLE_NAME = B.PARENT ORDER BY A.TABLE_NAME"; var table = new DataTable(); Sql.Fill(query, table); var tableList = new List <Table>(); //build table list foreach (DataRow row in table.Rows) { var tableParent = FieldUtils.AsText(row, "TABLE_NAME"); var tableChild = FieldUtils.AsText(row, "CHILD"); var parent = tableList.FirstOrDefault(tb => tb.Name.Equals(tableParent)); if (null == parent) { parent = new Table(tableParent); tableList.Add(parent); } if (!String.IsNullOrEmpty(tableChild)) { var child = tableList.FirstOrDefault(tb => tb.Name.Equals(tableChild)); if (null == child) { child = new Table(tableChild); tableList.Add(child); } var existsChild = parent.Children.FirstOrDefault(tbl => tbl.Name == child.Name); if (null != existsChild) { parent.Children.Add(child); } parent.IsTopMaster = true; } } //set level leafLevel = 1; foreach (var tbl in tableList) { if (tbl.IsTopMaster && tbl.Children.Count > 0) { tbl.SetLevel(); if (tbl.Level > leafLevel) { leafLevel = tbl.Level; } } } LogUtils.Logs.Log("Completed load schema table:{0} tables, leafLevel:{1}", tableList.Count, leafLevel); foreach (var tbl in tableList) { LogUtils.Logs.Log(tbl.ToString()); } return(tableList); }
public static List <ColumnBase> ReadSchemaColumn(String tableName) { LogUtils.Logs.Log("Begin reading schema column for table:{0}", tableName); var query = String.Format(@"SELECT C.*,RK.CNST_NAME, RK.PARENT_COL, RK.PARENT, ISPK, ISUK, UKNAME FROM (SELECT COLUMN_NAME ,IS_NULLABLE , DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= '{0}') AS C LEFT OUTER JOIN (SELECT DISTINCT OBJECT_NAME(constraint_object_id) CNST_NAME,COL_NAME(parent_object_id, parent_column_id ) AS CHILD_COL, COL_NAME(referenced_object_id, referenced_column_id ) AS PARENT_COL, OBJECT_NAME(referenced_object_id) AS PARENT FROM sys.foreign_key_columns WHERE OBJECT_NAME(parent_object_id) = '{0}') AS RK ON C.COLUMN_NAME = RK.CHILD_COL LEFT OUTER JOIN (SELECT COLUMN_NAME, OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsPrimaryKey') ISPK FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{0}' AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsPrimaryKey') = 1) AS PK ON C.COLUMN_NAME = PK.COLUMN_NAME LEFT OUTER JOIN (SELECT COLUMN_NAME, CONSTRAINT_NAME AS UKNAME , OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsUniqueCnst') ISUK FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{0}' AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsUniqueCnst') = 1) AS UK ON C.COLUMN_NAME = UK.COLUMN_NAME", tableName); var table = new DataTable(); Sql.Fill(query, table); var columnList = new List <ColumnBase>(); foreach (DataRow row in table.Rows) { var isPk = FieldUtils.AsInt(row, "ISPK") == 1; var isUk = FieldUtils.AsInt(row, "ISUK") == 1; var isRef = !String.IsNullOrEmpty(FieldUtils.AsText(row, "PARENT_COL")); ColumnBase column = null; if ((isPk || isUk) && !isRef) { column = new PrimaryColumn(); } else if (isRef) { column = new ReferenceColumn(); } else { column = new NormalColumn(); } column.Name = FieldUtils.AsText(row, "COLUMN_NAME"); column.UniqueName = FieldUtils.AsText(row, "UKNAME"); column.DataTypeName = FieldUtils.AsText(row, "DATA_TYPE"); column.MaxLength = FieldUtils.AsInt(row, "CHARACTER_MAXIMUM_LENGTH"); column.NumPrecision = FieldUtils.AsByte(row, "NUMERIC_PRECISION"); column.NumPrecisionRadix = FieldUtils.AsInt16(row, "NUMERIC_PRECISION_RADIX"); column.GenType = GetGenerateType(column.DataTypeName); var setting = default(GenSetting); switch (column.GenType) { case GenerateDataType.String: setting = new GenStringSetting(); break; case GenerateDataType.DateTime: setting = new GenDateTimeSetting(); break; case GenerateDataType.Time: setting = new GenDateTimeSetting(); break; case GenerateDataType.Int: setting = new GenNumSetting(); break; case GenerateDataType.Long: setting = new GenNumSetting(); break; case GenerateDataType.Bit: setting = new GenBitSetting(); break; case GenerateDataType.Uid: setting = new GenSetting(); break; default: setting = new GenSetting(); break; } column.Setting = setting; if (column.ColType.GetType() == typeof(NormalColumn)) { column.CanNull = FieldUtils.AsBool(row, "IS_NULLABLE"); } else { var refCol = column as ReferenceColumn; if (null != refCol) { refCol.CnstName = FieldUtils.AsText(row, "CNST_NAME"); refCol.RefTableName = FieldUtils.AsText(row, "PARENT"); refCol.RefColumnName = FieldUtils.AsText(row, "PARENT_COL"); } } columnList.Add(column); } LogUtils.Logs.Log("End reading schema column, table:{0}, {1} columns", tableName, columnList.Count); return(columnList); }