Stored procedure to script database objects to fil

Question

I'd like to write a stored procedure in SQL 2005 to script all my database objects to a file. The only problem is...I have no idea how to do it. Can anyone help?

Thanks!

Answer 1

I know your question says that you want a Stored Procedure to do the job done, but if you want an other solutions, I would be using SMO in VB.NET. When accessing database objects with the .NET objects, they all got a "Script()" function which returns the SQL to use to recreate the object. A table for instance.

Hope this might help

Answer 2

you can try something like this,

First of all creating a new store procedure in your SQL server, like this.

CREATE PROCEDURE ListObjectsToFile
AS
BEGIN
 select * from sysobjects;
END
GO

And then call it from the Command Line, indicating your complete connection string and the path/name of the output file

C:\>sqlcmd -S yourServer\yourSQLServerInstance -U yourUser -P yourUserPassword -q ListObjectsToFile -o C:\list.txt

Hope that helps,

Santi! :)

Answer 3

Try DBSourceTools. (http://dbsourcetools.codeplex.com)
Its open source, and specifically designed to script an entire database
- tables, views, procs and data to disk, and then re-create that database through a deployment target.