开发者社区> 异步社区> 正文

《PowerShell V3——SQL Server 2012数据库自动化运维权威指南》——2.7 查找数据库对象

简介:
+关注继续查看

本节书摘来自异步社区出版社《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]

将会得到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

-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

结果如下:
image

2.7.3 如何实现…

创建SMO服务器对象后,我们需要创建一个AdventureWorks2008R2数据库的SMO数据库句柄。

$databasename = "AdventureWorks2008R2"
$db = $server.Databases[$databasename]

我们也定义了查找字符串。我们的目的是获得名字中含有“Product”的所有数据库对象。

#what keyword are we looking for?
$searchString = "Product"

我们也创建一个空的数据,用于存储查询结果的记录。完成后,我们将以表格的形式显示最后的结果。

$results = @()

我们将会浏览所有的数据库相关的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      
   }
}

在循环中,我们解析并创建结果。

第一部分探测每一个属性,检查名字中是否包含我们的查询字符串。

$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     
   }
}

注意,传递给外层的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       
   }
}

最后,使用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

然而,当你查看结果时,你会注意到两个额外的对象,这是在准备部分的使用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

是的,还有另外一种备选方案。这个更长且没有那么灵活,但仍能获得所要的结果。你可以通过逐一遍历$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

这是有用的,如果你准确知道你要查找的对象类型,将会更快。

2.7.5 可参阅…

第1章中的浏览SMO服务器对象

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
《PowerShell V3——SQL Server 2012数据库自动化运维权威指南》导读
PowerShell V3——SQL Server 2012数据库自动化运维权威指南 PowerShell 是微软的新命令行Shell和脚本语言,可以简化微软不同的应用程序和组件之间的自动化过程和整合过程。
2339 0
+关注
异步社区
异步社区(www.epubit.com)是人民邮电出版社旗下IT专业图书旗舰社区,也是国内领先的IT专业图书社区,致力于优质学习内容的出版和分享,实现了纸书电子书的同步上架,于2015年8月上线运营。公众号【异步图书】,每日赠送异步新书。
文章
问答
视频
文章排行榜
最热
最新
相关电子书
更多
时序数据库TSDB的SQL与流计算
立即下载
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
相关实验场景
更多