In this recipe, we will search database objects based on a search string using PowerShell.
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.
Let's see how we can search for objects in your SQL Server database using PowerShell:
Open PowerShell ISE as administrator.
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 = "localhost" $server = New-Object -TypeName Microsoft.SqlServer...