Microsoft Windows PowerShell and SQL Server 2005 SMO – Part 8

(“Microsoft.SqlServer.Smo”) | out-null
$srv=New-Object “Microsoft.SqlServer.Management.Smo.Server”

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



Fig 1.2

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}



Fig 1.3

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



Fig 1.4

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



Fig 1.5

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

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



Fig 1.6

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.

»


See All Articles by Columnist
MAK

Latest Articles