Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8

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

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.

In this article, I will show you 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: 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: {}
act_Store_CustomerID}
PartitionSchemeParameters: {}
RowCountAsDouble: 753
Triggers: {}
eContact_CustomerID_ContactID}
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

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.DatabaseOptions
Synonyms: {}
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.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 Files Microsoft 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

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 :

1. listobjects is the script listobjects.ps1 in the c: ps directory
2. HOME is the hostname
3. SQLEXPRESS is the name of the sql server example on the HOME host
4. AdventureWorks is the database name residing in SQLEXPRESS
5. UDF is the parameter to display all the User Defined Functions available in the AdventureWorks database

Valid parameters for object types are :

1. UDF for User Defined Functions
2. TABLES for Tables
3. ASM for Assemblies
4. SP for Stored Procedures
5. XP for Extended Stored Procedures
6. VIEWS for views
7. 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: 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

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