Script Contents of SQL Server Database From a Batch File

Question

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.

Answer 1

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.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

$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
$transfer.ScriptTransfer()

Answer 2

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

Answer 3

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:

Answer 4

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.