PoshCode Logo PowerShell Code Repository

WPFTableSpace (modification of post by view diff)
embed code: <script type="text/javascript" src="http://PoshCode.org/embed/994"></script>download | new post

Uses Powerboots, Visifire and SQLPSX to display a WPF graph of SQL Server table data and index space usage

  1. #Usage: ./WPFTableSpace.ps1 'Z002\SqlExpress' AdventureWorks
  2. #Note: Requires .NET 3.5, Visifire Charts (tested on v2.1.0), Powerboots (tested on v0.1), and SQLPSX (tested on v1.5)
  3. param($sqlserver=$(throw 'sqlserver is required.'),$dbname=$(throw 'dbname is required.'),$top=10)
  4.  
  5. $libraryDir = Convert-Path (Resolve-Path "$ProfileDir\Libraries")
  6. [Void][Reflection.Assembly]::LoadFrom( (Convert-Path (Resolve-Path "$libraryDir\WPFVisifire.Charts.dll")) )
  7. . $libraryDir\LibrarySmo.ps1
  8.  
  9. if (!(Get-PSSnapin | ?{$_.name -eq 'PoshWpf'}))
  10. { Add-PsSnapin PoshWpf }
  11.  
  12. $qry = @"
  13. CREATE TABLE #spaceused
  14. (name nvarchar(128),
  15. rows char(11),
  16. reserved varchar(18),
  17. data varchar(18),
  18. index_size varchar(18),
  19. unused varchar(18));
  20. EXEC sp_MSforeachtable 'insert #spaceused exec sp_spaceused ''?''';
  21. SELECT TOP $top name
  22. , CAST(rows AS int) AS rows
  23. , CAST(SUBSTRING(reserved,0,LEN(reserved)-2) AS int) AS reserved
  24. , CAST(SUBSTRING(data,0,LEN(data)-2) AS int) AS data
  25. , CAST(SUBSTRING(index_size,0,LEN(index_size)-2) AS int) AS index_size
  26. , CAST(SUBSTRING(unused,0,LEN(unused)-2) AS int) AS unused
  27. FROM #spaceused
  28. ORDER BY reserved DESC;
  29. DROP TABLE #spaceused;
  30. "@
  31.  
  32. New-BootsWindow -Async {
  33.     $chart = New-Object Visifire.Charts.Chart
  34.     $chart.Height = 500
  35.     $chart.Width = 800
  36.     $chart.watermark = $false
  37.     $chart.Theme = "Theme2"
  38.     $chart.View3D = $true
  39.     $chart.BorderBrush = [System.Windows.Media.Brush]"Gray"
  40.     $chart.CornerRadius = [System.Windows.CornerRadius]5
  41.     $chart.BorderThickness = [System.Windows.Thickness]0.5
  42.     $chart.AnimationEnabled = $false
  43.  
  44.     $ds1 = New-Object Visifire.Charts.DataSeries
  45.     $ds1.RenderAs = [Visifire.Charts.RenderAs]"StackedBar"
  46.     $ds1.LegendText = "Data"
  47.     $ds1.LabelEnabled = $true
  48.     $ds1.LabelText = "#YValue"
  49.  
  50.     $ds2 = New-Object Visifire.Charts.DataSeries
  51.     $ds2.RenderAs = [Visifire.Charts.RenderAs]"StackedBar"
  52.     $ds2.LegendText = "Index"
  53.     $ds2.LabelEnabled = $true
  54.     $ds2.LabelText = "#YValue"
  55.     $ds2.RadiusX = 5
  56.     $ds2.RadiusY = 5
  57.  
  58.     foreach ($table in Get-SqlData $sqlserver $dbname $qry)
  59.     {
  60.     $dp1 = new-object Visifire.Charts.DataPoint
  61.     $dp1.AxisXLabel = $table.name
  62.     $dp1.YValue = $table.data
  63.     $ds1.DataPoints.Add($dp1)
  64.  
  65.     $dp2 = new-object Visifire.Charts.DataPoint
  66.     $dp2.YValue = $table.index_size
  67.     $ds2.DataPoints.Add($dp2)
  68.     }  
  69.     $chart.Series.Add($ds1)
  70.     $chart.Series.Add($ds2)
  71.  
  72.     $chart
  73. } -Title "Table Space"

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me