CODEWITHSUNDEEP
sql-server
yolololo
yolololo

Reputation:

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

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.

Upvotes: 4

Views: 2243

Answers (5)

Tara Raj
Tara Raj

Reputation: 391

Microsoft released a new tool last week called mssql-scripter. The tool is a Python-based, open source command line tool and you can find the official announcement here. Essentially, the scripter allows you to generate T-SQL scripts (DDL and DML) for your database/database object as a .sql file. It's the command line version of the 'Generate Scripts' wizard in SSMS. Here's a quick usage example to get you started (schema only is the default):

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

More usage examples are on our GitHub page here: https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/usage_guide.md

Upvotes: 1

onupdatecascade
onupdatecascade

Reputation: 3366

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.

Upvotes: 0

devio
devio

Reputation: 37205

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.

Upvotes: 0

tbreffni
tbreffni

Reputation: 5130

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);
}

Upvotes: 0

Raj
Raj

Reputation: 10843

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

Upvotes: 0

Related Questions