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

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
Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 7

 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, WMI cmdlets đơn giản. Trong phần 3 chúng tôi đã giới thiệu về cách lập kịch bản cho PowerShell và kết nối SQL Server.

Phần 4 đã giới thiệu cách sử dụng kịch bản PowerShell để lặp trong nội dung file và kết nối các máy chủ khác nhau. Phần 5 là giới thiệu về cách tạo cơ sở dữ liệu SQL Server bằng PowerShell và SMO. Phần 6 là nghiên cứu vấn đề backup một cơ sở dữ liệu SQL Server bằng PowerShell và SMO và phần 7 là cách tạo một danh sách các đối tượng trong một cơ sở dữ liệu.

Trong phần 8 này chúng tôi sẽ giới thiệu cho các bạn cách sử dụng PowerShell kết hợp với SMO để hiển thị các thuộc tính đối tượng cho tất cả các đối tượng SQL Server.

Phương pháp 1

Chúng ta hãy giả dụ rằng sẽ hiển thị đặc tính bảng của tất cả các bảng trong cơ sở dữ liệu AdventureWorks từ máy chủ “HOMESQLEXPRESS”. Thực thi cmdlets dưới đây như thể hiện trong hình 1.1 bên dưới.

[System.Reflection.Assembly]::LoadWithPartialName ("Microsoft.SqlServer.Smo") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "HOMESQLEXPRESS"
$db = $srv.Databases["adventureworks"]
foreach ($tbl in $db.tables) {$tbl}

Hình 1.1

Khi cmdlets ở trên được thực thi, nó sẽ hiển thị đặc tính bảng của tất cả các bảng trong cơ sở dữ liệu AdventureWorks, như thể hiện bên dưới (xem hình 1.2).

Kết quả

Parent                    : [adventureworks]
AnsiNullsStatus           : True
CreateDate                : 4/26/2006 11:44:31 AM
DataSpaceUsed             : 808
DateLastModified          : 4/26/2006 11:45:46 AM
FakeSystemTable           : False
FileGroup                 : PRIMARY
HasAfterTrigger           : True
HasClusteredIndex         : True
HasDeleteTrigger          : False
HasIndex                  : True
HasInsertTrigger          : True
HasInsteadOfTrigger       : False
HasUpdateTrigger          : False
ID                        : 2130106629
IndexSpaceUsed            : 80
IsIndexable               : True
IsPartitioned             : False
IsSystemObject            : False
PartitionScheme           :
QuotedIdentifierStatus    : True
Replicated                : False
RowCount                  : 701
TextFileGroup             : PRIMARY
Events                    : Microsoft.SqlServer.Management.Smo.TableEvents
Checks                    : {}
ForeignKeys               : {FK_Store_Customer_CustomerID,FK_Store_SalesPerson_SalesPersonID}
PartitionSchemeParameters : {}
RowCountAsDouble          : 701
Triggers                  : {iStore}
Indexes                   : {AK_Store_rowguid, IX_Store_SalesPersonID,PK_Store_CustomerID,PXML_Store_Demographics}
Statistics                : {AK_Store_rowguid, IX_Store_SalesPersonID,PK_Store_CustomerID}
ExtendedProperties        : {MS_Description}
Columns                   : {CustomerID, Name, SalesPersonID,Demographics...}
FullTextIndex             :
Schema                    : Sales
Name                      : Store
Urn                       : Server[@Name='HOMESQLEXPRESS']/Database[@Name='adventureworks']
                                /Table[@Name='Store' and @Schema='Sales']
Properties                : {CreateDate, DataSpaceUsed, FakeSystemTable,FileGroup...}
UserData                  :
State                     : Existing

Parent                    : [adventureworks]
AnsiNullsStatus           : True
CreateDate                : 4/26/2006 11:44:31 AM
DataSpaceUsed             : 40
DateLastModified          : 4/26/2006 11:45:45 AM
FakeSystemTable           : False
FileGroup                 : PRIMARY
HasAfterTrigger           : False
HasClusteredIndex         : True
HasDeleteTrigger          : False
HasIndex                  : True
HasInsertTrigger          : False
HasInsteadOfTrigger       : False
HasUpdateTrigger          : False
ID                        : 30623152
IndexSpaceUsed            : 120
IsIndexable               : True
IsPartitioned             : False
IsSystemObject            : False
PartitionScheme           :
QuotedIdentifierStatus    : True
Replicated                : False
RowCount                  : 753
TextFileGroup             :
Events                    : Microsoft.SqlServer.Management.Smo.TableEvents
Checks                    : {}
ForeignKeys               : {FK_StoreContact_Contact_ContactID, FK_StoreContact_ContactType_ContactTypeID, FK_StoreCont
                            act_Store_CustomerID}
PartitionSchemeParameters : {}
RowCountAsDouble          : 753
Triggers                  : {}
Indexes                   : {AK_StoreContact_rowguid, IX_StoreContact_ContactID, IX_StoreContact_ContactTypeID, PK_Stor
                            eContact_CustomerID_ContactID}
Statistics                : {AK_StoreContact_rowguid, IX_StoreContact_ContactID, IX_StoreContact_ContactTypeID, PK_Stor
                            eContact_CustomerID_ContactID}
ExtendedProperties        : {MS_Description}
Columns                   : {CustomerID, ContactID, ContactTypeID, rowguid...}
FullTextIndex             :
Schema                    : Sales
Name                      : StoreContact
Urn                       : Server[@Name='HOMESQLEXPRESS']/Database[@Name='adventureworks']/Table[@Name='StoreContact'
                             and @Schema='Sales']
Properties                : {CreateDate, DataSpaceUsed, FakeSystemTable, FileGroup...}
UserData                  :
State                     : Existing

Hình 1.2

Phương pháp 2

Chúng ta hãy giả dụ rằng sẽ hiển thị đặc tính của tất cả các bảng trong cơ sở dữ liệu AdventureWorks từ máy chủ “HOMESQLEXPRESS”. Vấn đề này có thể được thực hiện theo các cmdlets dưới đây (xem hình 1.3)

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

Hình 1.3

Bằng cách thực thi cmdlets ở trên, bạn có thể thấy đặc tính đối tượng của tất cả các đối tượng, kiểu đối tượng khác nhau trong cơ sở dữ liệu AdventureWorks trên máy chủ “HOMESQLEXPRESS” như thể hiện bên dưới. Xem hình 1.4.

Kết quả

ExtendedProperties                 : {MS_Description}
DatabaseOptions                    : Microsoft.SqlServer.Management.Smo.DatabaseOptions
Synonyms                           : {}
Tables                             : {AWBuildVersion, DatabaseLog, ErrorLog, Department...}
StoredProcedures                   : {uspGetBillOfMaterials, uspGetEmployeeManagers, uspGetManagerEmployees, uspGetWher
                                     eUsedProductID...}
Assemblies                         : {}
UserDefinedTypes                   : {}
UserDefinedAggregates              : {}
FullTextCatalogs                   : {}
Certificates                       : {}
SymmetricKeys                      : {}
AsymmetricKeys                     : {}
ExtendedStoredProcedures           : {sp_AddFunctionalUnitToComponent, sp_batch_params, sp_bindsession, sp_control_dbma
                                     sterkey_password...}
UserDefinedFunctions               : {ufnGetAccountingEndDate, ufnGetAccountingStartDate, ufnGetContactInformation, ufn
                                     GetDocumentStatusText...}
Views                              : {vEmployee, vEmployeeDepartment, vEmployeeDepartmentHistory, vJobCandidate...}
Users                              : {dbo, guest, INFORMATION_SCHEMA, sys}
Schemas                            : {db_accessadmin, db_backupoperator, db_datareader, db_datawriter...}
Roles                              : {db_accessadmin, db_backupoperator, db_datareader, db_datawriter...}
ApplicationRoles                   : {}
LogFiles                           : {AdventureWorks_Log}
FileGroups                         : {PRIMARY}
Defaults                           : {}
Rules                              : {}
UserDefinedDataTypes               : {AccountNumber, Flag, Name, NameStyle...}
XmlSchemaCollections               : {HRResumeSchemaCollection, AdditionalContactInfoSchemaCollection, ManuInstructions
                                     SchemaCollection, ProductDescriptionSchemaCollection...}
PartitionFunctions                 : {}
PartitionSchemes                   : {}
ActiveDirectory                    : [adventureworks]
MasterKey                          :
Triggers                           : {ddlDatabaseTriggerLog}
ServiceBroker                      : Microsoft.SqlServer.Management.Smo.Broker.ServiceBroker
Parent                             : [HOMESQLEXPRESS]
ActiveConnections                  : 0
AutoCreateStatisticsEnabled        : True
AutoUpdateStatisticsEnabled        : True
CaseSensitive                      : False
Collation                          : Latin1_General_CI_AS
CompatibilityLevel                 : Version90
CreateDate                         : 6/26/2007 1:07:37 AM
DatabaseGuid                       : 53b3fe26-b1f1-478a-8421-f7d30ae78ba0
DatabaseSnapshotBaseName           :
DataSpaceUsage                     : 101024
DboLogin                           : True
DefaultFileGroup                   : PRIMARY
DefaultFullTextCatalog             :
DefaultSchema                      : dbo
ID                                 : 9
IndexSpaceUsage                    : 59080
IsAccessible                       : True
IsDatabaseSnapshot                 : False
IsDatabaseSnapshotBase             : False
IsDbAccessAdmin                    : True
IsDbBackupOperator                 : True
IsDbDatareader                     : True
IsDbDatawriter                     : True
IsDbDdlAdmin                       : True
IsDbDenyDatareader                 : False
IsDbDenyDatawriter                 : False
IsDbOwner                          : True
IsDbSecurityAdmin                  : True
IsFullTextEnabled                  : True
IsMailHost                         : False
IsMirroringEnabled                 : False
IsSystemObject                     : False
IsUpdateable                       : True
LastBackupDate                     : 1/1/0001 12:00:00 AM
LastLogBackupDate                  : 1/1/0001 12:00:00 AM
LogReuseWaitStatus                 : Nothing
MirroringFailoverLogSequenceNumber :
MirroringID                        :
MirroringPartner                   :
MirroringPartnerInstance           :
MirroringRoleSequence              :
MirroringSafetyLevel               : None
MirroringSafetySequence            :
MirroringStatus                    : None
MirroringWitness                   :
MirroringWitnessStatus             : None
Owner                              : HOMEMAK
PrimaryFilePath                    : C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA
RecoveryForkGuid                   : fec6dd7c-016d-4aaf-a706-9a0a47917486
ReplicationOptions                 : 0
ServiceBrokerGuid                  : 8778510e-22e8-489d-b934-3b0d71d77302
Size                               : 178.75
SpaceAvailable                     : 16136
Status                             : Normal
UserName                           : dbo
Version                            : 611
Events                             : Microsoft.SqlServer.Management.Smo.DatabaseEvents
Name                               : adventureworks
Urn                                : Server[@Name='HOMESQLEXPRESS']/Database[@Name='adventureworks']
Properties                         : {ActiveConnections, CompatibilityLevel, CreateDate, DataSpaceUsage...}
UserData                           :
State                              : Existing

Hình 1.4

Phương pháp 3

Hãy kết hợp phương pháp 1 và phương pháp 2 thành một kịch bản PowerShell để hiển thị thuộc tính của tất cả các đối tượng cho kiểu đối tượng đã cho và cho cơ sở dữ liệu trên máy chủ đã cho. Tạo c:psDisplayObjectProperty.ps1 như hình bên dưới (xem hình 1.5).

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 Properties"
echo "-----------------"
foreach ($tbl in $db.Tables) {$tbl}
}

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

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

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

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

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

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

Hình 1.5

Kịch bản PowerShell ở trên có thể được thực thi như thể hiện bên dưới, xem hình 1.6

./DisplayObjectProperty "HOMESQLEXPRESS" "AdventureWorks" "UDF"

Hình 1.6

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

• listobjects là kịch bản listobjects.ps1 trong thư mục c:ps
• HOME là hostname
• SQLEXPRESS là tên ví dụ sql server trên host HOME
• AdventureWorks là tên cơ sở dữ liệu cư trú trong SQLEXPRESS
• UDF là tham số để hiển thị tất cả các User Defined Function có trong cơ sở dữ liệu AdventureWorks

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

• UDF cho User Defined Functions
• TABLES cho Tables
• ASM cho Assemblies
• SP cho Stored Procedures
• XP cho Extended Stored Procedures
• VIEWS cho views
• SYNONYMS cho synonyms

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

Kết quả

Parent                    : [AdventureWorks]
AnsiNullsStatus           : True
AssemblyName              :
ClassName                 :
CreateDate                : 4/14/2006 4:01:06 AM
DateLastModified          : 4/14/2006 4:01:06 AM
ExecutionContext          : Caller
ExecutionContextPrincipal :
FunctionType              : Inline
ID                        : -1024577103
ImplementationType        : TransactSql
IsDeterministic           : False
IsEncrypted               : False
IsSchemaBound             : False
IsSystemObject            : True
MethodName                :
QuotedIdentifierStatus    : True
ReturnsNullOnNullInput    :
TableVariableName         :
Events                    : Microsoft.SqlServer.Management.Smo.UserDefinedFunctionEvents
Schema                    : sys
Name                      : fn_dump_dblog
Urn                       : Server[@Name='HOMESQLEXPRESS']/Database[@Name='AdventureWorks']/UserDefinedFunction[@Name=
                            'fn_dump_dblog' and @Schema='sys']
Properties                : {AnsiNullsStatus, BodyStartIndex, CreateDate, DataType...}
UserData                  :
State                     : Existing

ExtendedProperties        : {}
Parameters                : {}
Indexes                   : {}
Columns                   : {db_name, current_principal, mirroring_role, mirroring_state}
Checks                    : {}
DataType                  :
TextBody                  : begin
                            insert into @mirrorinstances
                            select databases.name as db_name,
                                sys.fn_GetCurrentPrincipal(databases.name) as current_principal,
                                db_mirroring.mirroring_role as mirroring_role,
                                db_mirroring.mirroring_state as mirroring_state
                                from sys.database_mirroring db_mirroring, sys.databases databases where
                                db_mirroring.database_id = databases.database_id
                                and (databases.is_published = 1 or databases.is_merge_published = 1)
                                and db_mirroring.mirroring_role is NOT NULL
                            return
                            end

TextHeader                : create function sys.fn_EnumCurrentPrincipals()
                            RETURNS @mirrorinstances TABLE
                            (
                                db_name                        sysname,
                                current_principal            sysname,
                                mirroring_role                int NULL,
                                mirroring_state                int NULL
                            )
                            as

TextMode                  : True
Parent                    : [AdventureWorks]
AnsiNullsStatus           : True
AssemblyName              :
ClassName                 :
CreateDate                : 4/14/2006 4:03:46 AM
DateLastModified          : 4/14/2006 4:03:46 AM
ExecutionContext          : Caller
ExecutionContextPrincipal :
FunctionType              : Table
ID                        : -485928087
ImplementationType        : TransactSql
IsDeterministic           : False
IsEncrypted               : False
IsSchemaBound             : False
IsSystemObject            : True
MethodName                :
QuotedIdentifierStatus    : True
ReturnsNullOnNullInput    :
TableVariableName         : @mirrorinstances
Events                    : Microsoft.SqlServer.Management.Smo.UserDefinedFunctionEvents
Schema                    : sys
Name                      : fn_EnumCurrentPrincipals
Urn                       : Server[@Name='HOMESQLEXPRESS']/Database[@Name='AdventureWorks']/UserDefinedFunction[@Name=
                            'fn_EnumCurrentPrincipals' and @Schema='sys']
Properties                : {AnsiNullsStatus, BodyStartIndex, CreateDate, DataType...}
UserData                  :
State                     : Existing

Kết luận

Phần 8 của loạt bài này đã giới thiệu cho các bạn cách sử dụng PowerShell và SMO để tìm tất cả các kiểu đối tượng cụ thể và hiển thị các thuộc tính của nó trong một cơ sở dữ liệu đã cho trên máy chủ đã cho.

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