Generate SQL server scripts from command line?

问题:

In Microsoft SQL Server Management Studio 2008 there is a "Generate scripts..." option under the Tasks menu. I'm just wondering if this tool is available from the command line somehow?

It looks similar to the sqlpubwiz.exe command line tool that was available for SQL Server 2005, but I can't find this executable anywhere in the SQL 2008 installation.

回答1:

The script generating options of Management Studio are just a wrapper around the SMO scripting capabilities. Simple scripts are obtained by using the Script() method on various SMO types. More complex scripts are created by the specialized Scripter class.

Command line tools can be easily created by using the SMO libraries.

回答2:

The SQLPubWiz.Exe for SQL2008 R2 on my PC is located on "C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.4"

回答3:

You can use mssql-scripter, a command line tool that generates T-SQL scripts for databases and database objects (works on Windows, macOS, and Linux).

The download for mssql-scripter is via pip as it is Python-based: pip install mssql-scripter

You'll run a command similar to the following to script the database schema and data to a file: $ mssql-scripter -S localhost -d AdventureWorks -U sa --schema-and-data > ./adventureworks.sql More usage examples: https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/usage_guide.md

This project can be found at this GitHub repo - https://github.com/Microsoft/sql-xplat-cli

回答4:

SQLPUBWIZ SCRIPT -d yourDBName -S yourServerName -U userName -P userPassword scriptFilewithPath

Download for SQL2005 http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A