Can we schedule a job to generate scripts for objects?

Question

Can we schedule a job to generate scripts for objects?

SQL ORACLE

One Orange Chip

Points: 27807
More actions
September 4, 2009 at 12:01 pm

#135303

It is quite painful in MS SQL 2005 to generate scripts for all objects. Does anyone know how to schedule it?
Any input will be greatly appreciated.

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Well, for code objects, you could select from sys.sql_modules. That'll get you procs, views and functions. For tables, you'd need to select from sys.columns, and a variety of other objects for constraints, indexes, column data types, and so on. Would take a while to build, test, debug, etc., but would be interesting to do.

I'm not sure why you'd need a job to output generate scripts. Don't you have the scripts in source control? Or are people issuing rogue DDL commands into your database without going through source control of some sort?

- 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

Lowell SSC Guru Points: 323518 More actions · Answer 2

there's some script contributions here on SSC that call SMO; I've done this all via TSQL, where i stick a current "snapshot" of a database in a versioning table in my master database; did you want to script this to a file, or just stick it in a table?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

I would like to get the object-level permissions. It is very time-consuming. I would like to make a schedule to run it.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 4

If you have an old copy of SQL Server 2000 laying around and it doesn't share an instance of 2005 (which deletes the program), make a copy of the SCPTXFER.exe program that came with SQL Server 2000. SCPTXFER /? will tell you how it works and it will script your entire database with a single command... object permissions included if I recall correctly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)