Click here to Skip to main content
14,926,103 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I need to generate Table scripts and Stored procedure scripts dynamically from either C# or through stored procedure. Is it possible ?

For Example, We use to write stored proc to generate insert scripts given in the below link
Generating INSERT statements in SQL Server[^]. I want to do the same for tables scripts and stored procs scripts.

Please let me know any solution available for this.

Thanks & Regards,

1 solution

It's easy to do this with SMO, see Transfer; class for scripting operations and Database class for database operations (create, drop, etc). Usage looks like this:

private StringCollection GetTransferScript(Database database)
       var transfer = new Transfer(database);

       transfer.CopyAllObjects = true;
       transfer.CopyAllSynonyms = true;
       transfer.CopyData = false;

       // additional options
       transfer.Options.WithDependencies = true;
       transfer.Options.DriAll = true;
       transfer.Options.Triggers = true;
       transfer.Options.Indexes = true;
       transfer.Options.SchemaQualifyForeignKeysReferences = true;
       transfer.Options.ExtendedProperties = true;
       transfer.Options.IncludeDatabaseRoleMemberships = true;
       transfer.Options.Permissions = true;
       transfer.PreserveDbo = true;

       // generates script
       return transfer.ScriptTransfer();
Mathi2code 14-Oct-13 5:16am
I'm trying to add the namespaces but I'm unable to find it anywhere. Please let me know how to find this
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
[no name] 15-Oct-13 3:33am
Microsoft.SqlServer.Management.Common namespace resides in the Microsoft.SqlServer.ConnectionInfo.dll and Microsoft.SqlServer.Management.Smo reside in
you can download it form .net and add refreence to your project.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900