Jak zwykle do dyspozycji masz dwie metody (nie licząc innych pośredich… ale to normalne w powerhhell że jedną rzecz można zrobić na wiele sposobów.
Metoda sqlps:
Ta metoda jest moim zdaniem bardzo prosta. Wystarczy znać SQL i otworzyć sobie furtkę ze świata Powershell do świata SQL. Zaczynamy od zaimportowania modułu sqlps, następnie odszukujemy bazę, w której ma być utworzona tabela (tutaj tempdb). Potem budujemy napis, który tak na prawdę jest poleceniem SQL, które tworzy tabelę. Potem pozostaje wywołanie polecenia ExecuteNonQuery tzn. wykonaj na serwerze SQL ale wyniki właściwie nie są dla mnie interesujące… no bo rzeczywiście – wynikiem utworzenia tabeli jest albo informacja, że się udało albo komunikat o błędzie. Aby się przekonać, że tabela została rzeczywiście utworzona, ostatnim krokiem jest wyświetlenie listy tabel znajdujących się w bazie. Tu może cię czekać niespodzianka, bo może zabraknąć informacji o utworzeniu tabeli… W takim przypadku wywołaj najpierw metodę refresh na rzecz kolekcji Tables. No i jeszcze jedna drobna uwaga.
Można też było skorzystać z polecenia Invoke-Sqlcmd. W tym przypadku można by opuścić tworzenie zmiennej $db, oraz wywoływanie polecenia ExecuteNonQuery – byłoby więc w sumie jeszcze prościej – o ile tylko znasz SQL…
Import-Module SQLPS
$db = Get-Item SQLSERVER:\SQL\SQL01\DEFAULT\Databases\tempdb
[System.Collections.Specialized.StringCollection]$sql=''
$sql.add('SET ANSI_NULLS ON')
$sql.Add('SET QUOTED_IDENTIFIER ON')
$sql.Add(@"
CREATE TABLE Test(ID INT IDENTITY, NAME NVARCHAR(100))
"@
)
$db.ExecuteNonQuery($sql)
$db.Tables.Refresh()
$db.Tables
Tworzenie tabeli metodami SMO
A oto metoda z SMO. Główne założenie to, że nie znasz języka SQL, ale znasz obiekty, które występują w .NET. Najpierw łączymy się do serwera i wyszukujemy bazę danych (tutaj tempdb). Następnie w tej bazie utworzymy tabelę Test2. Wywołanie konstruktora w ten sposób od razu wypełnia właściwość parent. Potem definiujemy dwa obiekty kolumn, trzeba przy tym określić ich typ i inne właściwości. Kolumny dodajemy do kolekcji Columns obiektu tabeli. Ten obiekt tabeli występuje póki co tylko w pamięci. Aby obiekt został zapisany na dysku wystarczy wywołać metodę Create().
I tu ciekawostka – chociaż obiekt tworzymy absolutnie programistycznie, to wywołanie metody Script pozwoli zobaczyć kod języka TSQL, który zostanie wykorzystany do utworzenia tabeli:
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server 'localhost'
$db = $SqlServer.Databases["tempdb"]
$table = New-object Microsoft.SqlServer.Management.Smo.Table($db,'Test2')
$colID = New-Object Microsoft.SqlServer.Management.Smo.Column($table,'ID')
$colID.DataType = [Microsoft.SqlServer.Management.Smo.DataType]::Int
$colID.Identity = $true
$table.Columns.Add($colID)
$colNAME = New-Object Microsoft.SqlServer.Management.Smo.Column($table,'NAME')
$colNAME.DataType = [Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(100)
$table.Columns.Add($colNAME)
$table.Script()
$table.Create()
$db.Tables | select name
Dodawanie indeksów przez SMO
Zapewne domyślasz się że dodanie indeksu przy wykorzystaniu metod pssql to po prostu wysłanie komendy SQL do serwera, więc przeskakuję przez ten przykład. Za to poniżej można zobaczyć jak dodać primary key do tabeli z wykorzystaniem SMO.
Najpierw tworzymy obiekt $PK, którego rodzicem zostanie tabela. Na tym etapie nie wiadomo jeszcze jaki indeks będzie utworzony. O typie indeksu decyduje kolejna linijka – tu zdecydowaliśmy, że ma to być primary key. Klucz musi posiadać kolumny, dlatego w następnym kroku tworzymy obiekty kolumn indeksu, które potem są dodawane do kolekcji IndexedColumns indeksu. Wreszcie indeks dodajemy do kolekcji indexes tabeli. Wszystkie zmiany były wykonywane w pamięci. Polecenie Alter wysyła je na dysk.
I znowu podpowiedź, jak sprawdzić co się na prawdę działo pod spodem. Polecenie Script wywołane na rzecz tabeli pokaże kod SQL służący do utworzenia tej tabeli. Aby uzyskać podobny kod dla każdego indeksu należy kolekcję indeksów przesłać potokiem do foreach, który dla każdego indeksu wywoła polecenie Script!
$pk = New-object Microsoft.SqlServer.Management.Smo.Index($table,'PK_test')
$pk.IndexKeyType =[Microsoft.SqlServer.Management.Smo.IndexKeyType]::DriPrimaryKey
$pk_column = New-Object Microsoft.SqlServer.Management.Smo.IndexedColumn($pk,'ID')
$pk.IndexedColumns.Add($pk_column)
$table.Indexes.Add($pk)
$table.Alter()
$table.Script()
$table.Indexes | Foreach { $_.Script() }
