public IList<Column> GetTableDetails(Table table, string owner) { var columns = new List<Column>(); var conn = new SqlConnection(connectionStr); conn.Open(); using (conn) { using (SqlCommand tableDetailsCommand = conn.CreateCommand()) { tableDetailsCommand.CommandText = string.Format( @" SELECT c.column_name, c.data_type, c.is_nullable, tc.constraint_type, c.numeric_precision, c.numeric_scale, c.character_maximum_length, COLUMNPROPERTY(object_id('[' + '{1}' + '].[' + '{0}' + ']'), c.column_name, 'IsIdentity') as 'IsIdentity',t1.value as 'remark' from information_schema.columns c join syscolumns on ( id = object_id('[' + '{1}' + '].[' + '{0}' + ']') and syscolumns.name = c.column_name ) left outer join ( information_schema.constraint_column_usage ccu join information_schema.table_constraints tc on ( tc.table_schema = ccu.table_schema and tc.constraint_name = ccu.constraint_name and tc.constraint_type <> 'CHECK' ) ) on ( c.table_schema = ccu.table_schema and ccu.table_schema = '{1}' and c.table_name = ccu.table_name and c.column_name = ccu.column_name ) left join sys.extended_properties t1 on ( t1.major_id = object_id(c.table_name) and syscolumns.colid = t1.minor_id ) where c.table_name = '{0}' order by c.table_name, c.ordinal_position", table.Name, owner); using (SqlDataReader sqlDataReader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default)) { while (sqlDataReader.Read()) { string columnName = sqlDataReader.GetString(0); string dataType = sqlDataReader.GetString(1); bool isNullable = sqlDataReader.GetString(2).Equals("YES", StringComparison. CurrentCultureIgnoreCase); var characterMaxLenth = sqlDataReader["character_maximum_length"] as int?; var numericPrecision = sqlDataReader["numeric_precision"] as int?; var numericScale = sqlDataReader["numeric_scale"] as int?; var remark = sqlDataReader["remark"] as string; bool isPrimaryKey = (!sqlDataReader.IsDBNull(3) ? sqlDataReader.GetString(3).Equals( SqlServerConstraintType.PrimaryKey.ToString(), StringComparison.CurrentCultureIgnoreCase) : false); bool isForeignKey = (!sqlDataReader.IsDBNull(3) ? sqlDataReader.GetString(3).Equals( SqlServerConstraintType.ForeignKey.ToString(), StringComparison.CurrentCultureIgnoreCase) : false); bool isIdentity = sqlDataReader["IsIdentity"].ToString() == "1" ? true : false; columns.Add(new Column { TableName = table.Name, Name = columnName, DataType = dataType, IsNullable = isNullable, IsPrimaryKey = isPrimaryKey, //IsPrimaryKey(selectedTableName.Name, columnName) IsForeignKey = isForeignKey, // IsFK() MappedDataType = DataTypeMapper.MapFromDBType(dataType).ToString(), DataLength = characterMaxLenth, IsIdentity = isIdentity, Remark = string.IsNullOrEmpty(remark) ? "" : remark }); table.Columns = columns; } table.PrimaryKey = DeterminePrimaryKeys(table); //comment by ben 2011-09-04 //table.ForeignKeys = DetermineForeignKeyReferences(table); //table.HasManyRelationships = DetermineHasManyRelationships(table); } } } return columns; }
private IList<ForeignKey> DetermineForeignKeyReferences(Table table) { var foreignKeys = table.Columns.Where(x => x.IsForeignKey.Equals(true)); var tempForeignKeys = new List<ForeignKey>(); foreach (var foreignKey in foreignKeys) { tempForeignKeys.Add(new ForeignKey { Name = foreignKey.Name, References = GetForeignKeyReferenceTableName(table.Name, foreignKey.Name) }); } return tempForeignKeys; }
// http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/ private IList<HasMany> DetermineHasManyRelationships(Table table) { var hasManyRelationships = new List<HasMany>(); var conn = new SqlConnection(connectionStr); conn.Open(); using (conn) { using (var command = new SqlCommand()) { command.Connection = conn; command.CommandText = String.Format( @" SELECT DISTINCT PK_TABLE = b.TABLE_NAME, FK_TABLE = c.TABLE_NAME, FK_COLUMN_NAME = d.COLUMN_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA AND a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c ON a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA AND a.CONSTRAINT_NAME = c.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE d on a.CONSTRAINT_NAME = d.CONSTRAINT_NAME WHERE b.TABLE_NAME = '{0}' ORDER BY 1,2", table.Name); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { hasManyRelationships.Add(new HasMany { Reference = reader.GetString(1), ReferenceColumn = reader["FK_COLUMN_NAME"].ToString() }); } return hasManyRelationships; } } }
private static PrimaryKey DeterminePrimaryKeys(Table table) { IEnumerable<Column> primaryKeys = table.Columns.Where(x => x.IsPrimaryKey.Equals(true)); if (primaryKeys.Count() == 1) { Column c = primaryKeys.First(); var key = new PrimaryKey { Type = PrimaryKeyType.PrimaryKey, Columns = { new Column { DataType = c.DataType, Name = c.Name, IsIdentity=c.IsIdentity } } }; return key; } else { var key = new PrimaryKey { Type = PrimaryKeyType.CompositeKey }; foreach (var primaryKey in primaryKeys) { key.Columns.Add(new Column { DataType = primaryKey.DataType, Name = primaryKey.Name, IsIdentity = primaryKey.IsIdentity }); } return key; } }