Searching for database objects

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

Getting ready

We will use the AdventureWorks2014 database in this recipe and look for SQL Server objects with the word Product in it.

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

USE AdventureWorks2014
GO
SELECT
  *
FROM
  sys.objects
WHERE
  name LIKE '%Product%'
   -- check only for table, view, function
   -- or stored procedure
  AND [type] IN ('U', 'FN', 'P', 'V')
ORDER BY
  [type]

This will get you 23 results. Remember this number.

How to do it...

Let's see how we can search for objects in your SQL Server database using PowerShell:

1. Open PowerShell ISE as administrator.

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 = "localhost"
   $server = New-Object -TypeName Microsoft.SqlServer...