Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 7

Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 1
Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 2
Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 3
Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 4
Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 5
Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 6

The MAK

Phần 1 và phần 2 của loạt bài này chúng tôi đã giới thiệu về cài đặt PowerShell và SMO đơn giản, WMI cmdlet. Trong phần 3 chúng tôi giới thiệu về cách lập kịch bản PowerShell để kết nối đến máy chủ SQL Server.

Phần 4 giới thiệu cách sử dụng kịch bản PowerShell để thực hiện hành động lặp một file và kết nối các máy chủ khác. Phần 5 giới thiệu cho các bạn cách tạo một cơ sở dữ liệu SQL Server bằng PowerShell và SMO. Phần 6 giới thiệu về việc backup cơ sở dữ liệu SQL Server bằng PowerShell và SMO.

Mỗi một phần trong loạt bài này đều minh chứng cách sử dụng PowerShell kết hợp với SMO để trình bày các đối tượng của SQL Server.

Phương pháp 1: Hiển thị các tên bảng

Chúng ta hãy giả dụ rằng mình muốn tìm tất cả các bảng đã có trong cơ sở dữ liệu “AdventureWorks”, trên máy chủ “HOME\SQLEXPRESS”. Thực thi lệnh dưới đây, tham khảo hình 1.1.

[reflection.assembly]:: LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "HOME\SQLEXPRESS"
$db = $srv.Databases["adventureworks"]
foreach ($tbl in $db.tables) {$tbl.name}

Hình 1.1

Cmdlets ở trên sẽ hiển thị các tên bảng trong cơ sở dữ liệu AdventureWorks trên máy chủ “HOME\SQLEXPRESS” (xem hình 1.2)

Kết quả

AWBuildVersion
DatabaseLog
ErrorLog
Department
Employee
EmployeeAddress
EmployeeDepartmentHistory
EmployeePayHistory
JobCandidate
Shift
Address
AddressType
Contact
ContactType
CountryRegion
StateProvince
BillOfMaterials
Culture
Document
Illustration
Location
Product
ProductCategory
ProductCostHistory
ProductDescription
ProductDocument
ProductInventory
ProductListPriceHistory
ProductModel
ProductModelIllustration
ProductModelProductDescriptionCulture
ProductPhoto
ProductProductPhoto
ProductReview
ProductSubcategory
ScrapReason
TransactionHistory
TransactionHistoryArchive
UnitMeasure
WorkOrder
WorkOrderRouting
ProductVendor
PurchaseOrderDetail
PurchaseOrderHeader
ShipMethod
Vendor
VendorAddress
VendorContact
ContactCreditCard
CountryRegionCurrency
CreditCard
Currency
CurrencyRate
Customer
CustomerAddress
Individual
SalesOrderDetail
SalesOrderHeader
SalesOrderHeaderSalesReason
SalesPerson
SalesPersonQuotaHistory
SalesReason
SalesTaxRate
SalesTerritory
SalesTerritoryHistory
ShoppingCartItem
SpecialOffer
SpecialOfferProduct
Store
StoreContact

Hình 1.2

Phương pháp 2

Giả dụ rằng bạn muốn tìm tất cả các đối tượng đã có trong cơ sở dữ liệu “AdventureWorks”, trên máy chủ “HOME\SQLEXPRESS”. Thực thi lệnh dưới đây, tham khảo hình 1.3.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "HOME\SQLEXPRESS"
$db = $srv.Databases["adventureworks"]
echo "Tables"
echo "------"
foreach ($tbl in $db.Tables) {$tbl.name}
echo "Synonyms"
echo "------"
foreach ($Synonyms in $db.Synonyms) {$Synonyms.name}
echo "Stored Procedures"
echo "------"
foreach ($StoredProcedures in $db.StoredProcedures) {$StoredProcedures.name}
echo "Assemblies"
echo "------"
foreach ($Assemblies in $db.Assemblies) {$Assemblies.name}
echo "UserDefined Functions"
echo "------"
foreach ($UserDefinedFunctions in $db.UserDefinedFunctions) {$UserDefinedFunctions.name}
echo "Views"
echo "------"
foreach ($Views in $db.Views) {$Views.name}
echo "ExtendedStoredProcedures"
echo "------"
foreach ($ExtendedStoredProcedures in $db.ExtendedStoredProcedures) {$ExtendedStoredProcedures.name}

Hình 1.3

Cmdlets ở trên sẽ hiển thị các tên đối tượng trong cơ sở dữ liệu AdventureWorks trên máy chủ “HOME\SQLEXPRESS” (xem hình 1.4)

Kết quả

Hình 1.4

Phương pháp 3

Hãy nối phương pháp 1 và phương pháp 2 thành một biểu mẫu kịch bản PowerShell để chấp nhận các tham số như bên dưới. Tạo file listobjects.ps1 như bên dưới.

param
(
[string] $ServerName,
[string] $DatabaseName,
[string] $ObjectType
)

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$ServerName"
$db = $srv.Databases["$DatabaseName"]

if ($ObjectType -eq "TABLES")
{
echo "Tables"
echo "------"
foreach ($tbl in $db.Tables) {$tbl.name}
}

if ($ObjectType -eq "SYNONYMS")
{
echo "Synonyms"
echo "--------"
foreach ($Synonyms in $db.Synonyms) {$Synonyms.name}
}

if ($ObjectType -eq "SP")
{
echo "Stored Procedures"
echo "------------------"
foreach ($StoredProcedures in $db.StoredProcedures) {$StoredProcedures.name}
}

if ($ObjectType -eq "ASM")
{
echo "Assemblies"
echo "----------"
foreach ($Assemblies in $db.Assemblies) {$Assemblies.name}
}

if ($ObjectType -eq "UDF")
{
echo "UserDefined Functions"
echo "---------------------"
foreach ($UserDefinedFunctions in $db.UserDefinedFunctions)
{$UserDefinedFunctions.name}
}

if ($ObjectType -eq "VIEWS")
{echo "Views"
echo "------"
foreach ($Views in $db.Views) {$Views.name}
}

if ($ObjectType -eq "XP")
{
echo "ExtendedStoredProcedures"
echo "------------------------"
foreach ($ExtendedStoredProcedures in $db.ExtendedStoredProcedures)
{$ExtendedStoredProcedures.name}
}

Hình 1.5

Bây giờ thực thi file listobjects.ps1 như bên dưới (xem hình 1.6)

./listobjects "HOME\SQLEXPRESS" "AdventureWorks" "UDF"

Hình 1.6

Giải thích về các tham số

listobjects là file kịch bản listobjects.ps1 trong thư mục c:\ps.

HOME là tên cấu hình

SQLEXPRESS là tên máy chủ SQL trên cấu hình chủ có tên HOME

AdventureWorks là tên cơ sở dữ liệu cư trú trong SQLEXPRESS.

UDF là một tham số, tham số này dùng để hiển thị tất cả các chức năng định nghĩa của người dùng trong cơ sở dữ liệu AdventureWorks.

Tham số hợp lệ cho các kiểu đối tượng là

UDF - User Defined Functions

TABLES - Tables

ASM - Assemblies

SP - Stored Procedures

XP - Extended Stored Procedures

VIEWS - views

SYNONYMS - synonyms

Kịch bản PowerShell ở trên hiển thị các tên của các đối tượng của một cơ sở dữ liệu cụ thể trong máy chủ. (xem hình 1.7)

Kết quả

UserDefined Functions
---------------------
ufnGetAccountingEndDate
ufnGetAccountingStartDate
ufnGetContactInformation
ufnGetDocumentStatusText
ufnGetProductDealerPrice
ufnGetProductListPrice
ufnGetProductStandardCost
ufnGetPurchaseOrderStatusText
ufnGetSalesOrderStatusText
ufnGetStock
ufnLeadingZeros
dm_db_index_operational_stats
dm_db_index_physical_stats
dm_db_missing_index_columns
dm_exec_cached_plan_dependent_objects
dm_exec_cursors
dm_exec_plan_attributes
dm_exec_query_plan
dm_exec_sql_text
dm_exec_xml_handles
dm_io_virtual_file_stats
fn_builtin_permissions
fn_cColvEntries_80
fn_check_object_signatures
fn_dblog
fn_dump_dblog
fn_EnumCurrentPrincipals
fn_fIsColTracked
fn_get_sql
fn_GetCurrentPrincipal
fn_GetRowsetIdFromRowDump
fn_helpcollations
fn_helpdatatypemap
fn_IsBitSetInBitmask
fn_isrolemember
fn_listextendedproperty
fn_MapSchemaType
fn_MSdayasnumber
fn_MSgeneration_downloadonly
fn_MSget_dynamic_filter_login
fn_MSorbitmaps
fn_MSrepl_map_resolver_clsid
fn_MStestbit
fn_MSvector_downloadonly
fn_my_permissions
fn_numberOf1InBinaryAfterLoc
fn_numberOf1InVarBinary
fn_repladjustcolumnmap
fn_repldecryptver4
fn_replformatdatetime
fn_replgetcolidfrombitmap
fn_replgetparsedddlcmd
fn_replreplacesinglequote
fn_replreplacesinglequoteplusprotectstring
fn_repluniquename
fn_RowDumpCracker
fn_servershareddrives
fn_sqlvarbasetostr
fn_trace_geteventinfo
fn_trace_getfilterinfo
fn_trace_getinfo
fn_trace_gettable
fn_translate_permissions
fn_varbintohexstr
fn_varbintohexsubstring
fn_virtualfilestats
fn_virtualservernodes
fn_yukonsecuritymodelrequired

Hình 1.7

Kết luận

Phần 7 của loạt bài này chúng tôi đã minh chứng về cách sử dụng PowerShell và SMO để tìm tất cả các đối tượng có sẵn trong một cơ sở dữ liệu trên máy chủ.

Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 8
Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 9
Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 10
Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 11