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