Generate SQL Script...

Question

Post reply

Generate SQL Script...

dusang

SSC Enthusiast

Points: 164
More actions
March 21, 2003 at 11:35 am

#80492

Hi,
what I need is to Generate SQL Script. I know it is easy using Enterprise Manager -> All Tasks -> Generate SQL Scripts. I do backup of my source code (including database scripts) daily and it is time expensive to generate db scripts using Enterprise Manager.
I started to create script my own script (e.g. to get store procedure text: SELECT [text] FROM sysobjects so, syscomments sc WHERE (so.[id] = sc.[id]) AND (type = 'P')), but I think there should be easier way around to achieve this, e.g using system stored procedures.
I'd need to script out:
- tables
- primary/foreign keys
- indexes
- constraints
- stored procedures
- triggers
- user defined functions
Thanks.
dusan

Viewing 15 posts - 1 through 15 (of 18 total)

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

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

LOL.

I was going to ask the same question.

I to wanted to do something like this.

I watched profiler while scripting and it does a huge amount of work.

Busy sifting through it. Will let you know if I have any luck.

Find a way, let us know.

Cheers,

Crispin

Why don't you try practicing random acts of intelligence and senseless acts of self-control?

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

tcartwright SSCertifiable Points: 5573 More actions · Answer 2

I have a com object that I wrote that encapsulates DMO that does this very thing that I can email to whomever wants it. Also Bill Wunder has a DDL archival utility. http://www.sqlservercentral.com/products/bwunder/archiveutility/

My objects scripting signature :



HRESULT ScriptDb(

    [in] BSTR DatabaseName, 

    [in] BSTR ScriptDirectory, 

    [in, out, optional, defaultvalue(0)] TssScriptXfrObjectsType* ScriptXfrObjectsType, 

    [in, out, optional, defaultvalue(4)] TssSqldmoScriptType* SqldmoScriptType, 

    [in, out, optional, defaultvalue(1)] TssScriptXfrModeType* ScriptXfrModeType, 

    [out, retval] VARIANT* );

The enums it exposes for thsi function :

enum {

    TssXfrDefault = 0,

    TssXfrTables = 1,

    TssXfrViews = 2,

    TssXfrProcedures = 4,

    TssXfrFunctions = 8,

    TssXfrRules = 16,

    TssXfrTriggers = 32,

    TssXfrUserDefinedDatatypes = 64,

    TssXfrDropDestObjectsFirst = 128,

    TssXfrIncludeDependencies = 256,

    TssXfrDefaults = 512,

    TssXfrUsers = 1024,

    TssXfrLogins = 4096,

    TssXfrXportStaticData = 8192,

    TssXfrXportALLData = 73736

} TssScriptXfrObjectsType;



enum {

    TssSQLDMOScript_None = 0,

    TssSQLDMOScript_Default = 4,

    TssSQLDMOScript_Drops = 1,

    TssSQLDMOScript_ObjectPermissions = 2,

    TssSQLDMOScript_PrimaryObject = 4,

    TssSQLDMOScript_ClusteredIndexes = 8,

    TssSQLDMOScript_Triggers = 16,

    TssSQLDMOScript_DatabasePermissions = 32,

    TssSQLDMOScript_Permissions = 34,

    TssSQLDMOScript_ToFileOnly = 64,

    TssSQLDMOScript_Bindings = 128,

    TssSQLDMOScript_AppendToFile = 256,

    TssSQLDMOScript_NoDRI = 512,

    TssSQLDMOScript_UDDTsToBaseType = 1024,

    TssSQLDMOScript_IncludeIfNotExists = 4096,

    TssSQLDMOScript_NonClusteredIndexes = 8192,

    TssSQLDMOScript_Indexes = 73736,

    TssSQLDMOScript_Aliases = 16384,

    TssSQLDMOScript_NoCommandTerm = 32768,

    TssSQLDMOScript_DRIIndexes = 65536,

    TssSQLDMOScript_IncludeHeaders = 131072,

    TssSQLDMOScript_OwnerQualify = 262144,

    TssSQLDMOScript_TimestampToBinary = 524288,

    TssSQLDMOScript_SortedData = 1048576,

    TssSQLDMOScript_SortedDataReorg = 2097152,

    TssSQLDMOScript_TransferDefault = 422143,

    TssSQLDMOScript_DRI_NonClustered = 4194304,

    TssSQLDMOScript_DRI_Clustered = 8388608,

    TssSQLDMOScript_DRI_Checks = 16777216,

    TssSQLDMOScript_DRI_Defaults = 33554432,

    TssSQLDMOScript_DRI_UniqueKeys = 67108864,

    TssSQLDMOScript_DRI_ForeignKeys = 134217728,

    TssSQLDMOScript_DRI_PrimaryKey = 268435456,

    TssSQLDMOScript_DRI_AllKeys = 469762048,

    TssSQLDMOScript_DRI_AllConstraints = 520093696,

    TssSQLDMOScript_DRI_All = 532676608,

    TssSQLDMOScript_DRIWithNoCheck = 536870912,

    TssSQLDMOScript_NoIdentity = 1073741824,

    TssSQLDMOScript_UseQuotedIdentifiers = -2147483648

} TssSqldmoScriptType;



enum {

    TssSQLDMOXfrFile_Default = 1,

    TssSQLDMOXfrFile_SummaryFiles = 1,

    TssSQLDMOXfrFile_SingleFile = 2,

    TssSQLDMOXfrFile_SingleFilePerObject = 4,

    TssSQLDMOXfrFile_SingleSummaryFile = 8

} TssScriptXfrModeType;

Tim C.

//Will write code for food

Tim C //Will code for food

jmadren SSCrazy Points: 2055 More actions · Answer 3

At what level are you generating the scripts within EM? If you're doing it for each individual object, then yes that can take a while. But you can do it from the database level and it will do all objects at once.

If you are already doing it this way and it's just a performance issue, I don't think using custom scripts will do much better. There are some scripts in the library that do some of what you're wanting, but they don't support every feature of every object.

There are also some commercial products out there that do a lot of this. Some of them advertise on this web site.

Jay Madren

tcartwright SSCertifiable Points: 5573 More actions · Answer 4

My function lets you script all objects OR all objects of a specific type. Example :

object.ScriptDb "DB", "D:\SQL", , , TssSQLDMOXfrFile_SingleFile

generates all the objects with the default options in a single file.

object.ScriptDb "DB", "D:\SQL", TssXfrFunctions + TssXfrProcedures + TssXfrViews, , TssSQLDMOXfrFile_SingleFilePerObject

will script functions, procs, and views with a file per object in the d:\sql directory. The enums are bitmasks so combinations of the options can be sent in.

Tim C.

//Will write code for food

Tim C //Will code for food

Crispin Proctor SSCoach Points: 18161 More actions · Answer 5

You're just trying to show off...

I have not used DMO, can it in someway be added to a job? DTS/ActiveX Script?

CP

Why don't you try practicing random acts of intelligence and senseless acts of self-control?

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

tcartwright SSCertifiable Points: 5573 More actions · Answer 6

quote:
You're just trying to show off...

LOL, no, I actually like to share my knowledge with others. I would most likely even provide the source for my dll if asked nicely. A six pack of "Fat Tire" would do nicely. Besides often times answering others questions help me to grow in knowledge as I do not / can not think of all the questions, and sometimes others quetions make me go hmmmmmmm, How would I do that?

quote:
I have not used DMO, can it in someway be added to a job? DTS/ActiveX Script?

Yes, you can use it from script, so you could use DTS or a scheduled job to run it. Andy Warren has several nice aricles on DMO on this site. Take a look at : http://www.sqlservercentral.com/columnists/awarren/allarticles.asp

I use my DMO wrapper plus a VSS automation wrapper that I wrote to do what Bill Wunder did in his. Wrote mine before I found his btw, else I probably would have used his. I have them scheduled to script and back up my DB's nightly to VSS. I use a VBScript to execute my objects. I also added an extended property to tables I considered static, so that I make a BCP dump of those tables(using the same object with a defferent function). The VSS folder is then labeled so that for any point in time I can retrieve the script for a specific object and my static data as I have found that static data can evolve over time, and having backups of it is extremely useful.

Tim C.

//Will write code for food

Tim C //Will code for food

Crispin Proctor SSCoach Points: 18161 More actions · Answer 7

quote:
A six pack of "Fat Tire" would do nicely

I take it that is come kind of beer. All I can offer is a pack of Windhoek (The best beer out!!!!)

Trying to solve other peoples problems, I find, is the best way to broaden you knowledge of something. A nice challenge...

CP

Why don't you try practicing random acts of intelligence and senseless acts of self-control?

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

dusang SSC Enthusiast Points: 164 More actions · Answer 8

Truly, performance is not the issue. As I mentioned above I backup souce code daily. For this purpose I created archive.bat file.

e.g.

winrar a myProj.rar F:\Projects\myProj\*

I need to create script (generateSqlScript.sql) that will be called by osql utility from within archive.bat file.

"c:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe" /U sa /P pass /d myDB /n /i generateSqlScript.sql /o osql.txt

So my goal is "One Click Backup".

dusan

quote:
At what level are you generating the scripts within EM? If you're doing it for each individual object, then yes that can take a while. But you can do it from the database level and it will do all objects at once.
If you are already doing it this way and it's just a performance issue, I don't think using custom scripts will do much better. There are some scripts in the library that do some of what you're wanting, but they don't support every feature of every object.
There are also some commercial products out there that do a lot of this. Some of them advertise on this web site.
Jay Madren

cneuhold Hall of Fame Points: 3375 More actions · Answer 9

take a look at

http://www.sqlservercentral.com/scripts/contributions/246.asp

for all table related stuff. enhancing that to print out 'create table ...' etc. should not be a big deal.

Ken Klaft SSC Veteran Points: 225 More actions · Answer 10

I tried using VB6's DMO functions and it worked pretty good. I wanted a command-line object scripter and it performed OK. You might want to give that a try.

I actually wrote an object scripter in T-SQL a while back. Beside satisfying my curiosity, it was a -heck- of a challenge. If you have the time, I'd suggest it. You'll walk away with a much better understanding of the system tables.

Cheers,

Ken

PS Be prepared to bang out some code. I never truly appreciated DMO functions until I tried to "roll my own"... <grin>

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 11

5409045121009-7368

SSCoach

Points: 15740

March 24, 2003 at 1:54 pm

#451964

Anyone using scptxfr.exe?

tuxedodba SSC Journeyman Points: 92 More actions · Answer 12

I want some FAT TIRE!!! LOL! Where do I get some? I'll share!

-TuxedoDBA

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 13

Scptxfr.exe is a MS SQL supplied utility.

Look on this website in Products->Freeware for more.

Lowell SSC Guru Points: 323518 More actions · Answer 14

I just contributed a script that does this; everyone always points at tools to do it, but I felt it was a good challenge to try and write a SQL that does it;

look at my script and tell me what you think please;

http://www.sqlservercentral.com/scripts/contributions/730.asp

lowell@stormrage.com

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!