Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2002
    Location
    Buffalo, NY
    Posts
    9

    Question Unanswered: Dynamically create SQL Scripts

    I would like to be able to create SQL Scripts with a stored procedure (or some other means). I have looked all through the help files and even tried watching the profiler to see how EM does it. Any help would be appreciated.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I've written a set of procedures that will generate INSERT/SELECT/UPDATE statements for a given table, plus more. Is that what you are looking for?

  3. #3
    Join Date
    Feb 2002
    Location
    Buffalo, NY
    Posts
    9
    Thanks for your reply.

    I went back and read my post and I'm sorry, I should have been more clear. I am looking for a way to dynamically generate the scripts to create the objects themselves (Stored Procedures, Tables, Logins, etc.). I would like to have a job that would fire every week and write out the individual scripts for each object into a directory named the same as the database. Basically, I want to automate the "Generate SQL Scripts" menu option in EM.

  4. #4
    Join Date
    Feb 2002
    Location
    S.E. London
    Posts
    5
    Don't know an easy way to do this, I don't think you can do it using DTS
    at least not pre-2000.

    The hard way is to write an SP which generates all the DDL using
    sysobjects, syscolumns, systypes, sysindexes, syslogins, sp_helptext etc.

    You can use sp_help code as a preliminary guide on how to extract the DDL.

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You can use SQLDMO to generate the script. What version of SQL Server are you using, I may have a VB program that does this and a Perl program.

  6. #6
    Join Date
    Feb 2002
    Location
    Buffalo, NY
    Posts
    9
    Thanks for the reply plus any help given, I am using 2000.

  7. #7
    Join Date
    Feb 2002
    Location
    Buffalo, NY
    Posts
    9

    achorozy, help please...

    achorozy, you mentioned I could use DMO (something I have zero experience with) or you might have something I can use. Can you help me?

    Thanks

  8. #8
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I've attached a VB project that was written using SQLDMO to generate SQL script for a given database.

    This script was originally for 6.5 but was converted to 7.0 and 2000 by changing the SQLDMO libray from SQLOLE to SQLDMO. I believe this code original came from an example on DEVX a few years ago. This is not my code but I did modify it to work with 7.0 and 2000.

  9. #9
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Attachment
    Attached Files Attached Files

  10. #10
    Join Date
    Feb 2002
    Location
    Buffalo, NY
    Posts
    9
    Thanks!!

    That worked great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •