Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8September 19, 2007 Part 1 and Part 2 of this series discussed PowerShell installation and simple SMO, WMI cmdlets. Part 3 discussed how to script PowerShell and connect to SQL Server. 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
Click for larger image 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.
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:
Valid parameters for object types are:
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
ConclusionPart 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. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
("Microsoft.SqlServer.Smo") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server"
