Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By: Donabel Santos

Overview of this book

Table of Contents (21 chapters)
SQL Server 2014 with PowerShell v5 Cookbook
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Index

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...