Generate sql script of db/sprocs/etc. via command line

Question

Hi,

Is it possible to generate sql scripts for my sql server 2005/8 database via the command line?

I want to automate the build process and part of it requires me to get the entire schema (tables/sprocs/etc) in a file.

Answer 1

There used to be an utility called SCPTXFR.EXE which was part of the standard SQL Server 7.0 and 2000 install.Its original purpose was as part of the upgrade process that allowed SQL Server 6.5 databases to be migrated to SQL Server 7.0 and 2000. However it is also very useful in a day-to-day environment, as it allows you to programmatically generate scheduled scripts of database objects.

Not sure if this will work for SQL 2005/2008. Check this document for full details of this utility.

Raj

Answer 2

I had to do something similar in a past project. I wrote a simple C# console application using Sql Server Management Objects. You can write code as simple as this (may need some tweaking):

Server myServer = new Server("name");
Database db = myServer.Databases["name"];

foreach (StoredProcedure sp in db.StoredProcedures)
{
File.WriteAllText(sp.Name, sp.TextBody);
}

Answer 3

I wrote a utility for this task, SMOscript.

It's based on the principle of SCPTXFR (which Raj mentioned), and also supports the new object types in SQL 2005 and 2008. Script generation is performed by the SMO library.

Answer 4

Powershell script + object Microsoft.SqlServer.Management.Smo.Scripter? See http://blogs.msdn.com/buckwoody/archive/2009/07/02/powershell-and-sql-server-script-all-tables.aspx for an example showing how to get started w/tables. You'd have to extend the idea to other objects.