Create a sql script for db / sprocs / etc. through the command line

Question

Is it possible to generate sql scripts for my SQL Server 2005/8 database through the command line?

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

Answer 1

Previously, there was the utility SCPTXFR.EXE, which was part of the standard installation of SQL Server 7.0 and 2000. The initial goal was as part of the upgrade process, which allowed you to migrate SQL Server 6.5 databases to SQL Server 7.0 and 2000. However, it is also very useful in everyday environments. as it allows you to programmatically generate scheduled scripts of database objects.

, SQL 2005/2008. .

Answer 2

- . #, Sql. , ( ):

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

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

Answer 3

, SMOscript.

SCPTXFR ( ), SQL 2005 2008. Script SMO.

Answer 4

Powershell script + Microsoft.SqlServer.Management.Smo.Scripter? . http://blogs.msdn.com/buckwoody/archive/2009/07/02/powershell-and-sql-server-script-all-tables.aspx , , . .

Answer 5

Microsoft , mssql-scripter. - Python, . , T-SQL (DDL DML) / .sql. SSMS. , ( ):

$ pip install mssql-scripter
# script the database schema and data piped to a file.
$ mssql-scripter -S localhost -d AdventureWorks -U sa  > ./adventureworks.sql

GitHub: https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/usage_guide.md