How Can I Run SQL Server Queries from PowerShell?

Running SQL Server Queries from PowerShell

PowerShell provides several methods for executing SQL Server queries from a local machine. One versatile approach is to utilize .NET's built-in classes for accessing databases.

The following PowerShell function, Invoke-SQL, enables you to execute arbitrary queries against a SQL Server instance:

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
}

To execute a query using this function:

1. Replace the default values for $dataSource, $database, and $sqlCommand with your specific values.
2. Call the function with the modified parameters.

For instance, to execute the query "SELECT * FROM Customers" on a local instance using the "Northwind" database:

$results = Invoke-SQL -Database 'Northwind' -SqlCommand "SELECT * FROM Customers"

The $results variable will contain a table object with the query results.

The above is the detailed content of How Can I Run SQL Server Queries from PowerShell?. For more information, please follow other related articles on the PHP Chinese website!