在SQL Server中,可能存在一些注册的汇编,这些汇编可能引用其他汇编,并且不同的数据库中可能存在相同汇编的不同版本。本文的目的是创建一个脚本,报告所有数据库中所有汇编的相关信息。
以下是本文脚本生成的报告示例(这是一个HTML页面):
报告的解释将在本文稍后进行。
本脚本使用以下工具/库创建:
脚本环境:
获取信息:
使用了SMO、CLRCatalog视图和sp_helpuser方法的混合,因为有些信息不能使用一种方法检索,但可以使用另一种方法检索,或者使用一种方法获取信息比另一种方法更快。
这是微软的命令行/脚本环境。它建立在Microsoft .NET Framework上,提供了命令行环境和脚本语言。脚本是用这种语言编写的。如果系统中尚未安装,可以从以下网站获取:
http://support.microsoft.com/kb/968929
(如果安装了SQL Server 2008或更高版本,通常已经安装了它)。
记事本足以创建/编辑脚本。但是,为了拥有其他便于脚本编写的功能,如:IntelliSense、代码补全、即时语法检查等,最好有一个良好的PowerShell IDE。安装PowerShell时也会安装一个IDE,可以在开始菜单/附件/Windows PowerShell/Windows PowerShell ISE中找到。使用了PowerGui脚本编辑器,这是一个非常好的PowerShell脚本编辑器,可以从以下网站免费下载:
http://powergui.org
这是一组设计用于编程管理Microsoft SQL Server各个方面的对象。它允许做SQL Server Management Studio应用程序可以做的事情。如果系统中尚未安装,则:
http://www.microsoft.com/download/en/details.aspx?id=29065
(在同一页面中搜索“Microsoft® SystemCLRTypes for Microsoft®SQL Server® 2012”并根据系统选择x86或x64包)。
http://www.microsoft.com/download/en/details.aspx?id=29065
(在同一页面中搜索“Microsoft® SQL Server® 2012 Shared Management Objects”并根据系统选择x86或x64包)。
SMO 2012与SQL Server 2005及更高版本兼容。
这是一组视图,使能够检索当前数据库中注册的汇编的信息。它们可以在SQL ServerManagement Studio中的每个数据库下的“系统视图”节点下可视化:
这是一个存储过程,报告当前数据库中SQL Server用户、角色和登录的信息。它位于SQL Server Management Studio中的每个数据库下的“系统存储过程”节点下:
脚本定义了唯一的必选参数(服务器名称),必须在脚本命令行中给出(可以从本页顶部的链接下载完整脚本):
C# param ([Parameter(Mandatory=$true)] [string]$server)
然后加载SMO库:
C# # Load-SMO assembly [Void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
创建一个代表服务器实例的服务器对象:
C# # Get the server-object $serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
现在可以通过这个$serverInstance对象获取所有SQL Server信息。循环遍历所有服务器的数据库并获取它们的信息:
C# foreach ($database in $serverInstance.Databases) { $htmlServerInfo += GetDatabaseInfo $database $htmlServerInfo += GetAssembliesInfo $database $htmlServerInfo += GetAssembliesModulesInfo $database foreach ($assembly in $database.Assemblies) { if (-not $users.ContainsKey($assembly.Owner)) { $userDataset = $database.ExecuteWithResults('sp_helpuser') $userRow = $userDataset.Tables[0].Rows | where {$_.UserName -eq $assembly.Owner} $login = $serverInstance.Logins[$userRow.LoginName] $user = New-Object -TypeName psobject $users[$assembly.Owner]= $user | Add-Member -MemberType NoteProperty –Name Name –Value $assembly.Owner –PassThru | Add-Member -MemberType NoteProperty –Name LoginName –Value $userRow.LoginName –PassThru | Add-Member -MemberType NoteProperty –Name LoginType –Value $login.LoginType –PassThru | Add-Member -MemberType NoteProperty –Name AsymmetricKey –Value $login.AsymmetricKey –PassThru } } $htmlServerInfo += '' } $htmlServerInfo += GetUserInfo $users
上述脚本片段:
这些函数中的每一个都将其信息转换为HTML表格,通过Cmdlet ConvertTo-Html。使用了这个Cmdlet的-Fragment参数,以便只生成HTML表格。HTML、HEAD、TITLE和BODY标签被省略。
所有HTML转换的信息片段都被连接起来,并提供给Cmdlet ConvertTo-Html,定义了HTML页面样式。这个Cmdlet将完整页面写入名为SQLServerCLrAssemblies.htm的文件。该文件位于C:\Documents and Settings\
要运行脚本:
开始菜单/所有程序/附件/WindowsPowerShell/Windows PowerShell。
Set-ExecutionPolicy RemoteSigned
输入脚本文件的完整路径,加上必选参数–server,即SQL Server名称,然后按Enter。
本节将解释生成报告中显示的一些信息片段。
第一张表格:
第二张表格:
第三张表格:
它显示了汇编的注册模块(存储过程、函数...)以及引用它的汇编。例如,注意到汇编SampleLibrary有两个模块:SampleSP,这是一个存储过程,GetBookCount,这是一个函数。这个汇编引用了两个汇编:Tools和MyMessages汇编。
用户:汇编的所有者名称。
登录名:所有者的登录名。
登录类型:登录的登录类型。例如,SampleLibraryLogin具有AsymmetricKey登录类型。这个登录是使用名为SampleLibraryKey的非对称密钥创建的。
非对称密钥:非对称密钥名称。例如,非对称密钥SampleLibraryKey是使用汇编SampleLibrary的公钥创建的。