Generating sql code programmatically

Question

i have generated scripts manually through Generate script in tasks menu by right clicking database.

Now my problem is to generate that script through c# code...

My question is

1. is it possible to generate through c# code?

2. give me some tips in order to complete?

Waiting for your valuable suggestions and commands.

Solution 1

As it's already mentioned, you cas use SMO to do this, here is a an example using C# to script a database, I mentioned few options, but as it is in the post of @David Brabant, you can specify the values of many options.

public string ScriptDatabase()
{
      var sb = new StringBuilder();

      var server = new Server(@"ServerName");
      var databse = server.Databases["DatabaseName"];

      var scripter = new Scripter(server);
      scripter.Options.ScriptDrops = false;
      scripter.Options.WithDependencies = true;
      scripter.Options.IncludeHeaders = true;
      //And so on ....


      var smoObjects = new Urn[1];
      foreach (Table t in databse.Tables)
      {
          smoObjects[0] = t.Urn;
          if (t.IsSystemObject == false)
          {
              StringCollection sc = scripter.Script(smoObjects);

              foreach (var st in sc)
              {
                  sb.Append(st);
              }
           }
       }
            return sb.ToString();
 }

This link may help you getting and scripting stored procedures

Solution 2

You can use sql smo for basically implementing all functionality available in SQL Server Enterprise manager. There is a nice tutorial here.

Edit: an example using SMO in PowerShell

function SQL-Script-Database
{
    <#
    .SYNOPSIS
    Script all database objects for the given database.

    .DESCRIPTION
    This  function scripts all database objects  (i.e.: tables,  views, stored
    procedures,  and user defined functions) for the specified database on the
    the given server\instance. It creates a subdirectory per object type under 
    the path specified.

    .PARAMETER savePath
    The root path where to save object definitions.

    .PARAMETER database
    The database to script (default = $global:DatabaseName)

    .PARAMETER DatabaseServer 
    The database server to be used (default: $global:DatabaseServer).

    .PARAMETER InstanceName 
    The instance name to be used (default: $global:InstanceName).

    .EXAMPLE
    SQL-Script-Database c:\temp AOIDB
    #>

    param (
        [parameter(Mandatory = $true)][string] $savePath,
        [parameter(Mandatory = $false)][string] $database = $global:DatabaseName,
        [parameter(Mandatory = $false)][string] $DatabaseServer = $global:DatabaseServer,
        [parameter(Mandatory = $false)][string] $InstanceName = $global:InstanceName
    )

    try
    {
        if (!$DatabaseServer -or !$InstanceName)
            { throw "`$DatabaseServer or `$InstanceName variable is not properly initialized" }

        $ServerInstance = SQL-Get-Server-Instance $DatabaseServer $InstanceName

        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

        $s = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
        $db = $s.databases[$database]

        $objects = $db.Tables
        $objects += $db.Views
        $objects += $db.StoredProcedures
        $objects += $db.UserDefinedFunctions

        $scripter = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

        $scripter.Options.AnsiFile = $true
        $scripter.Options.IncludeHeaders = $false
        $scripter.Options.ScriptOwner = $false
        $scripter.Options.AppendToFile = $false
        $scripter.Options.AllowSystemobjects = $false
        $scripter.Options.ScriptDrops = $false
        $scripter.Options.WithDependencies = $false
        $scripter.Options.SchemaQualify = $false
        $scripter.Options.SchemaQualifyForeignKeysReferences = $false
        $scripter.Options.ScriptBatchTerminator = $false

        $scripter.Options.Indexes = $true
        $scripter.Options.ClusteredIndexes = $true
        $scripter.Options.NonClusteredIndexes = $true
        $scripter.Options.NoCollation = $true

        $scripter.Options.DriAll = $true
        $scripter.Options.DriIncludeSystemNames = $false

        $scripter.Options.ToFileOnly = $true
        $scripter.Options.Permissions = $true

        foreach ($o in $objects | where {!($_.IsSystemObject)}) 
        {
            $typeFolder=$o.GetType().Name 

            if (!(Test-Path -Path "$savepath\$typeFolder")) 
                { New-Item -Type Directory -name "$typeFolder"-path "$savePath" | Out-Null }

            $file = $o -replace "\[|\]"
            $file = $file.Replace("dbo.", "")

            $scripter.Options.FileName = "$savePath\$typeFolder\$file.sql"
            $scripter.Script($o)
        }
    }

    catch
    {
        Util-Log-Error "`t`t$($MyInvocation.InvocationName): $_"
    }
}

Solution 3

Based on @Sami Answer

I have create this Simple Function that will generate all the scripts for your Database( Tables, Views , stored procedures,Users and UserDefinedFunctions)

First: Get Required Assemblies

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies is the correct folder location (or C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies on 64-bit systems).

You need to add references to:

Microsoft.SqlServer.ConnectionInfo.dll

Microsoft.SqlServer.Smo.dll

Microsoft.SqlServer.Management.Sdk.Sfc.dll

Microsoft.SqlServer.SqlEnum.dll

Second: use this Function

  public static string ScriptDatabase() 
{
    // For Me Server is ".\SQLExpress" You may have changed
    Server myServer = new Server(@".\SQLExpress");
    Scripter scripter = new Scripter(myServer);

    //Databas1 is your database Name Thats Changable

    Database myAdventureWorks = myServer.Databases["MyDBName"];
    /* With ScriptingOptions you can specify different scripting  
    * options, for example to include IF NOT EXISTS, DROP  
    * statements, output location etc*/
    ScriptingOptions scriptOptions = new ScriptingOptions();
    scriptOptions.ScriptDrops = true;
   // scriptOptions.ScriptData = true;
    scriptOptions.ScriptSchema = true;


    scriptOptions.IncludeIfNotExists = true;
    string scrs = "";
    string tbScr = "";
    foreach (Table myTable in myAdventureWorks.Tables)
    {
        /* Generating IF EXISTS and DROP command for tables */
        StringCollection tableScripts = myTable.Script(scriptOptions);
        foreach (string script in tableScripts)
            scrs += script + "\n\n";

        /* Generating CREATE TABLE command */
        tableScripts = myTable.Script();
        foreach (string script in tableScripts)
            tbScr += script + "\n\n";
    }


    foreach (StoredProcedure mySP in myAdventureWorks.StoredProcedures)
    {
        /* Generating IF EXISTS and DROP command for StoredProcedures */
        StringCollection tableScripts = mySP.Script(scriptOptions);
        foreach (string script in tableScripts)
            scrs += script + "\n\n";

        /* Generating CREATE StoredProcedure command */
        tableScripts = mySP.Script(scriptOptions);
        foreach (string script in tableScripts)
            tbScr += script + "\n\n";
    }

    foreach (View myView in myAdventureWorks.Views)
    {
        /* Generating IF EXISTS and DROP command for Views */
        StringCollection tableScripts = myView.Script(scriptOptions);
        foreach (string script in tableScripts)
            scrs += script + "\n\n";

        /* Generating CREATE Views command */
        tableScripts = myView.Script(scriptOptions);
        foreach (string script in tableScripts)
            tbScr += script+"\n\n";
    }


    foreach (Microsoft.SqlServer.Management.Smo.User user in myAdventureWorks.Users)
    {
        /* Generating IF EXISTS and DROP command for Users */
        StringCollection tableScripts = user.Script(scriptOptions);
        foreach (string script in tableScripts)
            scrs += script+"\n\n";

        /* Generating CREATE Users command */
        tableScripts = user.Script(scriptOptions);
        foreach (string script in tableScripts)
            scrs += script + "\n\n";
    }



    foreach (Microsoft.SqlServer.Management.Smo.UserDefinedFunction userF in myAdventureWorks.UserDefinedFunctions)
    {
        /* Generating IF EXISTS and DROP command for UserDefinedFunctions */
        StringCollection tableScripts = userF.Script(scriptOptions);
        foreach (string script in tableScripts)
            scrs += script + "\n\n";

        /* Generating CREATE UserDefinedFunction command */
        tableScripts = userF.Script(scriptOptions);
        foreach (string script in tableScripts)
            scrs += script + "\n\n";
    } 

    // For WinForms
    return (scrs + "\n\n" + tbScr);
    //For Console
    //Console.WriteLine(scrs + "\n\n" + tbScr);
}

Solution 4

I've just been using the above answers to write a command line program for doing this in c# and thought I'd expand a bit on the answers above.

if you want to output data as well the schema you need to use:

scripter.EnumScript(something);
//instead of 
scripter.Script(something);

The script method just checks for the IncludeData option and throws an exception if it is set, but you have to get on google to find out what the right method to use is! Interesting API design!

The relevant lists in the database are as follows:

        database.Tables
        database.Schemas
        database.Views
        database.StoredProcedures
        database.UserDefinedFunctions
        database.Users
        database.Roles
        database.Sequences

although that might not be exhaustive.

Getting rid of system objects

The lists in these objects are all custom types which are IEnumerable but not IEnumerable<T> so you can't do linq on them. This also means you have to to find out what type is in them and use foreach's implicit casts to get them out. I'd never used that in c# before but I guess this stuff is probably targeting framework 2.

A lot of them also have properties called IsSystemObject but this is not implementing an interface. At first it looks like it'll be a real pain to get rid of all the system objects but you can cull them all in one fell swoop by setting the following option:

options.AllowSystemObjects = false;

This works for everything except for Roles for those you have to do the system ones by hand:

        foreach (DatabaseRole role in database.Roles)
        {
            if(role.IsFixedRole)
                continue;

            list.Add(role);
        }

Adding objects for output

The process I used was to create an UrnCollection and then add the different list to the collection. Like this:

        var list = new UrnCollection();

        foreach (Schema schema in database.Schemas)
            list.Add(schema.Urn);
        //... more of these

        scripter.EnumScript(list);

Options

From there you need to figure out what options to set to recreate the output you need. A few things to bear in mind:

• Indexes, Triggers, Constraints etc are set by options and not treated as first class objects.
• In the UI in SSMS you can't produce sequences at all so expect at least some diffs in your output if you are using these

See this post for more information on how to get foreign keys etc out.

Health warning

I started looking at this as a way to copy a database as I thought backup and restore wouldn't do what I wanted. After going quite a long way down the smo path and running into a lot of problems I had a bit of a re-evaluation and found backup and restore is a lot simpler for that use case.

Solution 5

Hopefully a it would guide you and upcoming ones.

You have to add following four references to your project to include following required namespaces

To add a references

1. Right click your project in solution explorer and choose add reference
2. Click Browse from upper menu
3. And choose 4 dll files as instructed below

Reference Microsoft.SqlServer.Smo.dll

namespaces

using System.Data.SqlClient;
using System.Collections.Specialized;
using Microsoft.SqlServer.Management.Smo;

Now use following code in any function or button click event

        // For Me Server is ".\SQLExpress" You may have changed
        Server myServer = new Server(@".\SQLExpress");
        Scripter scripter = new Scripter(myServer);

        //Databas1 is your database Name Thats Changable

        Database myAdventureWorks = myServer.Databases["Database1"];
        /* With ScriptingOptions you can specify different scripting  
        * options, for example to include IF NOT EXISTS, DROP  
        * statements, output location etc*/
        ScriptingOptions scriptOptions = new ScriptingOptions();
        scriptOptions.ScriptDrops = true;
        scriptOptions.IncludeIfNotExists = true;
        string scrs = "";
        string tbScr = "";
        foreach (Table myTable in myAdventureWorks.Tables)
        {
            /* Generating IF EXISTS and DROP command for tables */
            StringCollection tableScripts = myTable.Script(scriptOptions);
            foreach (string script in tableScripts)
                scrs += script;

            /* Generating CREATE TABLE command */
            tableScripts = myTable.Script();
            foreach (string script in tableScripts)
                tbScr += script;
        }
        // For WinForms
        MessageBox.Show(scrs + "\n\n" + tbScr);
        //For Console
        //Console.WriteLine(scrs + "\n\n" + tbScr);

It involved http://www.mssqltips.com/sqlservertip/1833/generate-scripts-for-database-objects-with-smo-for-sql-server/ Answer (above) by David Brabant and the SO link above

Code Block 2 is used. Now you can use others as well

i could not find myserver there but it is resolved as well in above code.

View more solutions

Comments

[GowthamanSS]

@david the link which u gave creates table,sp etc and delete it through code but my query is to take complete queries used in particular database through c#.net? is it possible

[David Brabant]

Yes, it is. And is is relatively simple. See my edited post for an example in PowerShell. Translating it to C# should be easy.

[GowthamanSS]

@DavidBrabant ya thanks David but i do not know powershell how can i translate it

[David Brabant]

I updated the example link to point to a C# example: mssqltips.com/sqlservertip/1833/…

[GowthamanSS]

while running ur solution i am getting error as "An exception occurred while executing a Transact-SQL statement or batch."

[GowthamanSS]

i have given server name and database name only and run ur code i am using sql 2008 how about u?

[GowthamanSS]

ya sry i changed database name it well executed now i need to get all the scripts what ever used in that database through your code is it possible

[SidAhmed]

For that you need to set the scripter options, my code does not contain the required options, I suggest you take a look on the options in @David Brabant post, they have the same names

[SidAhmed]

It may help you : take a look at the options when you do "Generate script" in Sql server management studio.

[GowthamanSS]

i have changed ur code as foreach (StoredProcedure sp in databse.StoredProcedures) { smoObjects[0] = sp.Urn; if (sp.IsSystemObject == false) { StringCollection sc = scripter.Script(smoObjects); foreach (var st in sc) { sb.Append(st); } } } but that condition is always true did i want to always change anything

[SidAhmed]

Are you sure you have stored procedures in your DB ? because the if statement is for excluding the system objects

[SidAhmed]

I'v edited my post : Added a link, it may help you with the stored procedures

[Sami]

@GowthamanSS have a look. Try it and tell if any problem. Howevere I have tested and it has worked for me.

[JonnyRaa]

I found the following useful stackoverflow.com/questions/6453415/…

[Shai]

Which option allow me to export data from a table? I tried scriptOptions.ScriptData = true; But could not figure out how to put in a loop to export data