Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers

up vote 5 down vote accepted

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()
share|improve this answer
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. –  Shawn Melton Sep 28 '12 at 16:54
    
I think this is pretty workable. I'll probably do a powershell script then. –  usr Sep 28 '12 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 '12 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. –  Shawn Melton Sep 28 '12 at 22:27
add comment

I wrote a command line utility named schemazen that does this. It's much faster than scripting from management studio and it's free and open source.

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
share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
    
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 '12 at 17:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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