Hi guys!
Do you have a powershell script to script all SQL objects (including create index syntax, constraints, etc)?
I want to automate this process by including it into a SQL job.
The whole idea is to generate all objects into single .sql file as you do it from Generate Scripts ->...(GUI method).
Thanks!
Perhaps easiest to do this using a powershell script. This page has an example. You may need to tweak it for your needs. For example, the script on that page writes each object to its own file. Replace the line that reads $OutFile = "$objpath" + "$ObjName" + ".sql" with a single filename if you want everything to be scripted to a single file.
JamesK, thank you for responding.
Starting from the link you gave me I've managed to modify my powershell script and now is exporting all objects into single file.
Any idea how to schedule "Script Database as..." as file/job (Prod database), change the name of it (let's say Prod_Test) and also name of files inside of file/job?
Windows Scheduler? or SQL Agent perhaps
If you want one scheduled job to be able to do a specific named table/file/etc. but for that name parameter to change, from time to time, I would store it in a "Config Table" (in an Admin Database, if it needs to be system-wide / database-independent).
If you actually only want to launch this Manually, not on a timer, then it might be easier to launch the thing from a Stored Procedure, and use the SProc Parameters as the "Name Parameter"
If you want to have it run on a timer, but do one/many databases/files then I would have a Config Table, as above, but allow it to have multiple rows - one per "iteration" and the loop around those in your scheduled job.