SMO – Shared Management Objects
Managment Studio uses SMO
SMO emits TSQL
Powershell is a scripting language
Objects – live in memory; have properties and methods.
How does SMO work? memory to engine and engine to memory; once the request is made there are no other requests – the data from that point in time is stored.
Server object contains the connection information
Default happenings –
- get database engine type
- compatability
- database name
Database
Default fields – name
Interesting Fields –
- ID (group)
- Size (retrieved)
- Owner (group)
- PageVerify (group)
- SpaceAvailable (retrieved)
- CompatibilityLevel (retrieved)
- CreateDate (group)
- Many more…
Table
Default fields – schema, name, ID
Interesting Fields –
- IndexSpaceUsed (retrieved)
- IsSystemObject (group)
- Rowcount (group)
- Createdate (group)
- LastModified (group)
- Many more…
Columns
Default fields – Name, ID
Interesting Fields –
- DataType (group)
- Identity (group)
- Nullable (group)
- InPrimaryKey (group)
- Others…
What now?
- Iteration with ForEach
- GetDefaultInitFields
- SetDefaultInitFields
- Use Profieler to continue to fish
- Be specific if performance is key (one byte at a time)
- Be aware of the pipline
Load assemblies
Old way –
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
New way (sort of) –
Add-Type -AssemblyName "Microsoft.SqlServer.Smo"
Other new way –
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=Neutral,...."
Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended, Version=11.0.0.0, Culture=Neutral,...."
Powershell memory considerations – depending on what you are pulling back, and whether or not you have already retrieved the data, the time it takes for the command to be processed will vary.
Different ramifications with foreach.