private void AddTableTypes(TreeNode typesNode)
{
var schemas = Database.Dmvs.Schemas.ToDictionary(x => x.schema_id);
var types = Database.Dmvs.TableTypes.Where(x => x.IsTableType && x.IsUserDefined)
.Select(x => new { t = x, Name = schemas[x.SchemaID].name + "." + x.Name })
.OrderBy(t => t.Name).ToArray();
if (types.Length == 0)
{
return;
}
var tableNode = typesNode.Nodes.Add("User-Defined Table Types");
foreach (var type in types)
{
var typeNode = tableNode.Nodes.Add(type.Name);
//typeNode.ContextMenu = tableTypeMenu;
// Add columns
var typeColumnsNode = typeNode.Nodes.Add("Columns");
var columns = Database.Dmvs.Columns
.Where(c => c.ObjectID == type.t.TypeTableObjectId)
.ToArray();
foreach (var c in columns)
{
var mainColumn = Database.Dmvs.Columns.Single(x => x.ColumnID == c.ColumnID && x.ObjectID == c.ObjectID);
var typeName = DatabaseMetaData.GetColumnTypeName(Database.Dmvs, mainColumn);
typeColumnsNode.Nodes.Add($"{c.Name} ({typeName})");
}
// Add indexes
var tableIndexesNode = typeNode.Nodes.Add("Indexes");
var indexes = Database.Dmvs.Indexes
.Where(i => i.ObjectID == type.t.TypeTableObjectId && i.IndexID > 0)
.OrderBy(i => i.Name);
foreach (var i in indexes)
{
var indexNode = tableIndexesNode.Nodes.Add(i.Name);
// Add index columns
var indexColumns = Database.Dmvs.IndexColumns
.Where(ic => ic.ObjectID == type.t.TypeTableObjectId && ic.IndexID == i.IndexID);
foreach (var ic in indexColumns)
{
var tableTypeColumn = columns.Single(c => c.ColumnID == ic.ColumnID);
var typeName = DatabaseMetaData.GetColumnTypeName(Database.Dmvs, tableTypeColumn);
indexNode.Nodes.Add($"{tableTypeColumn.Name} ({typeName})");
}
}
}
}
private void AddTablesNode(TreeNode rootNode)
{
var schemas = Database.Dmvs.Schemas.ToDictionary(x => x.schema_id);
var tablesWithRowCount = (from table in Database.Dmvs.Tables.Where(x => !x.IsMSShipped)
join partition in Database.Dmvs.Partitions on table.ObjectID equals partition.ObjectID
where partition.IndexID < 2
group partition by new { Name = $"{schemas[table.SchemaID].name}.{table.Name}", table.ObjectID }
into g
orderby g.Key.Name
select new { g.Key.Name, g.Key.ObjectID, RowCount = g.Sum(x => x.Rows) }).ToArray();
if (tablesWithRowCount.Length == 0)
{
return;
}
var tableRootNode = rootNode.Nodes.Add("Tables");
foreach (var t in tablesWithRowCount)
{
var tableNode = tableRootNode.Nodes.Add(t.Name);
//tableNode.ToolTipText = $"{t.RowCount} rows";
tableNode.Tag = t.RowCount;
tableNode.ContextMenu = tableMenu;
// Add columns
var tableColumnsNode = tableNode.Nodes.Add("Columns");
var columns = Database.Dmvs.Columns
.Where(c => c.ObjectID == t.ObjectID)
.ToArray();
foreach (var c in columns)
{
var mainColumn = Database.Dmvs.Columns.Single(x => x.ColumnID == c.ColumnID && x.ObjectID == c.ObjectID);
var typeName = DatabaseMetaData.GetColumnTypeName(Database.Dmvs, mainColumn);
tableColumnsNode.Nodes.Add($"{c.Name} ({typeName})");
}
// Add indexes
var tableIndexesNode = tableNode.Nodes.Add("Indexes");
var indexes = Database.Dmvs.Indexes
.Where(i => i.ObjectID == t.ObjectID && i.IndexID > 0)
.OrderBy(i => i.Name);
foreach (var i in indexes)
{
var indexNode = tableIndexesNode.Nodes.Add(i.Name);
// Add index columns
var indexColumns = Database.Dmvs.IndexColumns
.Where(ic => ic.ObjectID == t.ObjectID && ic.IndexID == i.IndexID);
foreach (var ic in indexColumns)
{
var tableColumn = columns.Single(c => c.ColumnID == ic.ColumnID);
var typeName = DatabaseMetaData.GetColumnTypeName(Database.Dmvs, tableColumn);
indexNode.Nodes.Add($"{tableColumn.Name} ({typeName})");
}
}
}
}
