Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: Script database using stored procedure

    Is it possible to use a stored procedure to script an entire database in MS SQL 2000?

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Re: Script database using stored procedure

    You can use the CREATE DATABASE statement in a stored proc.

    What you can't do is run the following:

    CREATE VIEW , CREATE PROC, CREATE RULE, CREATE DEFAULT , CREATE RULE

    You can script the entire database from enterprise manager which would be sufficient for the any purpose i can think of.

    Stored Procs should be used for heavily repeated processes not one off's.

    Hope this helps ....

  3. #3
    Join Date
    Jan 2004
    Posts
    49
    Yes it is possible, but is bad idea... maybe better SQLDMO?

    sp_helptext
    Prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.
    Syntax
    sp_helptext [ @objname = ] 'name'

    sp_help
    Reports information about a database object (any object listed in the sysobjects table), a user-defined data type, or a data type supplied by Microsoft® SQL Server™.

    Syntax
    sp_help [ [ @objname = ] name ]


    http://www.apexsql.com/

  4. #4
    Join Date
    Oct 2003
    Posts
    7

    Re: Script database using stored procedure

    well not exactly, let me explain.

    What I want to do is call a procedure that exports the entire database design (tables views etc) to a sql-script file.

    Pretty much the same as the server is able to do in the enterprise manager. The only difference is, that I want to be able to call the procedure from an application.

    Clearer?

    Rolf

  5. #5
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Re: Script database using stored procedure

    Hi Rolf,

    What you might want to do is use DTS to copy the objects across to whatever server you wish

    have a look at DTS - Copy SQL Server Objects

    You can set this to copy the objects but not the data

    If you need to call this from an application then you can use the cmd line DTSRun utility which will allow you to run it from a cmd prompt.

    Hope this helps ......

  6. #6
    Join Date
    Oct 2003
    Posts
    7

    Re: Script database using stored procedure

    OK thanks,

    I know of this possibility but I'd prefer to avoid it.

    thanks anyway.

    R.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I swear that at one time I found a command-line utility that I could call from xp_cmdshell to output a database script identical to Enterprise Manager's scripting results. But now I can't find it.

    I'll keep looking, but in the meantime check out the SQL-DMO SCRIPT method in Books Online.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Oct 2003
    Posts
    7
    Now we're getting somewhere. I'll try this first and will return if it doesn't work.

    Thanks.

    R.

  9. #9
    Join Date
    Jan 2004
    Posts
    1
    There is sample VB app from Micosoft on how to use the SQL-DMO Script methods referenced in SQL Server Books Online. If interested, look in the Books Online index for "SQL-DMO", then "samples", then "SQLScripts".

    I found the source on my machine in a file named GenSQLScriptsDemo.zip.

Posting Permissions

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