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:
Hi,

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,
Mathi.
Posted

1 solution

Hi,
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();
   
Comments
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
Microsoft.SqlServer.Smo.dll
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