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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

本节书摘来自异步社区出版社《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
相关文章
|
5天前
|
SQL Oracle 关系型数据库
|
4天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
24 3
|
9天前
|
SQL 监控 安全
数据库安全:SQL注入防御实践
【7月更文挑战第11天】SQL注入攻击作为一种常见的网络攻击手段,对数据库的安全性和业务稳定构成了严重威胁。为了有效防御SQL注入攻击,开发者和数据库管理员应采取一系列实践措施,包括输入验证与过滤、使用参数化查询、限制数据库用户权限、使用Web应用程序防火墙、定期更新和打补丁、实施实时监控和审计以及使用HTTPS协议等。通过这些措施的实施,可以显著提升数据库的安全性,降低遭受SQL注入攻击的风险。同时,开发者和数据库管理员应持续关注新的安全威胁和防御技术,不断提升自身的安全防护能力。
|
10天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
15天前
|
SQL 自然语言处理 网络协议
【Linux开发实战指南】基于TCP、进程数据结构与SQL数据库:构建在线云词典系统(含注册、登录、查询、历史记录管理功能及源码分享)
TCP(Transmission Control Protocol)连接是互联网上最常用的一种面向连接、可靠的、基于字节流的传输层通信协议。建立TCP连接需要经过著名的“三次握手”过程: 1. SYN(同步序列编号):客户端发送一个SYN包给服务器,并进入SYN_SEND状态,等待服务器确认。 2. SYN-ACK:服务器收到SYN包后,回应一个SYN-ACK(SYN+ACKnowledgment)包,告诉客户端其接收到了请求,并同意建立连接,此时服务器进入SYN_RECV状态。 3. ACK(确认字符):客户端收到服务器的SYN-ACK包后,发送一个ACK包给服务器,确认收到了服务器的确
134 1
|
16天前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
24 2
|
10天前
|
SQL Java 关系型数据库
Java面试题:描述JDBC的工作原理,包括连接数据库、执行SQL语句等步骤。
Java面试题:描述JDBC的工作原理,包括连接数据库、执行SQL语句等步骤。
20 0
|
10天前
|
SQL 监控 Java
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
20 0
|
18天前
|
SQL 存储 搜索推荐
SQL游标的原理与在数据库操作中的应用
SQL游标的原理与在数据库操作中的应用
|
15天前
|
数据采集 存储 API
在信息时代,Python爬虫用于自动化网络数据采集,提高效率。
【7月更文挑战第5天】在信息时代,Python爬虫用于自动化网络数据采集,提高效率。基本概念包括发送HTTP请求、解析HTML、存储数据及异常处理。常用库有requests(发送请求)和BeautifulSoup(解析HTML)。基本流程:导入库,发送GET请求,解析网页提取数据,存储结果,并处理异常。应用案例涉及抓取新闻、商品信息等。
39 2

热门文章

最新文章