Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 5, 2007

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7

By Muthusamy Anantha Kumar aka The MAK

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]::
 LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" 
 "HOME\SQLEXPRESS" 
$db = $srv.Databases["adventureworks"] 
foreach ($tbl in $db.tables) {$tbl.name}

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM