DATOS Y METODOLOGÍA
4.1 ESTIMACIÓN DEL MODELO
Prepare-SQLProvider
$server = Get-Item SQLSERVER:\SQL\localhost\default $server.GetType() | Format-Table –Auto
$server | Get-Member
Notice in figure 23.3 that you see the type: the server object is a Microsoft.SqlServer .Management.Smo.SqlSmoObject. More specifically, in the second statement it’s a Microsoft.SqlServer.Management.Smo.Server object. You can use this approach with databases, tables, and stored procedures to get and manipulate objects, all in a path to the object.
23.5
Getting a count of databases in an instance
The next listing uses the SQL Server provider to get a count of databases using func- tions to load the provider for whichever version of SQL Server is installed.
function Get-DatabaseCounts { [CmdletBinding()] Param( [Parameter(Position=0,Mandatory=$true)] [alias("server")] [string]$serverName, [Parameter(Position=1,Mandatory=$true)] [alias("instance")] [string]$instanceName ) $results = @() (Get-Item SQLSERVER:\SQL\$serverName\$instanceName).Databases | Foreach-Object { $db = $_ $db.Tables | Foreach-Object { $table = $_ $hash = @{ "Database" = $db.Name "Schema" = $table.Schema "Table" = $table.Name "RowCount" = $table.RowCount "Replicated" = $table.Replicated }
$item = New-Object PSObject -Property $hash $results += $item
} }
Listing 23.5 Getting a server object using the SQL Server provider
$results }
Prepare-SQLProvider -Verbose
Get-DatabaseCounts -server "localhost" -instance "DEFAULT" | Out-GridView
This listing shows the count of databases in the localhost \ DEFAULT instance of SQL Server using the Get-DatabaseCounts function.
23.6
Finding a table in many databases
This use case is a common one when you’re dealing with upgrades to a database or when you’re deploying new code that relies on a new table that was created during development. There are different ways to find a table in the midst of many databases. Listing 7 shows a function that uses the provider to find the table, and listing 8 still uses the provider but with a script.
Function Get-SQLTableInDB { [CmdletBinding()] Param( [Parameter(Position=0,Mandatory=$true)] [alias("server")] [string]$serverName, [Parameter(Position=1,Mandatory=$true)] [alias("instance")] [string]$instanceName, [Parameter(Position=2,Mandatory=$true)] [alias("table")] [string]$tableName ) (Get-Item SQLSERVER:\SQL\$serverName\$instanceName).Databases | Foreach-Object { $db = $_ $db.Tables | Foreach-Object { $sqltable = $_
If($tableName –eq $($sqltable.Name)) { Return $db.Name } } } } Prepare-SQLProvider
Get-SQLTableInDatabases –server "localhost" –instance "DEFAULT" ` –table "Table1"
95 Summary Prepare-SQLProvider $servername = "localhost" $instance = "default" $tableName = "backupset" $schema = "dbo" $instpath = "SQL\$servername\$instance\Databases"
foreach($db in (Get-ChildItem SQLSERVER:\SQL\$instpath)) { $dbname = $db.Name if(!(Test-Path SQLSERVER:\$instpath\$dbname\Tables\$schema`.$tableName)) { Write-Output $db.Name } }
23.7
Summary
In this chapter you’ve seen how to get the SQL Server PowerShell provider for 2008/R2 and how to add it to your PowerShell session. The SQL Server provider for Power- Shell is provided as a snap-in and is loaded with the Add-PSSnapin command; you access the structure of SQL Server using a path structure. You can add the provider to any Windows machine by downloading the PowerShell objects in the SQL Server Fea- ture Packs.
Whether you’re retrieving objects individually or detecting their existence a path structure provides a powerful way to use PowerShell and SQL Server together. This is just the tip of the iceberg when it comes to what you can do with the provider and how it all works, but I hope you caught the vision of where you can take it.
Chapter 25 discusses SMO and how to use objects in SQL Server with SMO; that chapter is a great companion to what you learned here. SQL Server 2012 wasn’t cov- ered in this chapter, but the concepts apply to the SQL Server 2012 provider; it’s just loaded as a module (SQLPS) instead of a snap-in. Now, go execute some PowerShell!
Here's your chance to learn from the best in the business. PowerShell Deep Dives is a trove of essential tech- niques, practical guidance, and the expert insights you earn only through years of experience. Editors Jeffery Hicks, Richard Siddaway, Oisin Grehan, and Aleksandar Nikolic hand-picked the 28 chapters in the book's four parts: Administration, Scripting, Development, and Plat- forms.
PowerShell has permanently changed Windows admi- nistration. This powerful scripting and automation tool allows you to control virtually every aspect of Windows and most Microsoft servers like IIS and SQL Server.
PowerShell Deep Dives is a trove of essential techniques and practical guidance. It is rich with insights from experts who won them through years of experience. The book's 28 chapters, grouped in four parts (Administration, Scripting, Development, and Platforms), were hand-picked by four section editors: Jeffery Hicks, Richard Sid- daway, Oisín Grehan, and Aleksandar Nikolic.
Whether you're just getting started with PowerShell or you already use it daily, you'll find yourself returning to this book over and over.
What's inside:
Managing systems through a keyhole
The Ten Commandments of PowerShell scripting
Scalable scripting for large datasets
Adding automatic remoting
Provisioning web servers and websites automatically to IIS 8