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!
+1
A:
Hey DonnMt,
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! :)
Santos
2010-03-11 00:23:50
Hey, thanks for the reply! Forgive the noobish question, but what would be the best way to automate the call to the command line? Batch file? Thanks.
DonnMt
2010-03-11 00:31:02
Also, is yourSQLServerInstance my database name? Thanks!
DonnMt
2010-03-11 00:36:49
No, the SQL Server Instance is "optional", and is only required if you have installed SQL Server twice or more on the same machine. When doing so, you must give a name to the second and other instances you install. So, if SQL Server is installed only once, just forget the "\yourSQLServerInstance" part
MaxiWheat
2010-03-11 03:16:51
You'll want to add a `-d yourDatabaseName` parameter to the command line to select a particular database, otherwise the default database associated with the login will be used (normally this is `master`).
devstuff
2010-03-11 04:29:13
And where ever possible, use a trusted connection (`-E` parameter) instead of the username/password combination. This lessens the chance of someone knowing the password if someone else reads the batch file.
devstuff
2010-03-11 04:32:07
+2
A:
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
MaxiWheat
2010-03-11 03:25:17
+1, I have yet to see a tsql approach to script out a complete runnable table script, which includes all possible table definition items: PK, FK, index, defaults, check constraint, computed columns, etc..
KM
2010-03-11 13:19:48
A:
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.
blorkfish
2010-07-21 05:35:11