如何从 PowerShell 运行 SQLServer 查询？

是否有办法在本地机器上使用 Powershell 在 SQLServer 上执行任意查询？

408616 次浏览
小开

这是我在 这个博客上找到的一个例子。

$cn2 = new-object system.data.SqlClient.SQLConnection("Data Source=machine1;Integrated Security=SSPI;Initial Catalog=master");
$cmd = new-object system.data.sqlclient.sqlcommand("dbcc freeproccache", $cn2);
$cn2.Open();
if ($cmd.ExecuteNonQuery() -ne -1)
{
echo "Failed";
}
$cn2.Close();

假设您可以用另一个 TSQL 语句替换它表示 dbcc freeproccache的地方。

小开

您可以使用 Invoke-Sqlcmd cmdlet

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

http://technet.microsoft.com/en-us/library/cc281720.aspx

小开

如果您想在 本地机器上而不是在 SQL 服务器上下文中执行，那么我将使用以下方法。我们公司就用这个。

$ServerName = "_ServerName_"
$DatabaseName = "_DatabaseName_"
$Query = "SELECT * FROM Table WHERE Column = ''"


#Timeout parameters
$QueryTimeout = 120
$ConnectionTimeout = 30


#Action of connecting to the Database and executing the query and returning results if there were any.
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$DatabaseName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

只要填写 $服务器名称，$DatabaseName和 $查询变量，你就可以开始了。

我不知道我们最初是如何发现这一点的，但有一些非常相似的 给你。

小开

这个函数将以 Powershell 对象数组的形式返回查询结果，这样您就可以在过滤器中使用它们并轻松访问列:

function sql($sqlText, $database = "master", $server = ".")
{
$connection = new-object System.Data.SqlClient.SQLConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database");
$cmd = new-object System.Data.SqlClient.SqlCommand($sqlText, $connection);


$connection.Open();
$reader = $cmd.ExecuteReader()


$results = @()
while ($reader.Read())
{
$row = @{}
for ($i = 0; $i -lt $reader.FieldCount; $i++)
{
$row[$reader.GetName($i)] = $reader.GetValue($i)
}
$results += new-object psobject -property $row
}
$connection.Close();


$results
}
小开

没有一种内置的“ PowerShell”方式来运行 SQL 查询。如果有 安装了 SQLServer 工具，就会得到一个 调用 SqlCmd cmdlet。

因为 PowerShell 是构建在.NET 之上的，所以可以使用 ADO.NET API来运行查询。

小开

对于其他人谁需要这样做，只是股票。NET 和 PowerShell (没有安装额外的 SQL 工具)这里是我使用的函数:

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


}

我用这个太久了，都不知道是谁写的。这是从别人的例子中提炼出来的，但是被简化为清晰的，没有额外的依赖性或者特性，只是需要什么。

我经常使用和分享它，所以我把它变成了 GitHub上的一个脚本模块，这样您现在就可以进入您的模块目录并执行 git clone https://github.com/ChrisMagnuson/InvokeSQL，从这一点开始调用-sql 将在您使用它时自动加载(假设您使用 PowerShell v3或更高版本)。

小开 
Invoke-Sqlcmd -Query "sp_who" -ServerInstance . -QueryTimeout 3
小开

为了避免使用 varchar 参数进行 SQL 注入，可以使用

function sqlExecuteRead($connectionString, $sqlCommand, $pars) {


$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$connection.Open()
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand, $connection)


if ($pars -and $pars.Keys) {
foreach($key in $pars.keys) {
# avoid injection in varchar parameters
$par = $command.Parameters.Add("@$key", [system.data.SqlDbType]::VarChar, 512);
$par.Value = $pars[$key];
}
}


$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset) | Out-Null
$connection.Close()
return $dataset.tables[0].rows


}


$connectionString = "connectionstringHere"
$sql = "select top 10 Message, TimeStamp, Level from dbo.log " +
"where Message = @MSG and Level like @LEVEL"
$pars = @{
MSG = 'this is a test from powershell'
LEVEL = 'aaa%'
};
sqlExecuteRead $connectionString $sql $pars
小开

您甚至可以根据需要格式化字符串和传递参数。

case "ADDSQLSERVERUSER":
//0 = coprorateName;
//1 = user password
//2 = servername
command = @"$sqlQuery = Use JazzUWS_'{0}'
Create login UWSUser_'{0}' with password='{1}';
Create user UWSUser_'{0}' for login UWSUser_'{0}';
Grant Execute to UWSUser_'{0}';


Use ReportSvrUWS_'{0}'
Create user UWSUser_'{0}' for login UWSUser_'{0}';
Grant Execute to UWSUser_'{0}';


Invoke-Sqlcmd -Query $sqlQuery -ServerInstance '{2}'";
break;

C # 远程执行代码(您可以按照自己的方式组织)

        string script = PowershellDictionary.GetPowershellCommand("ADDSQLSERVERUSER");
script = String.Format(script, this.CorporateName, password, this.SQLServerName)
PowerShellExecution.RunScriptRemote(_credentials.Server, _credentials.Username, _credentials.Password, new List<string> { script });
小开

您可以使用最好的 SQLServer 模块: DBATOOLS。运行对多个 sql 实例的查询也会使您受益。

Install-Module dbatools -Scope CurrentUser


$sql = 'SQL1','SQL1\INSTANCE1','SQL2'
$query = "SELECT 'This query would run on all SQL instances'"


Invoke-DbaQuery -SqlInstance $sqlinstances -Query $query -AppendServerInstance

提交答案