Get-SQLServer.ps1

<#
.SYNOPSIS
    This script uses SQL Server Server SMO objects to display
    server information.
.DESCRIPTION
    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
    tables.
.NOTES
    File Name  : Get-SQLServer.ps1
    Author     : Thomas Lee - tfl@psp.co.uk
    Requires   : PowerShell Version 3.0, SQL Server 2012
.LINK
    This script posted to:
        http://pshscripts.blogspot.com/2010/05/get-sqlserverps1.html 
.EXAMPLE
    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
    Service Account:  NT AUTHORITY\NETWORKSERVICE
 
    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
}