Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generate SQL Scripts from Database


Generate SQL Scripts from Database

Author
Message
Absinthe-428914
Absinthe-428914
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 109
Comments posted to this topic are about the item Generate SQL Scripts from Database
sudhir.adsul
sudhir.adsul
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 11
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
Absinthe-428914
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 109
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
sudhir.adsul
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 11
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
Absinthe-428914
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 109
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
sudhir.adsul
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 11
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
Absinthe-428914
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 109
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
kiran.kumar7
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
Hi,
I'm not able to generate the scripts for my database using the posted script Sad
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
Absinthe-428914
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 109
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
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10628 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search