I want to be able to script the schema of a given database into a .sql file from the Windows command prompt. Basically, I want to execute the "Generate Scripts" feature of Management Studio programmatically.

I know this is possible using .NET and SMO, but is there something built-in to do this?

Background to this question: This is meant as a simple auditing instrument. We want to capture the schema every night. Any primitive, low-tech solution works for us.

4 Answers 4


There's nothing built-in from the command line.

If you have Red Gate SQL Compare you can do it:

sqlcompare /s1:MySQLInstance /db1:MyDB /mkscr:MyDB_Schema /q

SSMS scripting functions are just wrappers for SMO. I know you mention it, but you could write a powershell script to use SMO.

This is adapted from code found on this Simple Talk post.


$serverName = "MYSQLINSTANCE"
$databaseName = "MyDB"
$sqlServer = new-object("Microsoft.SqlServer.Management.Smo.Server") $serverName
$sqlDb = $sqlServer.Databases[$databasename]

$options = new-object ("Microsoft.SqlServer.Management.Smo.ScriptingOptions")
$options.ExtendedProperties = $true
$options.DRIAll = $true
$options.Indexes = $true
$options.Triggers = $true
$options.ScriptBatchTerminator = $true
$options.Filename = "c:\\script_folder\\mydb_schema.sql"
$options.IncludeHeaders = $true
$options.ToFileOnly = $true

$transfer = new-object ("Microsoft.SqlServer.Management.Smo.Transfer") $sqlDb
$transfer.options = $options
  • 1
    Actually if you are using SQLPS, or the SQL server snapin it does have the Script() method. Which I guess is border line "built in". Check: midnightdba.com/Jen/2010/05/script-sql-objects-with-powershell it has a ton of the videos Sean and Jen McCown did on using PowerShell for this type of thing.
    – user507
    Sep 28, 2012 at 16:54
  • I think this is pretty workable. I'll probably do a powershell script then.
    – usr
    Sep 28, 2012 at 17:10
  • @Shawn that's a good point. I'm still pretty green with the PS snap-ins. I'll have to play with that. I'm pretty used to SMO from the C# side of things so my brain navigates that direction still.
    – squillman
    Sep 28, 2012 at 17:28
  • SQLPS is convenient for when you don't have access to your script library. I will admit I only have a handful of functions/scripts that actually depend on it. SQLPS is convient when you just don't really want to open SSMS :) However, most scripts I try to write for multiple versions of SQL Server so SMO has the advantage in that instance.
    – user507
    Sep 28, 2012 at 22:27

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

To just feed off of squillman's answer this is to show a sample of what SQLPS can do for you...You can browse each "directory" under the database and just do a get-member -MemberType Method, looking for Script(). Most of the directories have it I believe.

Add-PSSnapin *SQL*
# Note my hostname of the server is "SQLSERVER"
# To show object names to be scripted
dir SQLSERVER:\SQL\SQLSERVER\DEFAULT\Databases\JProCo\Tables | Select Name
$objects = dir SQLSERVER:\SQL\SQLSERVER\DEFAULT\Databases\JProCo\Tables
$objects | % {$_.Script()}

Look from real server: Screenshot from my test server


You can use the SQL Server Database Publishing Wizard.

If you don't have it installed then you can grab version 1.2 from here

The installer doesn't give any indication that it has installed but if you open up a command window and navigate to:

C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2 the SqlPubWiz.exe should be there.

You can use this to script out your schema.

Type SqlPubWiz help script in your command window and it will give you the options you need.

  • Thanks! I'll look into this. It does seem from the help command that a lot of scripting options are not available though. I hope this captures all the important stuff.
    – usr
    Sep 28, 2012 at 17:11

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.