A common question in the sqlserver.programming and sqlserver.server newsgroups is “where is the option to generate one file for each object when scripting an object from SQL Server Management Studio (SSMS)?”. Unfortunately the option is not available in SSMS even though it was available in Enterprise Manager in SQL Server 2000. I believe that the option to generate one file for each object will be re-added in SQL Server 2005 Service Pack 2.
In the meantime the VB.Net code snippet below illustrates how SQL Server Management Objects (SMO) can be used to script all tables in the Northwind database so that the script for each table is in an individual file named after the object:
Add Reference to Microsoft.SQLServer.ConnectionInfo
Add Reference to Microsoft.SQLServer.SMO
Imports System.IO
Imports Microsoft.SqlServer.Management.Smo
…
Dim SMOServer As Server = New Server(“BNEMOM”)
SMOServer.SetDefaultInitFields(GetType(Table), “IsSystemObject”)
Dim so As ScriptingOptions = New ScriptingOptions
so.Default = True
For Each tbl As Table In SMOServer.Databases(“northwind”).Tables
If Not tbl.IsSystemObject Then
Dim sw As StreamWriter = New StreamWriter(“c:\” & tbl.Name & “.sql”)
For Each s As String In tbl.Script(so)
sw.WriteLine(s)
Next
sw.Close()
End If
Next
