Microsoft Windows Powershell and SQL Server 2005 SMO - Part 8

Use this guide to understand Microsoft Windows powershell and SQL server 2005 smo - part 8 and the factors that affect it. The information is organized to make the topic easier to apply in practice.

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 2 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 4 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7

How Microsoft Windows Powershell and SQL Server 2005 SMO - Part 8 Works

The MAK

Part 1 and Part 2 of this series introduced PowerShell and SMO, WMI cmdlets simple installation.In Part 3, I explained how to script PowerShell and connect to SQL Server. Part 4 introduced how to use PowerShell script to iterate in file content and connect different servers. Part 5 is an introduction to creating a SQL Server database using PowerShell and SMO. Part 6 examines the problem of backing up a SQL Server database with PowerShell and SMO and Part 7 is how to create a list of objects in a database. This guide explains how to use PowerShell in conjunction with SMO to display object properties for all SQL Server objects. Method 1 Let's assume that will display the table properties of all tables in the AdventureWorks database from the server ' HOMESQLEXPRESS '. Execute the following cmdlets as shown in Figure 1.1 below.

[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}

Figure 1.1

When the above cmdlets are executed, it will display the table properties of all tables in the AdventureWorks database, as shown below (see Figure 1.2). Result

Parent: [adventureworks]AnsiNullsStatus: TrueCreateDate: 4/26/2006 11:44:31 AMDataSpaceUsed: 808DateLastModified: 4/26/2006 11:45:46 AMFakeSystemTable: FalseFileGroup: PRIMARYHasAfterTrigger: TrueHasClusteredIndex: TrueHasDeleteTrigger: FalseHasIndex: TrueHasInsertTrigger: TrueHasInsteadOfTrigger: FalseHasUpdateTrigger: FalseID: 2130106629IndexSpaceUsed: 80IsIndexable: TrueIsPartitioned: FalseIsSystemObject: FalsePartitionScheme:QuotedIdentifierStatus: TrueReplicated: FalseRowCount: 701TextFileGroup: PRIMARYEvents: Microsoft. SqlServer. Management. Smo. TableEventsChecks: {}ForeignKeys: {FK_Store_Customer_CustomerID, FK_Store_SalesPerson_SalesPersonID}PartitionSchemeParameters: {}RowCountAsDouble: 701Triggers: {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: SalesName: StoreUrn: Server [@ Name = 'HOMESQLEXPRESS'] / Database [@ Name = 'adventureworks']/ Table [@ Name = 'Store' and @ Schema = 'Sales']Properties: {CreateDate, DataSpaceUsed, FakeSystemTable, FileGroup.}UserData:State: ExistingParent: [adventureworks]AnsiNullsStatus: TrueCreateDate: 4/26/2006 11:44:31 AMDataSpaceUsed: 40DateLastModified: 4/26/2006 11:45:45 AMFakeSystemTable: FalseFileGroup: PRIMARYHasAfterTrigger: FalseHasClusteredIndex: TrueHasDeleteTrigger: FalseHasIndex: TrueHasInsertTrigger: FalseHasInsteadOfTrigger: FalseHasUpdateTrigger: FalseID: 30623152IndexSpaceUsed: 120IsIndexable: TrueIsPartitioned: FalseIsSystemObject: FalsePartitionScheme:QuotedIdentifierStatus: TrueReplicated: FalseRowCount: 753TextFileGroup:Events: Microsoft. SqlServer. Management. Smo. TableEventsChecks: {}Act_Store_CustomerID}PartitionSchemeParameters: {}RowCountAsDouble: 753Triggers: {}EContact_CustomerID_ContactID}EContact_CustomerID_ContactID}ExtendedProperties: {MS_Description}Columns: {CustomerID, ContactID, ContactTypeID, rowguid.}FullTextIndex:Schema: SalesName: StoreContactUrn: Server [@ Name = 'HOMESQLEXPRESS'] / Database [@ Name = 'adventureworks'] / Table [@ Name = 'StoreContact'And @ Schema = 'Sales']Properties: {CreateDate, DataSpaceUsed, FakeSystemTable, FileGroup.}UserData:State: Existing

Figure 1.2

Method 2 Let's assume that will display the properties of all tables in the AdventureWorks database from the server ' HOMESQLEXPRESS '. This problem can be done according to the following cmdlets (see Figure 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 "User Defined 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}

Figure 1.3

By executing cmdlets above, you can see the object properties of all objects, different object types in the AdventureWorks database on the server 'HOMESQLEXPRESS' as shown below. See Figure 1.4. Result

ExtendedProperties: {MS_Description}DatabaseOptions: Microsoft. SqlServer. Management. Smo. DatabaseOptionsSynonyms: {}Tables: {AWBuildVersion, DatabaseLog, ErrorLog, Department.}EUsedProductID.}Assemblies: {}UserDefinedTypes: {}UserDefinedAggregates: {}FullTextCatalogs: {}Certificates: {}SymmetricKeys: {}AsymmetricKeys: {}Sterkey_password.}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.}SchemaCollection, ProductDescriptionSchemaCollection.}PartitionFunctions: {}PartitionSchemes: {}ActiveDirectory: [adventureworks]MasterKey:Triggers: {ddlDatabaseTriggerLog}ServiceBroker: Microsoft. SqlServer. Management. Smo. Broker. ServiceBrokerParent: [HOMESQLEXPRESS]ActiveConnections: 0AutoCreateStatisticsEnabled: TrueAutoUpdateStatisticsEnabled: TrueCaseSensitive: FalseCollation: Latin1_General_CI_ASCompatibilityLevel: Version90CreateDate: 6/26/2007 1:07:37 AMDatabaseGuid: 53b3fe26-b1f1-478a-8421-f7d30ae78ba0DatabaseSnapshotBaseName:DataSpaceUsage: 101024DboLogin: TrueDefaultFileGroup: PRIMARYDefaultFullTextCatalog:DefaultSchema: dboID: 9IndexSpaceUsage: 59080IsAccessible: TrueIsDatabaseSnapshot: FalseIsDatabaseSnapshotBase: FalseIsDbAccessAdmin: TrueIsDbBackupOperator: TrueIsDbDatareader: TrueIsDbDatawriter: TrueIsDbDdlAdmin: TrueIsDbDenyDatareader: FalseIsDbDenyDatawriter: FalseIsDbOwner: TrueIsDbSecurityAdmin: TrueIsFullTextEnabled: TrueIsMailHost: FalseIsMirroringEnabled: FalseIsSystemObject: FalseIsUpdateable: TrueLastBackupDate: 1/1/0001 12:00:00 AMLastLogBackupDate: 1/1/0001 12:00:00 AMLogReuseWaitStatus: NothingMirroringFailoverLogSequenceNumber:MirroringID:MirroringPartner:MirroringPartnerInstance:MirroringRoleSequence:MirroringSafetyLevel: NoneMirroringSafetySequence:MirroringStatus: NoneMirroringWitness:MirroringWitnessStatus: NoneOwner: HOMEMAKPrimaryFilePath: C: Program Files Microsoft SQL ServerMSSQL.1MSSQLDATARecoveryForkGuid: fec6dd7c-016d-4aaf-a706-9a0a47917486ReplicationOptions: 0ServiceBrokerGuid: 8778510e-22e8-489d-b934-3b0d71d77302Size: 178.75SpaceAvailable: 16136Status: NormalUserName: dboVersion: 611Events: Microsoft. SqlServer. Management. Smo. DatabaseEventsName: adventureworksUrn: Server [@ Name = 'HOMESQLEXPRESS'] / Database [@ Name = 'adventureworks']Properties: {ActiveConnections, CompatibilityLevel, CreateDate, DataSpaceUsage.}UserData:State: Existing

Figure 1.4

Method 3 Combine methods 1 and method 2 into a PowerShell script to display the properties of all objects for the given object type and for the database on the given server. Create C: psDisplayObjectProperty. ps1 As shown below (see Figure 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 "User Defined Functions Properties"Echo "---------------------"Foreach ($ UserDefinedFunctions in $ db. UserDefinedFunctions) {$ UserDefinedFunctions}}If ($ ObjectType -eq "VIEWS")Foreach ($ Views in $ db. Views) {$ Views}}If ($ ObjectType -eq "XP"){Echo "ExtendedStoredProcedures Properties"Echo "------------------------"Foreach ($ ExtendedStoredProcedures in $ db. ExtendedStoredProcedures) {$ ExtendedStoredProcedures}}

Figure 1.5

The above PowerShell script can be executed as shown below, see Figure 1.6

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

Figure 1.6

Explain the parameters:

• Listobjects Is the script listobjects. ps1 in the c: ps directory
• HOME Is the hostname
• SQLEXPRESS Is the name of the SQL server example on the HOME host
• AdventureWorks Is the database name residing in SQLEXPRESS
• UDF Is the parameter to display all users Defined Functions available in the AdventureWorks database

Valid parameters for object types are:

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

The above PowerShell script displays the properties of a specific object from a specific database in a specific server (Figure 1.7). Result

Parent: [AdventureWorks]AnsiNullsStatus: TrueAssemblyName:ClassName:CreateDate: 4/14/2006 4:01:06 AMDateLastModified: 4/14/2006 4:01:06 AMExecutionContext: CallerExecutionContextPrincipal:FunctionType: InlineID: -1024577103ImplementationType: TransactSqlIsDeterministic: FalseIsEncrypted: FalseIsSchemaBound: FalseIsSystemObject: TrueMethodName:QuotedIdentifierStatus: TrueReturnsNullOnNullInput:TableVariableName:Events: Microsoft. SqlServer. Management. Smo. UserDefinedFunctionEventsSchema: sysName: fn_dump_dblogUrn: Server [@ Name = 'HOMESQLEXPRESS'] / Database [@ Name = 'AdventureWorks'] / UserDefinedFunction [@ Name ='fn_dump_dblog' and @ Schema = 'sys']Properties: {AnsiNullsStatus, BodyStartIndex, CreateDate, DataType.}UserData:State: ExistingExtendedProperties: {}Parameters: {}Indexes: {}Columns: {db_name, current_principal, mirroring_role, mirroring_state}Checks: {}DataType:TextBody: beginInsert into @mirrorinstancesSelect 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_stateFrom sys. database_mirroring db_mirroring, sys. databases databases whereDb_mirroring. database_id = databases. database_idAnd (databases. is_published = 1 or databases. is_merge_published = 1)And db_mirroring. mirroring_role is NOT NULLReturnEndTextHeader: create function sys. fn_EnumCurrentPrincipals ()RETURNS @mirrorinstances TABLE(Db_name sysname,Current_principal sysname,Mirroring_role int NULL,Mirroring_state int NULL)asTextMode: TrueParent: [AdventureWorks]AnsiNullsStatus: TrueAssemblyName:ClassName:CreateDate: 4/14/2006 4:03:46 AMDateLastModified: 4/14/2006 4:03:46 AMExecutionContext: CallerExecutionContextPrincipal:FunctionType: TableID: -485928087ImplementationType: TransactSqlIsDeterministic: FalseIsEncrypted: FalseIsSchemaBound: FalseIsSystemObject: TrueMethodName:QuotedIdentifierStatus: TrueReturnsNullOnNullInput:TableVariableName: @mirrorinstancesEvents: Microsoft. SqlServer. Management. Smo. UserDefinedFunctionEventsSchema: sysName: fn_EnumCurrentPrincipalsUrn: Server [@ Name = 'HOMESQLEXPRESS'] / Database [@ Name = 'AdventureWorks'] / UserDefinedFunction [@ Name ='fn_EnumCurrentPrincipals' and @ Schema = 'sys']Properties: {AnsiNullsStatus, BodyStartIndex, CreateDate, DataType.}UserData:State: Existing

Conclude Part 8 of this series showed you how to use PowerShell and SMO to find all of the specific object types and display its properties in a given database on a given server.

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11