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!
3 Answers
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! :)
5 Comments
DonnMt
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
Also, is yourSQLServerInstance my database name? Thanks!
MaxiWheat
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
devstuff
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
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.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
1 Comment
KM.
+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..
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.
