CODEWITHSUNDEEP
sql-serverpowershell
fahadash
fahadash

Reputation: 3281

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?

Upvotes: 1

Views: 1087

Answers (2)

dugas
dugas

Reputation: 12473

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 }

Upvotes: 2

Mike Shepard
Mike Shepard

Reputation: 18176

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.

Upvotes: 2

Related Questions