# ===================================================================================================================
6 L- Q" R- i; [% y/ \1 z) j
# AUTHOR : Ryan M. Rinehart
MCSE, MCDBA
, O: x' Y p4 Y: `7 A* O- P/ w
#
, ^% |: E; s3 w) @6 Q
# EMAIL :
rrinehart77@hotmail.com
+ l+ A# a: B, A% G#
8 x! K k/ L, R8 y. a# i8 ]4 r/ s# NAME : SMOListDB.ps1
6 f6 r) X+ m5 x( H4 R7 K2 k
#
1 h, s6 ~1 M0 s# VERSION : 1.0
% m4 J* [5 O/ s#
. v; G+ d2 u- e; W( U, j) ^' ?
# DATE : 7/16/
2007& O/ C& ?, C8 }9 }# S3 m
#
! R4 K# g5 Y9 f( ?# DESCRIPTION : Uses SQL 2005 SMO to list the version and databases running on the SQL Server specified
( ~! a: ]) K0 l" F
#
1 P2 o& L" h3 i3 T) a/ Q#
8 f4 V+ ], Z( S* E4 g' K4 H6 l#
( ^! w8 O( {+ x9 l) O# COMMENT(s) : SQL Server must support SMO for this to function properly - 8.0 + 9.0
% I2 @* z C1 N$ m3 o, F* O% B" h
#
* I1 G2 l1 ^, O% ], I#
8 ~8 d" ?: {# p( q5 f
# ===================================================================================================================
: j" K: @' B" p% f" n2 y
) i8 G& E9 r0 e2 Y& Q0 M3 S ~/ K, X# Path to the SDK assemblies
4 Z4 N0 I+ M+ J3 }: y
$sPath = [string] "C:\Program Files\
Microsoft SQL Server\90\SDK\Assemblies"
9 l* V, i0 _! `, \4 A
( |: E7 G' ~0 U1 y' I. w3 x% v# SQL Server to connect to
# L+ z7 h# l# T/ \+ r$sSqlSrv = [string] "SQL2005Test"
) \" K; b7 Y" T5 S! D' {4 B
0 ^3 N' O$ R: l0 _# load the assemblies needed for SQL SMO from the SDK
/ @/ r5 X; [# _0 L: {
[void][System.Reflection.Assembly]::LoadFile($sPath + "\Microsoft.SqlServer.ConnectionInfo.DLL")
- O. u' z& D2 V) Y: W: |[void][System.Reflection.Assembly]::LoadFile($sPath + "\Microsoft.SqlServer.SMO.DLL")
6 h# J% M! n. G( e
[void][System.Reflection.Assembly]::LoadFile($sPath + "\Microsoft.SqlServer.SMOEnum.DLL")
' B2 L L% j* F7 H
3 T' p0 ]" ^. ?& x. Z7 S1 ]# new-up a SQL Server SMO Server object reference
: u& A: p0 M9 z% J4 G$oSrv = New-Object Microsoft.SqlServer.Management.Smo.Server($sSqlSrv)
0 v5 I' t' K3 i8 m$ N k
2 B, }9 a2 e) h
# write SQL Version to console + line return
# ~: }9 [, v% X; C& I! u" x
M# {/ V8 G N* |Write-Host $sSqlSrv "runs SQL Server Version " $oSrv.Information.Version
( y9 ~" l5 Y+ w7 P7 L/ T& O3 ^+ Z
Write-Host "`r"
4 `1 y2 p( f$ c- v. R
Write-Host "Found the following Databases on " $sSqlSrv ":"
+ \. A7 q6 Z% m! G) p1 m4 x
Write-Host "`r"
! ] P6 k: P" v0 o. J1 b
- V, X! `/ b R, {' G# enumerate Databases on this
server5 v2 Y, J) g* v# t4 ]0 `- X& n5 I
foreach($oDb in $oSrv.Databases)
+ q/ o0 Y. f9 E
{
- g9 S0 S- _- U: s: }* U
# write database name/ID to console
2 ^+ Y' Y3 b b
Write-Host "Database: " $oDb.Name "`t" "DBID " $oDb.ID
$ Q' Z" f. J6 ^4 Y# e* }8 |
}
