☰

How do you run a SQL Server query from PowerShell? Ask Question

tag-icon • tag-icon powershell sql
How do you run a SQL Server query from PowerShell? Ask Question

Is there a way to execute an arbitrary query on a SQL Server using Powershell on my local machine?

ベストアンサー1

For others who need to do this with just stock .NET and PowerShell (no additional SQL tools installed) here is the function that I use:

function Invoke-SQL {
    param(
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()
    
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null
    
    $connection.Close()
    $dataSet.Tables

}

I have been using this so long I don't know who wrote which parts. This was distilled from others' examples, but simplified to be clear and just what is needed without extra dependencies or features.

I use and share this often enough that I have turned this into a script module on GitHub so that you can now go to your modules directory and execute git clone https://github.com/ChrisMagnuson/InvokeSQL and from that point forward invoke-sql will automatically be loaded when you go to use it (assuming your using PowerShell v3 or later).

おすすめ記事