Generate SQL Scripts from Database

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

  • 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.

  • 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?

  • 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.

  • 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.

  • 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] (

  • 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.

  • 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

  • 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

  • Thanks for the script.

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

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