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));
}
public static IList <SqlIndex> GetAllIndexes(string connectionString)
{
var result = new Collection <SqlIndex>();
using (var sqlcon = new SqlConnection(connectionString))
{
var command = sqlcon.CreateCommand();
//see http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db
//Note: we order everything so that we can easily deal with multiple indexes on the same table/column
command.CommandText = @"SELECT
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
col.name AS ColumnName,
ind.name AS IndexName,
ind.is_primary_key AS PrimaryKey,
ind.index_id AS IndexType,
ind.is_unique AS IsUnique,
col.is_identity AS IsIdentity
FROM
sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
WHERE
t.is_ms_shipped = 0
AND ind.index_id <> 0 -- No heap
ORDER BY
t.schema_id, t.name, ind.is_primary_key, ind.index_id, ind.is_unique, ind.name";
sqlcon.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var row = new SqlIndex();
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.SchemaName = reader.GetString(i++);
row.TableName = reader.GetString(i++);
row.ColumnName = reader.GetString(i++);
row.IndexName = reader.GetString(i++);
row.IsPrimaryIndex = reader.GetBoolean(i++);
row.Clustered = reader.GetInt32(i++) == 1;
row.IsUnique = reader.GetBoolean(i++);
row.IsIdentity = reader.GetBoolean(i++);
result.Add(row);
}
}
}
return(result);
}
