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

i have bunch of stored procedures in database, launched powershell sql server right clicking on stored procedures folder in ssms , choosing start powershell, tried following command 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 remove | select-string mykeyword part, spits out code every stored procedure 1 one. missing?

your command returning array of pscustomobjects have string property named "text". need pipe value of property select-string. right calling select-string on object not string. change to:

get-childitem |  foreach-object { (invoke-sqlcmd -suppressprovidercontextwarning -query  ("sp_helptext '$_'" )) } | select-object text | select-string yourkeyword 

to answer later question outputting sp name, store name in variable , output it, 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 }