PowerShell query SQL database

Invoke-Sqlcmd cmdlet
March 31, 2023 – 08:51 am
How do I query a SQL Server DB using PowerShell, and how do I

Invoke-Sqlcmd is a SQL Server cmdlet that runs scripts that CONTAIN statements from the languages ( Transact-SQL and XQuery) and commands supported by the sqlcmd utility.

Using Invoke-Sqlcmd

The Invoke-Sqlcmd cmdlet lets you run your sqlcmd script files in a Windows PowerShell environment. Much of what you can do with sqlcmd can also be done using Invoke-Sqlcmd.

This is an example of calling Invoke-Sqlcmd to execute a simple query, similar to specifying sqlcmd with the -Q and -S options:

Invoke-Sqlcmd -Query "SELECT GETDATE AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"

This is an example of calling Invoke-Sqlcmd, specifying an input file and piping the output to a file This is similar to specifying sqlcmd with the -i and -o options:

Invoke-Sqlcmd -InputFile "C:\MyFolder\TestSQLCmd.sql" | Out-File -filePath "C:\MyFolder\TestSQLCmd.rpt"

This is an example of using a Windows PowerShell array to pass multiple sqlcmd scripting variables to Invoke-Sqlcmd. The "$" characters identifying the sqlcmd scripting variables in the SELECT statement have been escaped by using the PowerShell back-tick "`" escape character:

$MyArray = "MyVar1 = 'String1'", "MyVar2 = 'String2'" Invoke-Sqlcmd -Query "SELECT `$(MyVar1) AS Var1, `$(MyVar2) AS Var2;" -Variable $MyArray

This is an example of using the SQL Server provider for Windows PowerShell to navigate to an instance of the Database Engine, and then using the Windows PowerShell Get-Item cmdlet to retrieve the SMO Server object for the instance and passing it to Invoke-Sqlcmd:

Set-Location SQLSERVER:\SQL\MyComputer\MyInstance Invoke-Sqlcmd -Query "SELECT GETDATE AS TimeOfQuery;" -ServerInstance (Get-Item .)

The -Query parameter is positional and does not have to be named. If the first string that is passed to Invoke-Sqlcmd: is unnamed, it is treated as the -Query parameter.

Invoke-Sqlcmd "SELECT GETDATE AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"

Path Context in Invoke-Sqlcmd

If you do not use the -Database parameter, the database context for Invoke-Sqlcmd is set by the path that is active when the cmdlet is called.

Source: docs.microsoft.com
Related Posts