Script database using stored procedure

Is it possible to use a stored procedure to script an entire database in MS SQL 2000?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 ...|||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/|||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|||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 .....|||OK thanks,

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

thanks anyway.

R.|||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.|||Now we're getting somewhere. I'll try this first and will return if it doesn't work.

Thanks.

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