本节书摘来自异步社区出版社《PowerShell V3—SQL Server 2012数据库自动化运维权威指南》一书中的第2章，第2.7节，作者：【加拿大】Donabel Santos，更多章节内容可以访问云栖社区“异步社区”公众号查看。
2.7 查找数据库对象
本方案讲述如何基于查找字符串通过PowerShell查找数据库对象。
2.7.1 准备
在这个练习中，我们将使用AdventureWorks2008R2查找名称中含有“Product”的SQL Server数据库对象。
为了清楚知道获得什么结果，在SSMS中运行如下脚本。
USE AdventureWorks2008R2
GO
SELECT
*
FROM
sys.objects
WHERE
name LIKE '%Product%'
-- filter table level objects only
AND [type] NOT IN ('C', 'D', 'PK', 'F')
ORDER BY
[type]
AI 代码解读
将会得到23行结果，记住这个数字。
2.7.2 如何做…
1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。
2．导入SQLPS模块，创建一个新的SMO服务器对象。
#import SQL Server module
Import-Module SQLPS -DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
AI 代码解读
-ArgumentList $instanceName
3．添加如下脚本并运行。注意，下面的脚本将只在PowerShell V3下起作用，因为使用的是简化的Where cmdlet。如果你想在PowerShell V2中使用，用V2的写法替代Where语法。
$databaseName = "AdventureWorks2008R2"
$db = $server.Databases[$databaseName]
#what keyword are we looking for?
$searchString = "Product"
#create empty array, we will store results here
$results = @()
#now we will loop through all database SMO
#properties and look of objects that match
#the search string
#note we are explicitly excluding Federations, because
#this throws an error
$db |
Get-Member -MemberType Property |
Where Definition -Like "*Smo*" |
Where Definition -NotLike "*Federation*" |
ForEach-Object {
$type = $_.Name
$db.$type |
Where Name -Like "*$searchstring*" |
ForEach-Object {
$result = New-Object -Type PSObject -Prop @{
"ObjectType"=$type.Replace("Microsoft.SqlServer.Management.Smo.", "")
"ObjectName"=$_.Name
}
$results += $result
}
}
#display results
$results
#export results to csv file
$file = "C:\Temp\SearchResults.csv"
$results | Export-Csv -Path $file –NoTypeInformation
#display file contents
notepad $file
AI 代码解读
结果如下：
2.7.3 如何实现…
创建SMO服务器对象后，我们需要创建一个AdventureWorks2008R2数据库的SMO数据库句柄。
$databasename = "AdventureWorks2008R2"
$db = $server.Databases[$databasename]
AI 代码解读
我们也定义了查找字符串。我们的目的是获得名字中含有“Product”的所有数据库对象。
#what keyword are we looking for?
$searchString = "Product"
AI 代码解读
我们也创建一个空的数据，用于存储查询结果的记录。完成后，我们将以表格的形式显示最后的结果。
$results = @()AI 代码解读
我们将会浏览所有的数据库相关的SMO属性，查找包含了该关键字的对象。注意，下面的脚本将只会在PowerShell V3中起作用，因为使用了简化的Where cmdlet。如果你想使用PowerShell V2，使用V2的写法替代Where语法。
#now we will loop through all database SMO
#properties and look of objects that match
#the search string
#note we are explicitly excluding Federations, because
#this throws an error
$db |
Get-Member -MemberType Property |
Where Definition -Like "*Smo*" |
Where Definition -NotLike "*Federation*" |
ForEach-Object {
$type = $_.Name
$db.$type |
Where Name -Like "*$searchstring*" |
ForEach-Object {
$result = New-Object -Type PSObject -Prop @{
"ObjectType"=$type.Replace("Microsoft.SqlServer.
Management.Smo.", "")
"ObjectName"=$_.Name
}
$results += $result
}
}
AI 代码解读
在循环中，我们解析并创建结果。
第一部分探测每一个属性，检查名字中是否包含我们的查询字符串。
$db |
Get-Member -MemberType Property |
Where Definition -Like "*Smo*" |
Where Definition -NotLike "*Federation*" |
ForEach-Object {
$type = $_.Name
$db.$type |
Where Name -Like "*$searchstring*" |
ForEach-Object {
$result = New-Object -Type PSObject -Prop @{
"ObjectType"=$type.Replace("Microsoft.SqlServer.
Management.Smo.", "")
"ObjectName"=$_.Name
}
$results += $result
}
}
AI 代码解读
注意，传递给外层的Where-Object cmdlets（这里简化为Where，只在PowerShell V3中支持）有两个条件，如下所示。
Where定义 –Like “Smo”，因为我们只查找SMO属性。
Where定义 –NotLike “Federation”，因为当你访问db.Federations，会出现例外。第二部分构建了一个包含两列的结果行：ObjectType和ObjectName。新的结果类型为PSObject。一旦构建成功，存储在results数组。我们从结果对象类型中去除Microsoft.SqlServer.Management.Smo子串。
$db |
Get-Member -MemberType Property |
Where Definition -Like "*Smo*" |
Where Definition -NotLike "*Federation*" |
ForEach-Object {
$type = $_.Name
$db.$type |
Where Name -Like "*$searchstring*" |
ForEach-Object {
$result = New-Object -Type PSObject -Prop @{
"ObjectType"=$type.Replace("Microsoft.SqlServer.
Management.Smo.", "")
"ObjectName"=$_.Name
}
$results += $result
}
}
AI 代码解读
最后，使用Export-Csv cmdlet将结果导出为CSV格式的文件，并在记事本中显示。
#export results to csv file
$file = "C:\Temp\SearchResults.csv"
$results | Export-Csv -Path $file -NoTypeInformation
#display file contents
notepad $file
AI 代码解读
然而，当你查看结果时，你会注意到两个额外的对象，这是在准备部分的使用T-SQL语句中没有捕获到的。如果我们比较这两种途径，PowerShell方式更加完整。除了期待的23行结果，PowerShell也捕获了：
Production – schema对象
ProductDescriptionSchemaCollection – XmlSchemaCollection对象
2.7.4 更多…
另一种方法是使用SMO数据库变量$db的EnumObjects方法来列举对象。
$searchString = "Product"
$db.EnumObjects() |
Where Name -Like "*$searchString*" |
Select DatabaseObjectTypes, Name |
Format-Table –AutoSize
AI 代码解读
是的，还有另外一种备选方案。这个更长且没有那么灵活，但仍能获得所要的结果。你可以通过逐一遍历$db对象属性来查找匹配字符串的对象。
#long version is to enumerate explicitly each object type
$db.Tables | Where Name -Like "*$searchstring*"
$db.StoredProcedures | Where Name -Like "*$searchstring*"
$db.Triggers | Where Name -Like "*$searchstring*"
$db.UserDefinedFunctions | Where Name -Like "*$searchstring*"
#etc
AI 代码解读
这是有用的，如果你准确知道你要查找的对象类型，将会更快。
2.7.5 可参阅…
第1章中的浏览SMO服务器对象
