How to search for a string in all stored procedures through SQL Powershell?

I have a bunch of stored procedures in my database, I launched the PowerShell for SQL Server by right clicking on the Stored Procedures folder in SSMS and choosing Start Powershell, I tried the following command but it gives me nothing

PS SQLSERVER:\SQL\MYCOMP\DEFAULT\Databases\MYDB\StoredProcedures> Get-ChildItem |  ForEach-Object { (Invoke-SQLCMD -SuppressProviderContextWarning -Query  ("sp_helptext '$_'" )) | Select-String mykeyword}

If I remove the | Select-String mykeyword part, it spits out the code for every stored procedure one by one. What am I missing?

Answers


Your command is returning an array of PSCustomObjects that have a string property named "Text". You need to pipe the value of that property to select-string. Right now you are calling select-string on an object that is not a string. Change to:

Get-ChildItem |  ForEach-Object { (Invoke-SQLCMD -SuppressProviderContextWarning -Query  ("sp_helptext '$_'" )) } | Select-Object Text | Select-String yourKeyword

To answer your later question about outputting the sp name, you could store the name in a variable and output it, something like:

$sName = '';
Get-ChildItem |  ForEach-Object { $sName = $_.Name; (Invoke-SQLCMD -SuppressProviderContextWarning -Query  ("sp_helptext '$_'" )) } | Select-Object Text | Select-String yourKeyword | ForEach-Object { Write-Host $sName }

I see that there's already an answer, but this was slightly more useful to me:

Get-ChildItem |  ForEach-Object { if(($line=Invoke-SQLCMD -SuppressProviderContextWarning -Query  ("sp_helptext '$_'" ) | Where Text -like  '*Title*'| select -expand Text)) {$_;$line}}

I'm outputting the stored procedure objects as well as the matching lines (interspersed). It would probably be worthwhile to make this into a function and package the output as objects but this is a good start.


Need Your Help

Can't run bundle update on Windows

ruby-on-rails ruby git gem bundle

Whenever I run bundle update or bundle install on Windows 8.1 I can't update/install gems from github. I can install other gems like uglifier, but it doesn't work for github gems specifically. For

using repeated macros in c++

c++ c macros

assume i have a macro like this