Get-SQLServer.ps1

Microsoft.SqlServer.Management.Smo.Server

1. <# 
2. .SYNOPSIS 
3.     This script uses SQL Server Server SMO objects to display server information. 
4. .DESCRIPTION 
5.     This script first loads the SMO assembly and then creates a server object. 
6.     The script then prints basic server information plus details of databases and 
7.     tables. 
8. .NOTES 
9.     File Name  : Get-SQLServer.ps1 
10.     Author     : Thomas Lee - tfl@psp.co.uk 
11.     Requires   : PowerShell Version 2.0 
12. .LINK 
13.     This script posted to: 
14.        http://pshscripts.blogspot.com/2010/05/get-sqlserverps1.html 
15.     MSDN Sample posted at: 
16.        http://msdn.microsoft.com/en-us/library/microsrosoft.management.smo.server.aspx 
17. .EXAMPLE 
18.     PS C:\Foo> .\Get-SQLServer.ps1' 
19.     Server Details 
20.     -------------- 
21.     Server Name:      SQL1 
22.     Product:          Microsoft SQL Server 
23.     Edition:          Enterprise Edition (64-bit) 
24.     Type:             Singleton 
25.     Version:          10.0.2531 
26.     Version String:   10.0.2531.0 
27.     Service Account:  .\sql 
28.   
29.     Databases and Table 
30.     ------------------- 
31.     master (contains 6 tables) 
32.     model (contains 0 tables) 
33.     msdb (contains 102 tables) 
34.     PSMC (contains 1 tables) 
35.     tempdb (contains 0 tables) 
36. #>  li class="alt">## 
37. # Start of Script 
38. ## 
39.  
40. # Load the SMO Assembly 
41. $null = [system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") 
42.   
43. # Create Server Object 
44. $server = New-Object Microsoft.SqlServer.Management.Smo.Server "SQL1" 
45.   
46. # Display key properties  
47. "Server Details" 
48. "--------------" 
49. "Server Name:      {0}" -f $Server.Netname 
50. "Product:          {0}" -f $Server.Product 
51. "Edition:          {0}" -f $Server.Edition 
52. "Type:             {0}" -f $Server.ServerType 
53. "Version:          {0}" -f $Server.Version 
54. "Version String:   {0}" -f $Server.Versionstring 
55. "Service Account:  {0}" -f $Server.ServiceAccount 
56. "" 
57.   
58. # Display Database/Table info 
59. "Databases and Table" 
60. "-------------------" 
61. foreach ($database in $server.Databases) { 
62. "{0} (contains {1} tables)" -f $Database.name, $Database.Tables.Count 
63. } 

Technorati Tags: PowerShell,Code,Scripts,SQL,MIcrosoft.SQLServer.Management.SMO.Server