为什么此命令在SQL Server Management Studio中有效，而在PowerShell中无效

Question

我有以下SQL命令，它读取名为Logik的数据库的索引碎片

USE Logik

SELECT 
    OBJECT_NAME(ind.OBJECT_ID) AS TableName,
    ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
    indexstats.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN 
    sys.indexes ind ON ind.object_id = indexstats.object_id
                    AND ind.index_id = indexstats.index_id
WHERE 
    indexstats.avg_fragmentation_in_percent > 0

在SQL Server Management Studio中，这是没有问题的，并返回带有碎片的表。

昨天，这也适用于PowerShell。我没有修改任何代码，但今天它不再工作了。有人知道为什么吗？

$Data将保持为空：

param(
    [string]$connectionstring = "Server=some\instance;uid=sa;pwd=dfdfdfdf;Database=Logik;Integrated Security=False;MultipleActiveResultSets=True;Connection Timeout=900;"
)

# Stop bei Fehler
$ErrorActionPreference = "SilentlyContinue"
$database = "logik"

# Database Connection herstellen
$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = $connectionstring
Try { $con.Open() } 
Catch { Throw $_.Exception.Message }

# command einlesen um Fragmentation zu lesen
$command = "
USE Logik
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind 
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 0"

# Command Zusammensetzen
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = $command
$cmd.Connection = $con
$cmd.CommandTimeout = 2000

# Execute Command
Try { $result = $cmd.ExecuteReader() }
Catch { Throw $_.Exception.Message }

# Output von Resultat
$Data = New-Object System.Data.DataTable
$Data.load($result)

整个过程很奇怪，因为在加载数据之后，我将其放入另一个变量$tables中，并且我希望在$tables为空时抛出异常。

我使用这段代码来检查是否加载了任何数据，它为$tables返回了一个空行，但$tables.count返回了8。这是怎么可能的？

# Logik Tabellen
$tables = $Data | ? { $_.avg_fragmentation_in_percent -ge 5 -and $_.IndexName -ne 0 }
if (!$tables) { Throw "Keine Datenbank gefunden, oder keine Höher als 5% Fragmentation" }
$tables.count ; $tables ; pause

Answer 1

根本不应该使用USE。它是SSMS或sqlcmd能够理解的批处理命令，而不是T-SQL命令。

在这种情况下，连接字符串已经连接到Logik数据库。没有理由使用USE来切换到它。

只需使用：

$command = "
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
...

如果您希望在同一查询中使用来自不同数据库的表，请使用由三部分组成的名称，例如：MyDatabase.dbo.ThatTableName。

您可能不应该在Powershell脚本中使用ADO.NET库。如果你拼写错了变量名，Powershell不会报错，它会创建一个新的空变量。

请改用SQL Server Powershell provider。这样，您就可以编写以下代码：

Import-Module Sqlps -DisableNameChecking;
cd SQL\MyRemoteServer\DEFAULT\Databases\MyDatabase
$result=Invoke-SqlCmd -Query "SELECT @@VERSION;"

在此之后，$result变量将保存查询结果。

如果查询返回多行，甚至返回具有多个字段的单个行，您将返回一个属性与列匹配的对象数组。

以下查询：

$tables=Invoke-SqlCmd -Query "SELECT * from sys.tables"

将返回sys.tables表的内容。您可以将单个列作为属性进行访问。此脚本将返回所有表名：

$tables | %{ $_.name;}

链接在一起：

Invoke-SqlCmd -Query "SELECT * from sys.tables" | % {$_.name}