Searching for database objects

In this recipe, we will search for database objects based on a search string by using PowerShell.

Getting ready

We will use AdventureWorks2008R2, in this exercise, and will look for SQL Server objects with the word "Product" in their names.

To get an idea of what are expecting to retrieve, run the following script in SQL Server Management Studio:

USE AdventureWorks2008R2
GO
SELECT 
 * 
FROM 
 sys.objects
WHERE 
 name LIKE '%Product%'
 -- filter table level objects only
 AND [type] NOT IN ('C', 'D', 'PK', 'F') 
ORDER BY 
 [type]

This will get you 23 results. Remember this number.

How to do it...

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Import the SQLPS module, and create a new SMO Server object:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking
   
   #replace this with your instance name
   $instanceName = "KERRIGAN"
   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList...