Generate SQL Scripts from Database

Question

Post reply

Generate SQL Scripts from Database

Absinthe-428914

Mr or Mrs. 500

Points: 514
More actions
January 4, 2008 at 2:41 pm

#185153

Comments posted to this topic are about the item Generate SQL Scripts from Database

Viewing 10 posts - 1 through 9 (of 9 total)

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

sudhir.adsul SSC Enthusiast Points: 113 More actions · Answer 1

Hi,

I have generated scripts of all SQL objects(table,veiw,UDF, stored procedure) using SQLDMO.SQLServer2 system object.

Definition of Triggers are included in script file of table. How can this be avoided and create separate files for triggers.

With Regards

Sudhir.

Absinthe-428914 Mr or Mrs. 500 Points: 514 More actions · Answer 2

Are you saying that you ran the posted script and got this behavior, or are you saying that you are trying to do the same thing some other way and got the described behavior?

sudhir.adsul SSC Enthusiast Points: 113 More actions · Answer 3

I haven't used the posted scirpt, I tried it doing some other way.

But I am sure even if I use the posted script at the start of file date and time will be stamped.

Absinthe-428914 Mr or Mrs. 500 Points: 514 More actions · Answer 4

Give it a try and see what happens. I am sure it makes separate files for triggers. As for date and time stamps, I am not certain what you are asking.

sudhir.adsul SSC Enthusiast Points: 113 More actions · Answer 5

Please note below text marked in bold . As you can see script creation date and time are added in script file. Can we avoid this.

/****** Object: Table [dbo].[AbandonReason] Script Date: 5/21/2009 1:51:20 PM ******/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[table1]

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

BEGIN

CREATE TABLE [dbo].[table1] (

Absinthe-428914 Mr or Mrs. 500 Points: 514 More actions · Answer 6

I don't mean to sound like I am getting short with you but I am going to ask this again:

Have you run the attached script?

When you use these objects there is a setting called ScriptType that I took the time to setup which does not generate the "heading comment" In order to actually include it you would have to modify what is in there by 'OR 131072'

If the attached script doesn't do what you want, let me know and I will help you modify it. However, if you are starting from scratch, then read the documentation on SQLDMO and the Transfer and Transfer2 objects and you will have everything you need. More specifically look at ScriptType and ScriptType2 attributes and you will be able to tweak whatever you like.

kiran.kumar7 SSC Rookie Points: 31 More actions · Answer 7

Hi,

I'm not able to generate the scripts for my database using the posted script 🙁

I need to login to my database using one user id and password.

The database name contains a "\" it is like "D-XXXXX\SS_INST1".

Could some one please let me know the steps to generate the script.

Please help me in this regard as i need to generate the scripts for my database at least once in two days and if the posted script works it reduces a lot my manual effort in generating those scripts.

Thanks..Kiran

Absinthe-428914 Mr or Mrs. 500 Points: 514 More actions · Answer 8

If you look at the script you will find some lines that look like:

sServer = "(local)"

sDatabase= "pubs"

That means that as a sample it makes the assumption that the sever is installed local as opposed to using a named instance like yours. It also means that it is using the default database called pubs.

If you wish to use a named instance, you have to change the line that says sServer = "(local)" to something like sServer = "D-XXXXX\SS_INST1"

That would work fine if in fact you were able to get in using windows security instead of having to use a username and password such as 'sa' .

If you want to use a username and password you will need to change the lines (in several places) that look like:

with oSS

.LoginSecure = True

.Connect sServer

end with

To look like:

with oSS

.LoginSecure = False

.Connect sServer

.Password "YourPassword"

.Login = "YourLoginname"

end with

Where "YourPassword" is the password you are wanting to use and "YourLoginName" is the name you want to use such as "sa" or whatever. Of course you could certainly place variables into that such as:

with oSS

.LoginSecure = False

.Connect sServer

.Password sPassword

.Login = sLoginName

end with

Then change main to include them:

sServer = "(local)"

sDatabase= "pubs"

sPassword= "YourPassword"

sLoginName= "YourLoginName"

Then change the procedure calls to include them:

ScriptDefaultsUsersRolesAndLogins sServer, sDatabase, sPassword, sLoginname

ScriptEverythingElse sServer, sDatabase, sPassword, sLoginname

ScriptViews sServer, sDatabase, sPassword, sLoginname

And their corresponding procedure definitions:

Sub ScriptEverythingElse(sServer, sDatabase, sPassword, sLoginname)

and so forth...

I hope this helps.

-- Bradley

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

akljfhnlaflkj

SSC Guru

Points: 76202

May 11, 2016 at 11:43 am

#1878341

Thanks for the script.