How to auto generate database object dreate scripts in SQL 2005?

Question

How to auto generate database object dreate scripts in SQL 2005?

sharon.smith

SSC Journeyman

Points: 96
More actions
November 25, 2008 at 6:08 am

#201755

I used to use 6.5 a long time ago then I had a career break until recently; although many things have improved in SQL there is one thing that hasn't.
I used to be able to schedule procedures to produce files of scripts which generated tables and procedures etc.
Although I can still do the same thing from the Management Console manually I don't seem to be able to schedule this sort of thing.
I used the 'upgrade' tool in SQL 2000 but this doesn't work in 2005.
Does anybody have an idea please? I don't know how to use SMO/DMO so I'm stuck.
Thanks in advance

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

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

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 1

I've never thought about automating it before. I took a quick look at what happens in Profiler when I script a database. It must be an SMO call from Management Studio because only a few RPC's come through, not enough to get all the tables, procedures, etc., so I think you'd have to use SMO.

The good news is, you could install PowerShell and use the SMO calls through that. It's very simple to do.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sharon.smith SSC Journeyman Points: 96 More actions · Answer 2

Hi Grant,

Thanks for the reply. I had tried using the profiler myself - with no result - I should have said that, sorry.

I think you're right. I'm going to have to look at SMO. Thanks for the powershell recommendation too.

I script the files and put them into subversion to keep track of changes to table changes and procedures just in case. Being command line I can automate the whole job to run every night.

Regards,

Sharon

mvm219 Grasshopper Points: 14 More actions · Answer 3

Sharon,

Were you able to build a script to perform the activities ? I'm also looking for an on-the-fly script which will browse thru the sys tables to give me the list of USER defined DB objects and then give me the CREATE scripts for all of them.

~M