Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7September 5, 2007 Part 1 and Part 2 of this series discussed Power Shell 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 discussed backing up a SQL Server database using PowerShell and SMO. This installment of the series illustrates how to use PowerShell in conjunction with SMO to display SQL Server Objects. Method 1: Display all the Table names Let us assume that we want to find all of the tables available on the “AdventureWorks” database, on the “HOME\SQLEXPRESS” server. Execute the following command as shown below. [Refer Fig 1.1] [reflection.assembly]::
Click for larger image 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
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}
The above cmdlets display the object names from the “AdventureWorks” database, on the “HOME\SQLEXPRESS” server. [Refer Fig 1.4] Results
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}
}
Now execute the listobjects.ps1 as shown below. [Refer Fig 1.6] ./listobjects "HOME\SQLEXPRESS" "AdventureWorks" "UDF"
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
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. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server"
