Microsoft Windows PowerShell and SQL Server 2005 SMO – Part 7

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

The above cmdlets display the tables names from the AdventureWorks
database on the “HOME\SQLEXPRESS” server. [Refer Fig 1.2]

Result


AWBuildVersion
DatabaseLog
ErrorLog
Department
Employee
EmployeeAddress
EmployeeDepartmentHistory
EmployeePayHistory
JobCandidate
Shift
Address
AddressType
Contact
ContactType
CountryRegion
StateProvince
BillOfMaterials
Culture
Document
Illustration
Location
Product
ProductCategory
ProductCostHistory
ProductDescription
ProductDocument
ProductInventory
ProductListPriceHistory
ProductModel
ProductModelIllustration
ProductModelProductDescriptionCulture
ProductPhoto
ProductProductPhoto
ProductReview
ProductSubcategory
ScrapReason
TransactionHistory
TransactionHistoryArchive
UnitMeasure
WorkOrder
WorkOrderRouting
ProductVendor
PurchaseOrderDetail
PurchaseOrderHeader
ShipMethod
Vendor
VendorAddress
VendorContact
ContactCreditCard
CountryRegionCurrency
CreditCard
Currency
CurrencyRate
Customer
CustomerAddress
Individual
SalesOrderDetail
SalesOrderHeader
SalesOrderHeaderSalesReason
SalesPerson
SalesPersonQuotaHistory
SalesReason
SalesTaxRate
SalesTerritory
SalesTerritoryHistory
ShoppingCartItem
SpecialOffer
SpecialOfferProduct
Store
StoreContact



Fig 1.2

Method 2

Let’s assume that we want to find all of the objects
available on the “AdventureWorks” database, on the “HOME\SQLEXPRESS” server.
Execute the following command as 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”
echo “——”
foreach ($tbl in $db.Tables) {$tbl.name}
echo “Synonyms”
echo “——”
foreach ($Synonyms in $db.Synonyms) {$Synonyms.name}
echo “Stored Procedures”
echo “——”
foreach ($StoredProcedures in $db.StoredProcedures) {$StoredProcedures.name}
echo “Assemblies”
echo “——”
foreach ($Assemblies in $db.Assemblies) {$Assemblies.name}
echo “UserDefined Functions”
echo “——”
foreach ($UserDefinedFunctions in $db.UserDefinedFunctions) {$UserDefinedFunctions.name}
echo “Views”
echo “——”
foreach ($Views in $db.Views) {$Views.name}
echo “ExtendedStoredProcedures”
echo “——”
foreach ($ExtendedStoredProcedures in $db.ExtendedStoredProcedures) {$ExtendedStoredProcedures.name}



Fig 1.3

The above cmdlets display the object names from the “AdventureWorks”
database, on the “HOME\SQLEXPRESS” server. [Refer Fig 1.4]

Results



Fig 1.4

Method 3

Let’s join Method 1 and Method 2 in the form of a PowerShell script to accept parameters as
shown below. Create listobjects.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”
echo “——”
foreach ($tbl in $db.Tables) {$tbl.name}
}

if ($ObjectType -eq “SYNONYMS”)
{
echo “Synonyms”
echo “——–”
foreach ($Synonyms in $db.Synonyms) {$Synonyms.name}
}

if ($ObjectType -eq “SP”)
{
echo “Stored Procedures”
echo “——————”
foreach ($StoredProcedures in $db.StoredProcedures) {$StoredProcedures.name}
}

if ($ObjectType -eq “ASM”)
{
echo “Assemblies”
echo “———-”
foreach ($Assemblies in $db.Assemblies) {$Assemblies.name}
}

if ($ObjectType -eq “UDF”)
{
echo “UserDefined Functions”
echo “———————”
foreach ($UserDefinedFunctions in $db.UserDefinedFunctions)
{$UserDefinedFunctions.name}
}

if ($ObjectType -eq “VIEWS”)
{echo “Views”
echo “——”
foreach ($Views in $db.Views) {$Views.name}
}

if ($ObjectType -eq “XP”)
{
echo “ExtendedStoredProcedures”
echo “————————”
foreach ($ExtendedStoredProcedures in $db.ExtendedStoredProcedures)
{$ExtendedStoredProcedures.name}
}



Fig 1.5

Now
execute the listobjects.ps1 as shown below. [Refer Fig 1.6]

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



Fig 1.6

Parameters explained

listobjects is actually 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 – User
Defined Functions

TABLES –
Tables

ASM – Assemblies

SP – Stored
Procedures

XP – Extended
Stored Procedures

VIEWS –
views

SYNONYMS
– synonyms

The above PowerShell script displays the names of the
specified objects from the specified database from the specified server. [Refer
Fig 1.7]

Result:


UserDefined Functions
———————
ufnGetAccountingEndDate
ufnGetAccountingStartDate
ufnGetContactInformation
ufnGetDocumentStatusText
ufnGetProductDealerPrice
ufnGetProductListPrice
ufnGetProductStandardCost
ufnGetPurchaseOrderStatusText
ufnGetSalesOrderStatusText
ufnGetStock
ufnLeadingZeros
dm_db_index_operational_stats
dm_db_index_physical_stats
dm_db_missing_index_columns
dm_exec_cached_plan_dependent_objects
dm_exec_cursors
dm_exec_plan_attributes
dm_exec_query_plan
dm_exec_sql_text
dm_exec_xml_handles
dm_io_virtual_file_stats
fn_builtin_permissions
fn_cColvEntries_80
fn_check_object_signatures
fn_dblog
fn_dump_dblog
fn_EnumCurrentPrincipals
fn_fIsColTracked
fn_get_sql
fn_GetCurrentPrincipal
fn_GetRowsetIdFromRowDump
fn_helpcollations
fn_helpdatatypemap
fn_IsBitSetInBitmask
fn_isrolemember
fn_listextendedproperty
fn_MapSchemaType
fn_MSdayasnumber
fn_MSgeneration_downloadonly
fn_MSget_dynamic_filter_login
fn_MSorbitmaps
fn_MSrepl_map_resolver_clsid
fn_MStestbit
fn_MSvector_downloadonly
fn_my_permissions
fn_numberOf1InBinaryAfterLoc
fn_numberOf1InVarBinary
fn_repladjustcolumnmap
fn_repldecryptver4
fn_replformatdatetime
fn_replgetcolidfrombitmap
fn_replgetparsedddlcmd
fn_replreplacesinglequote
fn_replreplacesinglequoteplusprotectstring
fn_repluniquename
fn_RowDumpCracker
fn_servershareddrives
fn_sqlvarbasetostr
fn_trace_geteventinfo
fn_trace_getfilterinfo
fn_trace_getinfo
fn_trace_gettable
fn_translate_permissions
fn_varbintohexstr
fn_varbintohexsubstring
fn_virtualfilestats
fn_virtualservernodes
fn_yukonsecuritymodelrequired



Fig 1.7

Conclusion

Part 7 of
this article series illustrated how to use PowerShell and SMO to find all of the
objects available on a given database on a given server.

»


See All Articles by Columnist
MAK

Latest Articles