Powershell i SQL 03 – Wykonywanie czynności dla każdej bazy danych

W tym artykule spróbuję pokazać sposób na przejście przez każdą bazę danych i wykonanie pewnej czynności na każdej bazie danych, jednak postaram się powstrzymywać od stosowania  czystego SQL. Zadanie polegające na wykonaniu szeregu czynności dla każdej bazy są dość częste w pracy administratora. Może np. ze względu na audyt trzeba zaraportować użytkowników z każdej bazy, a może chcesz sprawdzić czy każda baza ma swój backup, a może na serwerze szukasz bazy, w której występuje określona tabela czy procedura. W każdym z tych przypadków może się przydać poniższy kawałek kodu.

Skorzystamy z SMO, na początku należy więc załadować SMO:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

Niektórzy wynik powyższego polecenia przesyłają potokiem do Out-Null, dzięki czemu ekran nie będzie zaśmiecony komunikatem o załadowanej bibliotece. Warto z tego skorzystać zwłaszcza, jeżeli tworzony kod ma być wykorzystywany jako funkcja. Z punktu widzenia użytkownika funkcji te komunikaty to „niezrozumiały bełkot”.

Kolejny krok, który będzie się powtarzał również w następnych artykułach, więc warto go zapamiętać, to utworzenie obiektu odpowiadającego za instancję, na której chcesz pracować. U nas będzie to domyślna instancja na lokalnym komputerze. Tworzenie obiektu dokładniej pokazałem w części nr 1:

$SqlServer = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server -ArgumentList "localhost"

alternatywnie było można to zapisać tak:

$SqlServer = New-Object Microsoft.SqlServer.Management.SMO.Server "localhost"

lub tak:

$SqlServer = New-Object("Microsoft.SqlServer.Management.SMO.Server") "localhost"

I właściwie już blisko rozwiązania. Bo okazuje się, że obiekt SMO.Server ma właściwość Databases (!). Wystarczy więc napisać pętlę przechodzącą przez wszystkie elementy tej kolekcji:

#display all databases (even system)
foreach($sqldatabase in $SqlServer.Databases)
{
  $sqldatabase.name 
}

W tym przypadku ograniczyliśmy się do wyświetlenia tylko nazwy bazy danych. Pewnie interesuje cię jakie jeszcze właściwości ma baza danych widoczna w powershell. nic trudnego. Obiekt $sqldatabase jeszcze istnieje, więc prześlij go potokiem do Get-Member:

$sqldatabase | Get-Member

podobnie zresztą można zrobić z $SqlServer aby zbadać jakie właściwości ma obiekt odpowiadający za cały SQL server:

$SqlServer | Get-Member

Komplikujemy przykład. Dla każdej tabeli w każdej bazie danych chcemy wyświetlić na ekranie kropkę. Propozycja rozwiązania:

foreach($sqldatabase in $SqlServer.Databases)
{
  $sqldatabase.name 
  foreach($table in $sqldatabase.Tables)
  {
    Write-Host -NoNewline "."
  }
  Write-Host
}

Wyświetlić bazy danych możesz też wchodząc na wirtualny napęd SQLServer:

Import-Module sqlps
cd SQLServer:\SQL\Localhost\Default\Databases
Get-ChildItem

W tym jednak przypadku… nie widać baz systemowych. Gdyby zaś chcieć wyświetlić wszystkie tabele w ramach jednej wybranej bazy danych wykonać można:

cd AdventureWorks2014

cd Tables

ls

A gdyby chcieć zobaczyć tylko tabele ze schematu Sales, dodaj wyrażenie where.

ls | where { $_.Schema -eq 'Sales’ }

A jeśli dla każdej tabeli chcesz zobaczyć pełną ścieżkę do tabeli dodaj

ls | where { $_.Schema -eq 'Sales’ } | select pspath

I na tym na dzisiaj kończymy. c.d.n.