The following was tested on:
- Win 7.0, .NET 4.0, VS 2010, SQL Server 2008R2
- Win 7.0, .NET 4.6.1, VS 2017, SQL Server 2014
Required assembly references:
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Management.Sdk.Sfc
- Microsoft.SqlServer.Smo
I created a simple Win forms app with OK button and called below function.
Note: If scripting out views you need to add
if( myView.IsSystemObject == true ) continue;
to ensure that system views are not being scripted; I have not tested this. For old versions of SQL Server you may also need this check for tables as well.
public static string ScriptDatabase( string dbConnectionString, string databaseName )
{
SqlConnection conn = new SqlConnection( dbConnectionString );
ServerConnection serverConn = new ServerConnection( conn );
var server = new Server( serverConn );
var database = server.Databases[ databaseName ];
var scripter = new Scripter( server );
scripter.Options.IncludeIfNotExists = true;
scripter.Options.ScriptSchema = true;
scripter.Options.ScriptData = true;
string scrs = "";
foreach( Table myTable in database.Tables )
{
foreach( string s in scripter.EnumScript( new Urn[] { myTable.Urn } ) )
scrs += s + "
"; ;
}
return ( scrs );
}
Function would be called as follows:
// Connection string for local SQL Server default instance
ScriptDatabase( "Server=.;Database=PlayGround;Trusted_Connection=True;", "PlayGround" );
Output:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tBlah]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tBlah](
[ID] [int] IDENTITY(1,1) NOT NULL,
[tID] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[Value] [varchar](20) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
END
SET IDENTITY_INSERT [dbo].[tBlah] ON
INSERT [dbo].[tBlah] ([ID], [tID], [Value]) VALUES (1, N'2', N'1234')
INSERT [dbo].[tBlah] ([ID], [tID], [Value]) VALUES (2, N'2', N'345.6')
MSDN References:
Updated 17-Dec-2019: Check with latest .NET version; Added required References; Clean-up example code; Added sample conn string
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…