Microsoft Windows Powershell and SQL Server 2005 SMO - Part 7

Use this guide to understand Microsoft Windows powershell and SQL server 2005 smo - part 7 and the factors that affect it. The information is organized to make the topic easier to apply in practice.

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

How Microsoft Windows Powershell and SQL Server 2005 SMO - Part 7 Works

The MAK

Part 1 and Part 2 of this series introduced PowerShell and simple SMO, WMI cmdlet installation.In Part 3, I will show you how to create a PowerShell script to connect to a SQL Server. Part 4 introduces how to use a PowerShell script to perform a file loop and connect other servers. Part 5 shows you how to create a SQL Server database using PowerShell and SMO. Part 6 introduces backing up SQL Server databases with PowerShell and SMO. Each part of this series demonstrates how to use PowerShell in conjunction with SMO to present SQL Server objects. Method 1: Display the table names Let's assume we want to find all the tables already in the ' AdventureWorks ' database, on the server ' HOMESQLEXPRESS '. Execute the following command, refer to Figure 1.1.

[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. name}

Figure 1.1

The above Cmdlets will display the table names in the AdventureWorks database on the server ' HOMESQLEXPRESS ' (see Figure 1.2). Result

AWBuildVersionDatabaseLogErrorLogDepartmentEmployeeEmployeeAddressEmployeeDepartmentHistoryEmployeePayHistoryJobCandidateShiftAddressAddressTypeContactContactTypeCountryRegionStateProvinceBillOfMaterialsCultureDocumentIllustrationLocationProductProductCategoryProductCostHistoryProductDescriptionProductDocumentProductInventoryProductListPriceHistoryProductModelProductModelIllustrationProductModelProductDescriptionCultureProductPhotoProductProductPhotoProductReviewProductSubcategoryScrapReasonTransactionHistoryTransactionHistoryArchiveUnitMeasureWorkOrderWorkOrderRoutingProductVendorPurchaseOrderDetailPurchaseOrderHeaderShipMethodVendorVendorAddressVendorContactContactCreditCardCountryRegionCurrencyCreditCardCurrencyCurrencyRateCustomerCustomerAddressIndividualSalesOrderDetailSalesOrderHeaderSalesOrderHeaderSalesReasonSalesPersonSalesPersonQuotaHistorySalesReasonSalesTaxRateSalesTerritorySalesTerritoryHistoryShoppingCartItemSpecialOfferSpecialOfferProductStoreStoreContact

Figure 1.2

Method 2 Suppose that you want to find all the objects that are already in the ' AdventureWorks ' database, on the server ' HOMESQLEXPRESS '. Execute the following command, refer to 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"Echo "------"Foreach ($ tbl in $ db. Tables) {$ tbl. name}Echo "Synonyms"Echo "------"Foreach ($ Synonyms in $ db. Synonyms) {$ Synonyms. name}"Stored Procedures" echoEcho "------"Foreach ($ StoredProcedures in $ db. StoredProcedures) {$ StoredProcedures. name}Echo "Assemblies"Echo "------"Foreach ($ Assemblies in $ db. Assemblies) {$ Assemblies. name}Echo "User Defined 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}

Figure 1.3

The above Cmdlets will display the object names in the AdventureWorks database on the server ' HOMESQLEXPRESS ' (see Figure 1.4). Result

Figure 1.4

Method 3 Please connect method 1 and method 2 to a PowerShell script form to accept the following parameters. Create the Listobjects. ps1 File as shown below.

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"){"Stored Procedures" echoEcho "------------------"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 "User Defined Functions"Echo "---------------------"Foreach ($ UserDefinedFunctions in $ db. UserDefinedFunctions){$ UserDefinedFunctions. name}}If ($ ObjectType -eq "VIEWS")Foreach ($ Views in $ db. Views) {$ Views. name}}If ($ ObjectType -eq "XP"){Echo "ExtendedStoredProcedures"Echo "------------------------"Foreach ($ ExtendedStoredProcedures in $ db. ExtendedStoredProcedures){$ ExtendedStoredProcedures. name}}

Figure 1.5

Now execute the Listobjects. ps1 File as shown below (see Figure 1.6).

./listobjects "HOMESQLEXPRESS" "AdventureWorks" "UDF"

Figure 1.6

Explain the parameters

Listobjects Is the script file listobjects. ps1 in the c: ps directory. HOME Is the configuration name SQLEXPRESS Is the SQL server name on the master configuration named HOME AdventureWorks Is the database name residing in SQLEXPRESS. UDF Is a parameter, which is used to display all user defined functions in 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 PowerShell script above shows the names of the objects of a particular database in the server. (see Figure 1.7) Result

UserDefined Functions---------------------UfnGetAccountingEndDateUfnGetAccountingStartDateUfnGetContactInformationUfnGetDocumentStatusTextUfnGetProductDealerPriceUfnGetProductListPriceUfnGetProductStandardCostUfnGetPurchaseOrderStatusTextUfnGetSalesOrderStatusTextUfnGetStockUfnLeadingZerosDm_db_index_operational_statsDm_db_index_physical_statsDm_db_missing_index_columnsDm_exec_cached_plan_dependent_objectsDm_exec_cursorsDm_exec_plan_attributesDm_exec_query_planDm_exec_sql_textDm_exec_xml_handlesDm_io_virtual_file_statsFn_builtin_permissionsFn_cColvEntries_80Fn_check_object_signaturesFn_dblogFn_dump_dblogFn_EnumCurrentPrincipalsFn_fIsColTrackedFn_get_sqlFn_GetCurrentPrincipalFn_GetRowsetIdFromRowDumpFn_helpcollationsFn_helpdatatypemapFn_IsBitSetInBitmaskFn_isrolememberFn_listextendedpropertyFn_MapSchemaTypeFn_MSdayasnumberFn_MSgeneration_downloadonlyFn_MSget_dynamic_filter_loginFn_MSorbitmapsFn_MSrepl_map_resolver_clsidFn_MStestbitFn_MSvector_downloadonlyFn_my_permissionsFn_numberOf1InBinaryAfterLocFn_numberOf1InVarBinaryFn_repladjustcolumnmapFn_repldecryptver4Fn_replformatdatetimeFn_replgetcolidfrombitmapFn_replgetparsedddlcmdFn_replreplacesinglequoteFn_replreplacesinglequoteplusprotectstringFn_repluniquenameFn_RowDumpCrackerFn_servershareddrivesFn_sqlvarbasetostrFn_trace_geteventinfoFn_trace_getfilterinfoFn_trace_getinfoFn_trace_gettableFn_translate_permissionsFn_varbintohexstrFn_varbintohexsubstringFn_virtualfilestatsFn_virtualservernodesFn_yukonsecuritymodelrequired

Figure 1.7

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

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8 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