Microsoft Windows PowerShell and SQL Server 2005 SMO – Part 8

Part 4 covered how to use a PowerShell script to loop through the content of a file and connect to different servers. Part 5 discussed creating a SQL Server database using PowerShell and SMO. Part 6 examined backing up a SQL Server database using PowerShell and SMO and Part 7 illustrated how to list all of the objects in a database.

This installment illustrates how to use PowerShell in conjunction with SMO to display object properties of all SQL Server Objects.

Method 1

Let’s assume that we want to display the table property of all of the tables in the AdventureWorks database from the “HOME\SQLEXPRESS” server. Execute the following cmdlets as shown below. [Fig 1.1]

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

When the above cmdlets is executed, it displays the table property of all of the tables in the AdventureWorks database, as shown below. [Refer Fig 1.2]

Result

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='HOME\SQLEXPRESS']
                               /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='HOME\SQLEXPRESS']/Database[@Name='adventureworks']/Table[@Name='StoreContact'
                             and @Schema='Sales']
Properties                : {CreateDate, DataSpaceUsed, FakeSystemTable, FileGroup...}
UserData                  :
State                     : Existing

Method 2

Let’s assume that we want to display all of the properties of all kinds of objects in the AdventureWorks database from the “HOME\SQLEXPRESS” server. This can be achieved by executing the following cmdlets shown below. [Refer Fig 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 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}

By executing the above cmdlets, you can see the object property of all of the objects, of different object type in the AdventureWorks database on the “HOME\SQLEXPRESS” server, as shown below. Refer 1.4

Result

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                             : [HOME\SQLEXPRESS]
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                              : HOME\MAK
PrimaryFilePath                    : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
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='HOME\SQLEXPRESS']/Database[@Name='adventureworks']
Properties                         : {ActiveConnections, CompatibilityLevel, CreateDate, DataSpaceUsage...}
UserData                           :
State                              : Existing

Method 3

Let’s combine Method 1 and Method 2 in the form of a PowerShell script that will display the properties of all objects for the given object type and for a given database on a given server. Create c:\ps\DisplayObjectProperty.ps1 as shown below. [Refer Fig 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} 
}

The above PowerShell script can be executed as shown below. Refer Fig 1.6

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

Parameters explained:

• listobjects is actually the listobjects.ps1 script in the folder c:\ps
• HOME is the hostname
• SQLEXPRESS is the sql server instance name on the host HOME
• AdventureWorks is the database name that resides in SQLEXPRESS
• UDF is the parameter, which when passed displays all the User Defined Functions available on 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 property of the specified object from the specified database from the specified server [Refer Fig 1.7]

Result

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='HOME\SQLEXPRESS']/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='HOME\SQLEXPRESS']/Database[@Name='AdventureWorks']/UserDefinedFunction[@Name=
                            'fn_EnumCurrentPrincipals' and @Schema='sys']
Properties                : {AnsiNullsStatus, BodyStartIndex, CreateDate, DataType...}
UserData                  :
State                     : Existing

Conclusion

Part 8 of this series has illustrated how to use PowerShell and SMO to find all of the specified object types and display their properties on a given database on a given server.