Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generate SQL Scripts from Database Expand / Collapse
Author
Message
Posted Friday, January 4, 2008 2:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 2, 2012 7:24 AM
Points: 30, Visits: 109
Comments posted to this topic are about the item Generate SQL Scripts from Database
Post #439135
Posted Monday, August 17, 2009 1:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 14, 2009 7:25 AM
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.
Post #771744
Posted Monday, August 17, 2009 7:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 2, 2012 7:24 AM
Points: 30, 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?
Post #771892
Posted Monday, August 17, 2009 10:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 14, 2009 7:25 AM
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.
Post #772419
Posted Tuesday, August 18, 2009 9:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 2, 2012 7:24 AM
Points: 30, 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.
Post #772808
Posted Tuesday, August 18, 2009 10:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 14, 2009 7:25 AM
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] (
Post #773194
Posted Wednesday, August 19, 2009 7:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 2, 2012 7:24 AM
Points: 30, 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.
Post #773458
Posted Friday, September 11, 2009 10:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 23, 2009 6:34 AM
Points: 1, Visits: 4
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
Post #786812
Posted Monday, September 14, 2009 7:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 2, 2012 7:24 AM
Points: 30, 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
Post #787425
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse