In this recipe, we will search for database objects based on a search string by using PowerShell.
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.
Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.
Import the
SQLPSmodule, 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...