Create an Excel Graph of your Big Tables – with PowerShell!


I showed a demo of how to find the top ten tables in the database at the PASS Conference. Here’s that script – you’ll need to fix the server name, instance name, and database name. You can use this to display any numbers – and even more. The mind reels with the possibilities. This uses the PowerShell provider from SQL Server 2008 (sqlps.exe) but works against 2005 and even 2000 Instances.

Oh, you have to have Excel Installed, of course! All the usual caveats apply – use a test server, know what you’re doing and all that:

# Big Tables to Excel Chart

# Keep this next part on one line… This gets your objects to put in the chart

$BigTables= DIR SQLSERVER:\SQL\UNIVAC\DEFAULT\Databases\Adventureworks\Tables | sort-Object -Property RowCount -desc | select-Object -First 10

$excel = new-object -comobject excel.application

$excel.visible = $true

$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]

$workbook = $excel.workbooks.add()

$workbook.WorkSheets.item(1).Name = "BigTables"

$sheet = $workbook.WorkSheets.Item("BigTables")

$x = 2

$sheet.cells.item(1,1) = "Schema Name"

$sheet.cells.item(1,2) = "Table Name"

$sheet.cells.item(1,3) = "RowCount"

Foreach($BigTable in $BigTables)

{

$sheet.cells.item($x,1) = $BigTable.Schema

$sheet.cells.item($x,2) = $BigTable.Name

$sheet.cells.item($x,3) = $BigTable.RowCount

$x++

}

$range = $sheet.usedRange

$range.EntireColumn.AutoFit()

$workbook.charts.add()

$workbook.ActiveChart.SetSourceData($range)

Comments (4)

  1. John Stafford says:

    Having trouble with the DIR command which sets $BigTables – how does this work? I would have thought you'd need to set a sql connection with sqlcmd?

  2. John Stafford says:

    Having trouble with the DIR command which sets $BigTables – how does this work? I would have thought you'd need to set a sql connection with sqlcmd?

  3. SQLCowboyUP says:

    Really enjoyed seeing this at PASS Buck. Nice!

  4. Mike Dimmick says:

    Answering John Stafford's question: The SQL Server PowerShell provider is a namespace provider, presenting the SQLSERVER: 'drive'. In the example given, UNIVAC is the server name, and DEFAULT the name of the instance (here it was the default instance, which must be specified).

    To authenticate using a SQL Server login, rather than a Windows login, see technet.microsoft.com/…/hh231287.aspx