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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

本节书摘来自异步社区出版社《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服务器对象

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
13天前
|
SQL 安全 算法
【SQL server】玩转SQL server数据库:第四章 数据库安全性
【SQL server】玩转SQL server数据库:第四章 数据库安全性
75 12
|
13天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库:第二章 关系数据库
【SQL server】玩转SQL server数据库:第二章 关系数据库
77 10
|
13天前
|
SQL 存储 算法
【SQL server】玩转SQL server数据库:第一章 绪论
【SQL server】玩转SQL server数据库:第一章 绪论
44 5
|
13天前
|
SQL 数据库 数据安全/隐私保护
Sql Server数据库Sa密码如何修改
Sql Server数据库Sa密码如何修改
|
13天前
|
SQL 数据库 数据库管理
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(一)模式、表、索引与视图
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(一)模式、表、索引与视图
74 11
|
13天前
|
关系型数据库 MySQL 数据库
用navicat连接数据库报错:1130-host ... is not allowed to connect to this MySql server如何处理
用navicat连接数据库报错:1130-host ... is not allowed to connect to this MySql server如何处理
32 0
|
13天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
124 6
|
5天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
31 2
|
12天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之当 SQL Server 源数据库中的数据更新后,CDC 吐出的操作(op)是怎样的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
13天前
|
SQL 存储 数据库连接
C#SQL Server数据库基本操作(增、删、改、查)
C#SQL Server数据库基本操作(增、删、改、查)
13 0