//-------------------------------------------------------------------
//
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));
}