CODEWITHSUNDEEP
sql-serversql-server-2008
Jason
Jason

Reputation: 1325

Any way to get the T-SQL generated by SSMS Script Wizard?

I'm using SQL Server 2008.

I'm trying to script out all my stored procedures, and it's very easy to do in SSMS by right clicking my database, then going to Tasks -> Generate Scripts. I set my options and all is good.

I would like to get the actual T-SQL that the script wizard is executing so that I don't have to go through and select all of my options every single time I want to do it. I want to just open the script and hit run. Is there a way to copy the script that the wizard itself is executing? Or do I just have to do it manually every time?

Upvotes: 1

Views: 187

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

If all you're after is "script all procedures," and it's a one-time thing, you can open Object Explorer Details in Management Studio, highlight Stored Procedures in Object Explorer, then Ctrl + A, right-click, script as > ...

If you want a slightly more automated way, there are several schema comparison products on the market so that you don't have to care about the script that Management Studio uses to generate the script for a single object. As a bonus, it will be much easier to synchronize other, more complicated objects - just try to generate the script for a table yourself, and you will see it is no picnic. I go over many options in this blog post.

Upvotes: 0

JNK
JNK

Reputation: 65147

You can do this with a pretty simple powershell script using the SMO Framework. You will need to have SQL Server Management Studio installed for the framework to get picked up. You should look into this further, but the basic framework will be:

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

$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" 'MyServer'

$db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
$db = $srv.Databases['MyDatabase']

$scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$scr.Server = $srv

$scr.options.filename = 'C:\SomeFolder\MyExports.SQL'

$db.StoredProcedures | where-object {$_.IsSystemObject -eq $False} | %{$scr.Script($_)}

You may need to alter some additional options. MSDN has a pretty thorough overview of the framework here.

Essentially the above will script out all the stored procs in a database to whatever file you specify. SMO is the framework that SSMS uses so it should be identical.

Upvotes: 1

Related Questions