//------------------------------------------------------------------- // public static IList <SqlForeignKey> GetForeignKeys(string connectionString) { var result = new Collection <SqlForeignKey>(); using (var sqlcon = new SqlConnection(connectionString)) { var command = sqlcon.CreateCommand(); //see https://msdn.microsoft.com/en-us/library/ms190196.aspx command.CommandText = @"SELECT OBJECT_NAME(constraint_object_id) AS ConstraintName ,OBJECT_SCHEMA_NAME(f.parent_object_id) AS SchemaName ,OBJECT_NAME(f.parent_object_id) AS ParentTableName ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ParentColName ,OBJECT_NAME (f.referenced_object_id) AS ReferencedTableName ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferencedColName ,delete_referential_action_desc AS DeleteAction --,update_referential_action_desc FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id --WHERE f.parent_object_id = OBJECT_ID('<schema_name.table_name>')"; sqlcon.Open(); using (var reader = command.ExecuteReader()) { while (reader.Read()) { var row = new SqlForeignKey(); var i = 0; //for (int j = 0; j < reader.FieldCount; j++) //{ // object col = reader[j]; // Console.WriteLine("{0}: {1}, type = {2}", j, col, col.GetType()); //} row.ConstraintName = reader.GetString(i++); row.SchemaName = reader.GetString(i++); row.ParentTableName = reader.GetString(i++); row.ParentColName = reader.GetString(i++); row.ReferencedTableName = reader.GetString(i++); row.ReferencedColName = reader.GetString(i++); row.DeleteAction = reader.GetString(i++); result.Add(row); } } } return(result); }
public static SqlAllInfo SqlAllInfoFactory(string connection) { var allTablesAndCol = SqlTableAndColumnData.GetSqlTablesAndColumns(connection); var allForeignKeys = SqlForeignKey.GetForeignKeys(connection); var tableInfos = from tableGroup in allTablesAndCol.GroupBy(x => x.TableName) let schemaName = tableGroup.First().SchemaName let primaryKey = SqlPrimaryKey.GetPrimaryKeysNames(connection, tableGroup.Key) select(new SqlTableInfo(schemaName, tableGroup.Key, tableGroup.Select(y => new SqlColumnInfo(y.ColumnName, y.SqlTypeName, primaryKey.SingleOrDefault(z => z.ColumnName == y.ColumnName), y.IsNullable, y.MaxLength)).ToList())); var allIndexes = SqlIndex.GetAllIndexes(connection); return(new SqlAllInfo(tableInfos.ToList(), allForeignKeys, allIndexes)); }