Sql-server – Script Contents of SQL Server Database From a Batch File

command linescriptingsmosql server

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.

Best Answer

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()
Related Question