Yesterday a friend asked to me if PowerShell could help him to script all Stored Procedures from All Databases in one of his servers. For some reason, the SSMS was taking too long to finish. In fact, until now, he still can not do it by GUI.
Another point is by the policies from his company , he needs to create a process to perform this operation every week.
I told him that we can do it with a command line. He thought I was lying and probably you too, so lets do it.
The first step was download the SQLPX (SQL Server PowerShell Extensions) and install in his desktop and be loaded in the profile. Then it is just type :
1 – To all stored procedures from one Database :
2 – To all stored procedures from ALL databases. It will create a .sql file with all stored procedures for each database.Ha make no mistake, this is a command line.
3 – If you want the “if not exists”, lets use the scripting options. Ok now I confess, it is not a command line….humm… three.
Now it is just create a SQL Server Agent Job and run this script.
Well, I can say that when he started the process and finished some minutes after, the only word I heard was. “HOLY *&¨%$%¨%$#$#%…..” ..well you can imagine the rest