It may seem a little redundant, but I love stuff like this. I was asked in front of a group of devs to script out a group of SPs on the prod box and copy them over to the new test box. These SPs stretch across a couple schemas and are named differently from the other ones in those schemas. As it turns out, there are something like 300 of them total. I don’t have a final count.

So when the guy asked me I said sure, that’ll take me like 60secs. And one of the other devs said, there’s no way. You have to check all of those boxes individually and and make sure you don’t miss anything. I said, of course I can. I’m a powershell guy (yes, i actually said that). He was like, even if you could script something like that out, there’s no way to easily get all the ones you need. You’ll be much faster in the wizard.

I told him, I accept your challenge. And for the first time, I gave a dev rights in prod and we had a face-off right there. We sat side by side and both of us started working feverishly to get our SPs scripted. Him in the wizard and me in powershell. Very quickly a crowd gathered. We prob had like 15-20 people gather. These were the PMs, other devs, report writers, etc. They all wanted to see if the bigshot DBA MVP could be taken down by a lowly dev.

Unfortunately like 90secs later, I completed my script and was building my file with my scripted SPs. He was still slugging his way through the wizard and wasn’t even close to having all his little boxes checked. When I finished, I just stood up and walked out as everyone clapped. When I left to come back upstairs he was still working at the wizard determinded to at least finish.

At least that’s how my powershell hero stories always play-out in my mind. I really did get that assignment today, but it was through email and I just did it without any pomp and circumstance. Oh well, a guy can dream can’t he?

Here’s the code I wrote today to pull SPs by schema and matching a pattern. I used the regex in powershell to make this happen. Enjoy.

PS SQLSERVER:\SQL\Server1\DEFAULT\Databases\ProdDB\StoredProcedures> dir | ?{$_.schema -eq “Bay” -or $_.schema -match “EBM”} | ?{$_.Name -match “Bay?_PR”} | %{$_.Script() | out-file C:\SPs.txt -append; “GO” | out-file C:\SPs.txt -append}

Maybe someday one of you will actually save the day with it.