How do I generate DB script at runtime?

Question

How do I generate DB script at runtime?

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

You might be able to if your modifications are in some sort of source control application. Are they?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Brian-538429 SSC Veteran Points: 244 More actions · Answer 2

You could create a copy of an existing database that has the structure you want to put your subset of data into and then use sp_msForEachTable to delete all rows in all tables in that copied database. The way I do things is to have a base .bak file for an old version of our products and a host of .sql files to update from version to version. Every night MSBuild restores the .bak and then applies all .sql files under source control to it. These sql files both update the structure and add static data. I find this is a good way to test upgrade scripts on a daily basis rather than running into problems when a client wants to upgrade. If you really need to generate a structure script dynamically at run time you're going to have to use the functions in the Microsoft.SqlServer.Management.Smo namespace in some .net language as there is no T-SQL solution (that I'm aware of anyway) to producing the script. The smo stuff is very easy to use however, and is very powerful.