Schedule creation of a stored procedure

Question

I have a Test database which is overwritten each week by a fresh new production copy. But we have changes in our Test environment which I script in manually each Monday morning after the copy is created. Is there a way to schedule script code to run which can generate my objects and data changes eg new stored procedures etc.

The Job scheduler in SQL Server can import a SQL script, but it's not dynamic I need something that I can use in future where it will read in the script each time before it's run and pick up any changes.

Answer 1

I suggest you create a SSIS package and use SMO inside a script component to generate DDL.

This link may help you a little bit.

Using SMO is very easy and straight forward

SMO tutorial