I am running SQL Server 2012.

The SQL Server Management Studio has the option to right click on a database then select Tasks and Generate Scripts.

Is there a way to automate that via command line somehow?

I want to create a script that includes the schema and data of the entire data base.

Tools like ScriptDB and sqlpubwiz.exe all seem to target SQL Server 2005. What about SQL Server 2012?

5 Answers 5


Best is to use Powershell - if you are going to use it frequently. You can refer to Automated Script-generation with Powershell and SMO.

Also, SQL Server PowerShell Extensions (SQLPSX) are of great value when working with Powershell. All the modules are having help files e.g. Get-SqlScripter.

Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlTable | Get-SqlScripter | Set-Content -Path D:\scripts\script.sql
Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlStoredProcedure | Get-SqlScripter
Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlView | Get-SqlScripter

For third party tools, highly recommend to check out (there are many third party tools out there, but below ones I have used and they are great):

  • 1
    SQLPSX had no commits since Feb 2018 ...
    – Macke
    Mar 16, 2020 at 10:19
  • you are commenting on an answer written in 2014. things have drastically changed. check dbatools as it covers majority of use cases that you will need.
    – Kin Shah
    Mar 16, 2020 at 13:44
  • 1
    Indeed they have. It was quite at the top of my google search though, so I thought I'd help out others that arrive by the same means.
    – Macke
    Mar 16, 2020 at 13:58
  • he is commenting to help keep the answer relevant and updated Dec 12, 2022 at 14:26

Tara Raj from Microsoft recently announced that the Microsoft SQL team has released a set of command line tools to generate T-SQL scripts that appear to do exactly what you've asked:


Mssql-scripter is the multiplatform command line equivalent of the widely used Generate Scripts Wizard experience in SSMS.

You can use mssql-scripter on Linux, macOS, and Windows to generate data definition language (DDL) and data manipulation language (DML) T-SQL scripts for database objects in SQL Server running anywhere, Azure SQL Database, and Azure SQL Data Warehouse. You can save the generated T-SQL script to a .sql file or pipe it to standard *nix utilities (for example, sed, awk, grep) for further transformations. You can edit the generated script or check it into source control and subsequently execute the script in your existing SQL database deployment processes and DevOps pipelines with standard multiplatform SQL command line tools such as sqlcmd.

Mssql-scripter is built using Python and incorporates the usability principles of the new Azure CLI 2.0 tools. The source code can be found on Github at https://github.com/Microsoft/sql-xplat-cli, and we welcome your contributions and pull requests!

Some examples of use:

Generate DDL scripts for all database objects (default) in the Adventureworks database and output to stdout

$ mssql-scripter -S localhost -d AdventureWorks -U sa

Generate DDL scripts for all database objects and DML scripts (INSERT statements) for all tables in the Adventureworks database and save the script to a file

$ mssql-scripter -S localhost -d AdventureWorks -U sa –schema-and-data  > ./output.sql
  • 3
    Sounds very nice and "official-ish", but no commits since 2018 gives me bad feeling.. ?
    – Macke
    Mar 16, 2020 at 10:19

I wrote an open source command line utility named SchemaZen that does this. It's much faster than scripting from management studio and it's output is more version control friendly. It supports scripting both schema and data.

To generate scripts run:

schemazen.exe script --server localhost --database db --scriptDir c:\somedir

Then to recreate the database from scripts run:

schemazen.exe create --server localhost --database db --scriptDir c:\somedir

Just an update: in current versions of SQL Server powershell modules (SQL Server 2014 and on, I believed. Tested on SSMS 17), most of these options are native commands and methods.

For instance, you can use Get-SqlDatabase and methods such as .Script() and .EnumScript(). It's really useful and simple, especially if you want a more granular approach (specific tables and other objects).

For example, this will generate CREATE scripts for user defined functions and save it to file:

$Database = Get-SqlDatabase -ServerInstance $YourSqlServer -Name $YourDatabaseName

$MyFuncs = $Database.UserDefinedFunctions | Where Schema -eq "dbo"
$MyFuncs.Script() | Out-File -FilePath ".\SqlScripts\MyFunctions.sql"

If you want to script data and elements like indexes, keys, triggers, etc. you will have to specify scripting options, like this:

$scriptOptions = New-Object -TypeName Microsoft.SqlServer.Management.Smo.ScriptingOptions

$scriptOptions.NoCollation = $True
$scriptOptions.Indexes = $True
$scriptOptions.Triggers = $True
$scriptOptions.DriAll = $True
$scriptOptions.ScriptData = $True

$Database.Tables.EnumScript($scriptOptions) | Out-File -FilePath ".\AllMyTables.sql"

Note that the Script() method doesn't support scripting data. Use EnumScript() for tables.

A single table:

($Database.Tables | Where Name -eq "MyTableName").EnumScript($scriptOptions)

All your views, save one file per each view, DROP and CREATE script:

ForEach ($view in $($Database.Views | Where Schema -eq "dbo")) {

    "`nIF OBJECT_ID('$($view.Name)') IS NOT NULL DROP VIEW $($view.Name);`n`n" | Out-File -FilePath ".\SqlScripts\$($view.Name).sql"    
    $view.Script() | Out-File -FilePath ".\SqlScripts\$($view.Name).sql" -Append

Hope this helps.

  • Awesome. Correct soution for 2020! However, the order is not the same as from SSMS and I get some dependency issues when exporting with EnumScripts. Do you have any ideas for that?
    – Macke
    Mar 16, 2020 at 12:26
  • NVM. WithDependencies seem the way to go.
    – Macke
    Mar 16, 2020 at 12:32
  • The WithDependencies works for one table, not all, since it re-exports the dependent-upon tables for each export, so one should use the Scripter object's dependency resolving functions instead, as done here: patlau.blogspot.com/2012/09/…
    – Macke
    Mar 16, 2020 at 13:57

You can use the command line versions of xSQL Schema Compare and xSQL Data Compare. With the command line versions you can schedule periodic data and schema comparisons against an empty database and this always have the scripts to build an exact replica of the latest version of your database from scratch.

Disclosure: I'm affiliated to xSQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.