Как сделать запрос через Powershell к SQL серверу


23 июня 2019


Как в Powershell SQL Server сделать запрос

В Windows есть два модуля для работы с MS SQL сервер через Powershell. Первый SQLPS - он устанавливается вместе с SQL Server и больше не обновляется. Более новый модуль называется SQLServer и устанавливается отдельно. Что бы проверить установлен ли у вас SQLPS нужно запустить следующий командлет:

Get-Command -Module *SQL*

Если вы видите, то мы должны обновить модуль:

Install-Module -Name SqlServer -AllowClobber

Если его нет, то просто устанавливаем с 0 либо на хост, либо на сервер:

Install-Module -Name SqlServer

Мы так же можем запустить консоль powershell с SSMS и если модуля не будет нам об этом скажут:

Запуск Powershell из SSMS

Для того что бы посмотреть все командлеты модуля нужно выполнить следующую команду:

Get-Command -Module SQLServer

Нам нужна команда powershell Invoke-Sqlcmd, которая может выполнять запросы удаленно так и локально. Т.к. я буду часто обращаться к серверу я сразу объявляю переменные с инстансом и учетными данными. Если вы запустили powershell из SSMS, то вам делать это не нужно. Если же вы запустили консоль удаленно, при каких-то проблемах убедитесь, что все нужные настройки сделаны. Настройки позволяющие удаленно подключаться через SSMS позволяют подключаться и через Powershell.

#Объявляем компьютер\экземпляр SLQ
$ins = "WIN-MJ79T89M7PL\SQLEXPRESS"
#Запрашивается логин и пароль, а затем хранится в переменной текущего сеанса powershell
$cred = Get-Credential

Выполним запрос на получение списка баз данных, для этого есть отдельный командлет:

Get-SqlDatabase -ServerInstance $ins -Credential $cred

Получение списка баз данных Powershell SQL

После этого выполним обычный SELECT через powershell:

Invoke-Sqlcmd -ServerInstance $ins -Credential $cred -Database pubs -Query "SELECT * FROM authors"

Где:
-ServerInstance - переменная, в которой лежит имя сервера и экземпляра
-Credential - переменная с пользовательскими данными
-Database - имя базы данных (опционально)
-Query - сам запрос

В Powerhsell есть отдельный командлет на чтение данных с MS SQL. В данном случае запрос аналогичен предыдущему:

Read-SqlTableData -ServerInstance $ins -Cred $cred -DatabaseName pubs -SchemaName "dbo" -TableName "authors"

Вывод мы получим в виде "листа". Для того, что бы преобразовать в таблицу есть команда Format-Table или алиас ft :

Invoke-Sqlcmd -ServerInstance $ins -Credential $cred -Database pubs -Query "SELECT * FROM authors" | ft

Для того что бы все время не писать повторяющиеся значения я помещу их в хеш-таблицу:

$params = @{Credential=$cred; Database='pubs'; ServerInstance=$ins}

Можно сам запрос поместить в переменную и переназначать его каждый раз когда нужно:

$quer = "SELECT * FROM authors"
Invoke-Sqlcmd @params -Query $quer | ft

У меня есть SQL запрос, который лежит в файле и я могу его сразу исполнить благодаря ключу -InputFiles. С помощью Powershell я так же могу сразу выгрузить результат SQL запроса в CSV с разделителем в виде ; 

Invoke-Sqlcmd @params -InputFile "C:\SqlScript.sql" | Export-Csv -Path C:\SqlResult -Delimiter ";"

Теперь выполню сравнение:

#Объявляем переменные со строковым и численным значением
$somecity = "Oakland"
$contract = 1
#Обращаем внимание на кавычки. Как и в большинстве языков если снаружи одни кавычки, то внутри другие
$quer = "USE pubs SELECT * FROM authors WHERE City='$somecity' and contract=$contract"
Invoke-Sqlcmd -Query $quer @params | ft

Можно передать значения используя format:

$somecity = "Oakland"
$cont = 1
#Переменные передаем через форматирование
$quer = "USE pubs SELECT * FROM authors WHERE City='{0}' and contract={1}" -f $somecity, $cont

Мы можем использовать знак обратного ударения, если у нас длинные командлеты и мы хотим перенести строки (под буквой ё в английской раскладке):

$quer = "USE pubs SELECT * `
		FROM authors `
		WHERE City='{0}' and contract={1}" -f $somecity, $cont

Мы можем сформировать ответ сразу в визуальном представлении для этого есть Out-GridView. Лучше увидеть это на практике:

$somecity = "Oakland"
$contr = 1
$var1 = "USE pubs SELECT * FROM authors WHERE City='{0}' and contract={1}" -f $somecity, $contr 
Invoke-Sqlcmd -Query $var1 @params | Out-GridView

И это даст нам такое представление с возможностью фильтрации:

Powershell SQL Server Grid View

Выполним запрос через powershell, где вставим данные из CSV в MS SQL. Для начала создам таблицу, где буду хранить данные по своим дискам. Таблица будет состоять из 3 колонок:

$var1 = "CREATE TABLE TestTable3(Name nvarchar(40), Provider nvarchar(50), Root nvarchar(30))"
Invoke-Sqlcmd -Query $var1 @params

Затем экспортирую нужные данные в CSV

Get-PSDrive | Export-Csv -Path psdrive.csv

Создам строку куда потом буду подставлять значения:

$quer = "INSERT INTO TestTable3(Name, Provider, Root)  VALUES('{0}', '{1}', '{2}')"

Затем импортирую данные с csv и циклом буду подставлять значения в переменную query и сразу выполнять запрос через powerhsell:

Import-CSV psdrive.csv | ForEach-Object { $data = $quer -f $_.'Name', $_.'Provider', $_.'Root'
Invoke-Sqlcmd @params -Query $data }

Если мы не хотим писать запрос, то можно воспользоваться существующим командлетом Write-SqlTableData:

$Obj2 = [PSCustomObject] @{Name="Test1";Provider="Test2";Root="Test3"}
Write-SqlTableData -SchemaName "dbo" -TableName "TestTable3" -Force -DatabaseName pubs -Credential $cred -ServerInstance $ins -InputData $Obj2

Остальные возможности этих командлетов можно посмотреть через справку:

Get-Help Invoke-Sqlcmd -Examples
...

Теги: #powershell #ms-sql


Популярные тэги
О блоге
Этот блог представляет собой конспекты выученного материала, преобретенного опыта и лучшие практики в системном администрировании и программировании.