/// <summary>Initializes a new instance of the <see cref="IndexMetadata{TObjectName, TDbType}"/> class.</summary>
/// <param name="tableName">Name of the table (or view).</param>
/// <param name="name">The name.</param>
/// <param name="isPrimaryKey">if set to <c>true</c> is a primary key.</param>
/// <param name="isUnique">if set to <c>true</c> is a unique index.</param>
/// <param name="isUniqueConstraint">if set to <c>true</c> is a unique constraint.</param>
/// <param name="columns">The columns.</param>
/// <param name="indexSizeKB">Approximate index size in KB</param>
/// <param name="rowCount">Approximate row count</param>
/// <param name="indexType">Type of the index.</param>
protected IndexMetadata(string tableName, string?name, bool isPrimaryKey, bool isUnique, bool isUniqueConstraint, IndexColumnMetadataCollection columns, long?indexSizeKB, long?rowCount, IndexType indexType)
{
TableName = tableName;
Name = name;
IsUnique = isUnique;
IsUniqueConstraint = isUniqueConstraint;
IsPrimaryKey = isPrimaryKey;
Columns = columns;
IndexSizeKB = indexSizeKB;
RowCount = rowCount;
IndexType = indexType;
}
/// <summary>
/// Initializes a new instance of the IndexColumnMetadataCollection class that is a read-only wrapper around the specified list.
/// </summary>
/// <param name="source">The source.</param>
public IndexColumnMetadataCollection(IEnumerable <IndexColumnMetadata <TDbType> > source) : base(source.ToList())
{
GenericCollection = new IndexColumnMetadataCollection(this);
}
/// <summary>Initializes a new instance of the <see cref="IndexMetadata{TObjectName, TDbType}"/> class.</summary>
/// <param name="tableName">Name of the table (or view).</param>
/// <param name="name">The name.</param>
/// <param name="isPrimaryKey">if set to <c>true</c> is a primary key.</param>
/// <param name="isUnique">if set to <c>true</c> is a unique index.</param>
/// <param name="isUniqueConstraint">if set to <c>true</c> is a unique constraint.</param>
/// <param name="columns">The columns.</param>
/// <param name="indexSizeKB">Approximate index size in KB</param>
/// <param name="rowCount">Approximate row count</param>
/// <param name="indexType">Type of the index.</param>
/// <exception cref="ArgumentNullException">columns</exception>
public IndexMetadata(TObjectName tableName, string?name, bool isPrimaryKey, bool isUnique, bool isUniqueConstraint, IndexColumnMetadataCollection <TDbType> columns, long?indexSizeKB, long?rowCount, IndexType indexType) : base(tableName.ToString() !, name, isPrimaryKey, isUnique, isUniqueConstraint, columns?.GenericCollection !, indexSizeKB, rowCount, indexType)
/// <summary>
/// Gets the indexes for a table.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <returns></returns>
/// <remarks>
/// This should be cached on a TableOrViewMetadata object.
/// </remarks>
public override IndexMetadataCollection <SqlServerObjectName, SqlDbType> GetIndexesForTable(SqlServerObjectName tableName)
{
const string indexSql = @"SELECT i.name,
i.is_primary_key,
i.is_unique,
i.is_unique_constraint,
i.index_id,
i.type,
(SELECT SUM(used_page_count) * 8 FROM sys.dm_db_partition_stats ddps WHERE ddps.object_id=i.object_id AND ddps.index_id = i.index_id) AS IndexSizeKB,
(SELECT SUM(row_count) FROM sys.dm_db_partition_stats ddps WHERE ddps.object_id=i.object_id AND ddps.index_id = i.index_id) AS [RowCount]
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE o.name = @Name
AND s.name = @Schema;";
var allColumns = GetColumnsForIndex(tableName);
using (var con = new SqlConnection(m_ConnectionBuilder.ConnectionString))
{
con.Open();
using (var cmd = new SqlCommand(indexSql, con))
{
cmd.Parameters.AddWithValue("@Schema", tableName.Schema);
cmd.Parameters.AddWithValue("@Name", tableName.Name);
var results = new List <IndexMetadata <SqlServerObjectName, SqlDbType> >();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var is_primary_key = reader.GetBoolean("is_primary_key");
var is_unique = reader.GetBoolean("is_unique");
var is_unique_constraint = reader.GetBoolean("is_unique_constraint");
var index_id = reader.GetInt32("index_id");
var name = reader.GetStringOrNull("Name");
var columns = new IndexColumnMetadataCollection <SqlDbType>(allColumns.Where(c => c.IndexId == index_id));
var indexSize = reader.GetInt64("IndexSizeKB");
var rowCount = reader.GetInt64("RowCount");
IndexType indexType;
switch (reader.GetByte("type"))
{
case 0: indexType = IndexType.Heap; break;
case 1: indexType = IndexType.Clustered; break;
case 2: indexType = IndexType.Nonclustered; break;
case 3: indexType = IndexType.Xml; break;
case 4: indexType = IndexType.Spatial; break;
case 5: indexType = IndexType.ClusteredColumnstoreIndex; break;
case 6: indexType = IndexType.NonclusteredColumnstoreIndex; break;
case 7: indexType = IndexType.NonclusteredHashIndex; break;
default: indexType = IndexType.Unknown; break;
}
results.Add(new IndexMetadata <SqlServerObjectName, SqlDbType>(tableName, name, is_primary_key, is_unique, is_unique_constraint, columns, indexSize, rowCount, indexType));
}
return(new IndexMetadataCollection <SqlServerObjectName, SqlDbType>(results));
}
}
}
}
