If you search this blog, there are a number of PowerShell scripts that I have shared in the recent past. Some of them include:
Using PowerShell and Native Client to run queries in SQL Server
Identify Disk Latency on SQL Server Box Using PowerShell
How to Find Logical and Physical Processors on SQL Server?
As I write a number of them, every time I explore and do something totally different. So in this blog, I want to do a neat trick of using PowerShell. Here I we will try to get the details of SQL Server running on my localhost and then we will try to create an HTML page which will hold and show the data.
$OutputFile = "c:\temp\test.html"
$SQLServer = "localhost"
[Reflection.Assembly]::<wbr />LoadWithPartialName("<wbr />Microsoft.SqlServer.Smo")
$Result = @()
$SQLServerInfo = new-object “Microsoft.SqlServer.<wbr />Management.Smo.<span class="il">Server</span>” $SQLServer
$Result += $SQLServerInfo | Select Name, Edition,ProductLevel, Version, ServerType, Platform, IsClustered, PhysicalMemory, Processors
if($Result -ne $null)
{
$HTML = '<style type="text/css">
#Header{font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;width:100%;border-<wbr />collapse:collapse;}
#Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:3px 7px 2px 7px;}
#Header th {font-size:14px;text-align:<wbr />left;padding-top:5px;padding-<wbr />bottom:4px;background-color:#<wbr />A7C942;color:#fff;}
#Header tr.alt td {color:#000;background-color:#<wbr />EAF2D3;}
</Style>'
$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header>
<TR>
<TH><B><span class="il">Server</span> Name</B></TH>
<TH><B>Edition</B></TD>
<TH><B>ProductLevel</B></TH>
<TH><B>Version</B></TH>
<TH><B>ServerType</B></TH>
<TH><B>Platform</B></TH>
<TH><B>IsClustered</B></TH>
<TH><B>PhysicalMemory (MB)</B></TH>
<TH><B>Logical Processors</B></TH>
</TR>"
$HTML += "<TR>
<TD>$($Result.Name)</TD>
<TD>$($Result.Edition)</TD>
<TD align=center>$($Result.<wbr />ProductLevel)</TD>
<TD>$($Result.Version)</TD>
<TD>$($Result.ServerType)</TD>
<TD>$($Result.Platform)</TD>
<TD align=center>$($Result.<wbr />IsClustered)</TD>
<TD align=center>$($Result.<wbr />PhysicalMemory)</TD>
<TD align=center>$($Result.<wbr />Processors)</TD>
</TR>"
$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile
}
The output of this will be a file on our c:\temp folder. If we open the same in a browser, you can see the SQL Server Information about version, edition, Server type, Memory and Processor on the box. This is a neat way to explore and play with a scripting language and PowerShell to create something interesting.
Have you ever created something really interesting like this in your environments? Will you be willing to share something via the comments so that others can also take advantage of this collective knowledge.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Dear Pinal,
Thank you for interesting post.
I found small inaccuracy in PS script.
Variable $Result is array and in variable $HTML filling we need use $Result[0] (or repeat for every row in $Result).
Best Regards,
Ruslan.
Dear Pinal,
Ruslan is right, you created array $Result but use it only as a variable of object $SQLServerInfo in statement:
$Result += $SQLServerInfo | ….
With this statement you populated only $Result[0] element.
So, you can use only $SQLServerInfo instead of $Result in your HTML code.
Best Regards,
Arsen
Sure. Will update them.
Not sure how to make this code work. It looks like it’s got a lot of extra html embedded in it.
Fixed the issue.