6

I want to automatically (ideally from the command prompt in a batch file) automate the generation of the schema of my SQL Server 2008 R2 database.

In SSMS, I can right-click the DB, choose "Tasks", "Generate scripts", and then follow the wizard to gen a Schema script. Is there a command-line version of this process that I can use?

Matt Roberts
  • 26,371
  • 31
  • 103
  • 180
  • I think SQL Server is a great piece of software, but I absolutely loathe its supporting tools for their lack of common-sense features. – Jason Kleban May 13 '11 at 19:00

2 Answers2

3

Microsoft released a new tool a few weeks ago called mssql-scripter that's the command line version of the "Generate Scripts" wizard in SSMS. It's a Python-based, open source command line tool and you can find the official announcement here. Essentially, the scripter allows you to generate a T-SQL script for your database/database object as a .sql file. You can generate the file and then execute it. This might be a nice solution for you to generate the schema of your db (schema is the default option). Here's a quick usage example to get you started:

$ 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

Tara Raj
  • 381
  • 3
  • 4
2

From this answer, there appear to be tools called SMOScript and ScriptDB that can do that.

If you find a way without third party tools please share :)

Community
  • 1
  • 1
Andomar
  • 232,371
  • 49
  • 380
  • 404