Thursday, 27 November 2008


This script uses SQL Server Server SMO objects to display
    server information.
This script first loads the SMO assembly and then creates a
    server object. The script then prints basic server information
    plus details of databases and
File Name : Get-SQLServer.ps1
Author : Thomas Lee -
Requires : PowerShell Version 3.0, SQL Server 2012
This script posted to:
C:\Foo> .\Get-SQLServer.ps1
Server Details
Server Name: SQL2012
Product: Microsoft SQL Server
Edition: Enterprise Edition (64-bit)
Type: Standalone
Version: 11.0.3000
Version String: 11.0.2000.0

Databases and Table
master (contains 6 tables)
model (contains 0 tables)
msdb (contains 102 tables)
PSMC (contains 1 tables)
ReportServer (contains 34 tables)
ReportServer (contain 13 tables)
tempdb (contains 0 tables)

# Start of Script

# Load the SMO Assemblies - installed with the module!
# Note this generates a warning since SQL module uses non-standard verb!
Import-Module SQLPS

# Create Server Object
$server = New-Object Microsoft.SqlServer.Management.Smo.Server 'SQL2-12'

# Display key properties
'Server Details'
'Server Name: {0}' -f $Server.Netname
'Product: {0}' -f $Server.Product
'Edition: {0}' -f $Server.Edition
'Type: {0}' -f $Server.ServerType
'Version: {0}' -f $Server.Version
'Version String: {0}' -f $Server.Versionstring
'Service Account: {0}' -f $Server.ServiceAccount

# Display Database/Table info
'Databases and Table'
Foreach ($database in $server.Databases) {
'{0} (contains {1} tables)' -f $Database.Name, $Database.Tables.Count

No comments: