Subversion Repositories sql.sqlscripter

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using System.Data;
using System.IO;
//using Microsoft.SqlServer.Management.Nmo;
using System.Reflection;

using System.Collections.Specialized;
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Common;
//last one needed for SQL2008
using Microsoft.SqlServer.Management.Sdk.Sfc;



namespace SQLScripter
{
  class ScripterHelper
  {
    private string _serverName;
    private string _databaseName;
    private string _path;
    private Server _server;
    private Database _db;
    private DateTime _lastUpdate = DateTime.MinValue;
    private DateTime _newLastUpdate = DateTime.MinValue;
    private string _lastUpdateFilename;
    private int _sqlmajorversion;

    public ScripterHelper(string serverName, string databaseName, string path)
    {
      _serverName = serverName;
      _databaseName = databaseName;
      _path = path;

      Console.WriteLine("Connecting database (Server Name = '{0}', Database Name = '{1}')...", serverName, databaseName);

      _server = new Server(serverName);
      _db = _server.Databases[databaseName];

      _sqlmajorversion = _server.Information.Version.Major;

      Console.WriteLine("Connected database (Server Name = '{0}', Database Name = '{1}'), SQL Version = {2}", serverName, databaseName, _sqlmajorversion);

      _lastUpdateFilename = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), serverName + "-" + databaseName + ".txt");

      //remove slashes from file name, and replace with underscore
      _lastUpdateFilename.Replace('\\', '_');
      _lastUpdateFilename.Replace('/', '_');

      if (File.Exists(_lastUpdateFilename))
      {
        _lastUpdate = DateTime.FromBinary(Convert.ToInt64(File.ReadAllText(_lastUpdateFilename)));
        _newLastUpdate = _lastUpdate;
      }
    }

    //tables
    public void ScriptTables()
    {
      Console.Write("Scripting Tables...");

      Scripter scripter = new Scripter();
      scripter.Server = _server;
      scripter.Options.IncludeHeaders = true;
      scripter.Options.SchemaQualify = true;
      scripter.Options.SchemaQualifyForeignKeysReferences = true;
      scripter.Options.NoCollation = true;
      scripter.Options.DriAllConstraints = true;
      scripter.Options.DriAll = true;
      scripter.Options.DriAllKeys = true;
      scripter.Options.DriIndexes = true;
      scripter.Options.ClusteredIndexes = true;
      scripter.Options.NonClusteredIndexes = true;
      scripter.Options.Triggers = true;
      scripter.Options.ToFileOnly = true;

      string tablesPath = Path.Combine(_path, "Tables");

      Dictionary<string, Table> sqlTables = new Dictionary<string, Table>();

      foreach (Table table in _db.Tables)
      {
        if (!table.IsSystemObject)
        {
          sqlTables.Add(table.Name, table);
        }
      }

      if (!Directory.Exists(tablesPath))
      {
        Directory.CreateDirectory(tablesPath);
      }
      else
      {
        string[] files = Directory.GetFiles(tablesPath, "*.sql");

        foreach (string file in files)
        {
          if (!sqlTables.ContainsKey(Path.GetFileNameWithoutExtension(file)))
            File.Delete(file);
        }
      }

      int index = 0;

      foreach (KeyValuePair<string, Table> kvp in sqlTables)
      {
          Table table = kvp.Value;
          DateTime tablelastmodified;

          //SQL version below 9 (eg SQL 2000) dows not support property DateLastModified
          if (_sqlmajorversion < 9)
            tablelastmodified = System.DateTime.Now; 
          else
            tablelastmodified = table.DateLastModified;

          if (tablelastmodified > _lastUpdate)
          {
            if (tablelastmodified > _newLastUpdate)
              _newLastUpdate = tablelastmodified;

            scripter.Options.FileName = Path.Combine(tablesPath, table.Name + ".sql");
            scripter.Script(new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[] { table.Urn });
            index++;
          }
      }

      Console.WriteLine(index);
    }

    //views
    public void ScriptViews()
    {
      Console.Write("Scripting Views...");

      Scripter scripter = new Scripter();
      scripter.Server = _server;
      scripter.Options.IncludeHeaders = false;
      scripter.Options.SchemaQualify = true;
      scripter.Options.SchemaQualifyForeignKeysReferences = true;
      scripter.Options.NoCollation = true;
      scripter.Options.DriAllConstraints = true;
      scripter.Options.DriAll = true;
      scripter.Options.DriAllKeys = true;
      scripter.Options.DriIndexes = true;
      scripter.Options.ClusteredIndexes = true;
      scripter.Options.NonClusteredIndexes = true;
      scripter.Options.ToFileOnly = true;

      string viewsPath = Path.Combine(_path, "Views");

      Dictionary<string, View> sqlViews = new Dictionary<string, View>();

      foreach (View view in _db.Views)
      {
        if (!view.IsSystemObject)
        {
          sqlViews.Add(view.Name, view);
        }
      }

      if (!Directory.Exists(viewsPath))
      {
        Directory.CreateDirectory(viewsPath);
      }
      else
      {
        string[] files = Directory.GetFiles(viewsPath, "*.sql");

        foreach (string file in files)
        {
          if (!sqlViews.ContainsKey(Path.GetFileNameWithoutExtension(file)))
            File.Delete(file);
        }
      }

      int index = 0;

      foreach (KeyValuePair<string, View> kvp in sqlViews)
      {
        View view = kvp.Value;
        DateTime viewlastmodified;

        //SQL version below 9 (eg SQL 2000) dows not support property DateLastModified
        if (_sqlmajorversion < 9)
          viewlastmodified = System.DateTime.Now;
        else
          viewlastmodified = view.DateLastModified;

        if (viewlastmodified > _lastUpdate)
        {
          if (viewlastmodified > _newLastUpdate)
            _newLastUpdate = viewlastmodified;

          scripter.Options.FileName = Path.Combine(viewsPath, view.Name + ".sql");
          scripter.Script(new Urn[] { view.Urn });
          index++;
        }
      }

      Console.WriteLine(index);
    }

    //stored procedures
    public void ScriptStoredProcedures()
    {
      Console.Write("Scripting Stored Procedures...");

      Scripter scripter = new Scripter();
      scripter.Server = _server;
      scripter.Options.IncludeHeaders = false;
      scripter.Options.SchemaQualify = true;
      scripter.Options.SchemaQualifyForeignKeysReferences = true;
      scripter.Options.NoCollation = true;
      scripter.Options.DriAllConstraints = true;
      scripter.Options.DriAll = true;
      scripter.Options.DriAllKeys = true;
      scripter.Options.DriIndexes = true;
      scripter.Options.ClusteredIndexes = true;
      scripter.Options.NonClusteredIndexes = true;
      scripter.Options.ToFileOnly = true;

      string storedProceduresPath = Path.Combine(_path, "StoredProcedures");

      Dictionary<string, StoredProcedure> sqlStoredProcedures = new Dictionary<string, StoredProcedure>();

      foreach (StoredProcedure storedProcedure in _db.StoredProcedures)
      {
        if (!storedProcedure.IsSystemObject)
        {
          sqlStoredProcedures.Add(storedProcedure.Name, storedProcedure);
        }
      }

      if (!Directory.Exists(storedProceduresPath))
      {
        Directory.CreateDirectory(storedProceduresPath);
      }
      else
      {
        string[] files = Directory.GetFiles(storedProceduresPath, "*.sql");

        foreach (string file in files)
        {
          if (!sqlStoredProcedures.ContainsKey(Path.GetFileNameWithoutExtension(file)))
            File.Delete(file);
        }
      }

      int index = 0;

      foreach (KeyValuePair<string, StoredProcedure> kvp in sqlStoredProcedures)
      {
        StoredProcedure storedProcedure = kvp.Value;
        DateTime storedlastmodified;

        //SQL version below 9 (eg SQL 2000) dows not support property DateLastModified
        if (_sqlmajorversion < 9)
          storedlastmodified = System.DateTime.Now;
        else
          storedlastmodified = storedProcedure.DateLastModified;

        if (storedlastmodified > _lastUpdate)
        {
          if (storedlastmodified > _newLastUpdate)
            _newLastUpdate = storedlastmodified;

          scripter.Options.FileName = Path.Combine(storedProceduresPath, storedProcedure.Name + ".sql");
          scripter.Script(new Urn[] { storedProcedure.Urn });
          index++;
        }
      }

      Console.WriteLine(index);
    }

    //database triggers
    public void ScriptDatabaseTriggers()
    {

      //database trigger are not supported in SQL 2000 or earlier
      if (_sqlmajorversion < 9) 
        return;
         
      Console.Write("Scripting Database Triggers...");


      Scripter scripter = new Scripter();
      scripter.Server = _server;
      scripter.Options.IncludeHeaders = true;
      scripter.Options.SchemaQualify = true;
      scripter.Options.SchemaQualifyForeignKeysReferences = true;
      scripter.Options.NoCollation = true;
      scripter.Options.DriAllConstraints = true;
      scripter.Options.DriAll = true;
      scripter.Options.DriAllKeys = true;
      scripter.Options.DriIndexes = true;
      scripter.Options.ClusteredIndexes = true;
      scripter.Options.NonClusteredIndexes = true;
      scripter.Options.ToFileOnly = true;

      string triggersPath = Path.Combine(_path, "DatabaseTriggers");

      Dictionary<string, Trigger> sqlTriggers = new Dictionary<string, Trigger>();

      foreach (Trigger trigger in _db.Triggers)
      {
        if (!trigger.IsSystemObject)
        {
          sqlTriggers.Add(trigger.Name, trigger);
        }
      }

      if (!Directory.Exists(triggersPath))
      {
        Directory.CreateDirectory(triggersPath);
      }
      else
      {
        string[] files = Directory.GetFiles(triggersPath, "*.sql");

        foreach (string file in files)
        {
          if (!sqlTriggers.ContainsKey(Path.GetFileNameWithoutExtension(file)))
            File.Delete(file);
        }
      }

      int index = 0;

      foreach (KeyValuePair<string, Trigger> kvp in sqlTriggers)
      {
        Trigger trigger = kvp.Value;
        DateTime triggerlastmodified;

        //SQL version below 9 (eg SQL 2000) dows not support property DateLastModified
        if (_sqlmajorversion < 9)
          triggerlastmodified = System.DateTime.Now;
        else
          triggerlastmodified = trigger.DateLastModified;

        if (triggerlastmodified > _lastUpdate)
        {
          if (triggerlastmodified > _newLastUpdate)
            _newLastUpdate = triggerlastmodified;

          scripter.Options.FileName = Path.Combine(triggersPath, trigger.Name + ".sql");
          scripter.Script(new Urn[] { trigger.Urn });
          index++;
        }
      }

      Console.WriteLine(index);
    }
    
    //user defined aggregates    
    public void ScriptUserDefinedAggregates()
    {

      //user defined aggreegates are not supported by SQL2005 ansd above
      if (_sqlmajorversion < 9)
        return;

      Console.Write("Scripting User Defined Aggregates...");

      Scripter scripter = new Scripter();
      scripter.Server = _server;
      scripter.Options.IncludeHeaders = false;
      scripter.Options.SchemaQualify = true;
      scripter.Options.SchemaQualifyForeignKeysReferences = true;
      scripter.Options.NoCollation = true;
      scripter.Options.DriAllConstraints = true;
      scripter.Options.DriAll = true;
      scripter.Options.DriAllKeys = true;
      scripter.Options.DriIndexes = true;
      scripter.Options.ClusteredIndexes = true;
      scripter.Options.NonClusteredIndexes = true;
      scripter.Options.ToFileOnly = true;

      string userDefinedAggregatesPath = Path.Combine(_path, "UserDefinedAggregates");

      Dictionary<string, UserDefinedAggregate> sqlUDA = new Dictionary<string, UserDefinedAggregate>();

      foreach (UserDefinedAggregate uda in _db.UserDefinedAggregates)
      {
        sqlUDA.Add(uda.Name, uda);
      }

      if (!Directory.Exists(userDefinedAggregatesPath))
      {
        Directory.CreateDirectory(userDefinedAggregatesPath);
      }
      else
      {
        string[] files = Directory.GetFiles(userDefinedAggregatesPath, "*.sql");

        foreach (string file in files)
        {
          if (!sqlUDA.ContainsKey(Path.GetFileNameWithoutExtension(file)))
            File.Delete(file);
        }
      }

      int index = 0;


      foreach (KeyValuePair<string, UserDefinedAggregate> kvp in sqlUDA)
      {
        UserDefinedAggregate uda = kvp.Value;
        DateTime udalastmodified;

        //SQL version below 9 (eg SQL 2000) dows not support property DateLastModified
        if (_sqlmajorversion < 9)
          udalastmodified = System.DateTime.Now;
        else
          udalastmodified = uda.DateLastModified;

        if (udalastmodified > _lastUpdate)
        {
          if (udalastmodified > _newLastUpdate)
            _newLastUpdate = udalastmodified;

          scripter.Options.FileName = Path.Combine(userDefinedAggregatesPath, uda.Name + ".sql");
          scripter.Script(new Urn[] { uda.Urn });
          index++;
        }
      }

      Console.WriteLine(index);
    }

    //user defined functions
    public void ScriptUserDefinedFunctions()
    {
      Console.Write("Scripting User Defined Functions...");

      Scripter scripter = new Scripter();
      scripter.Server = _server;
      scripter.Options.IncludeHeaders = false;
      scripter.Options.SchemaQualify = true;
      scripter.Options.SchemaQualifyForeignKeysReferences = true;
      scripter.Options.NoCollation = true;
      scripter.Options.DriAllConstraints = true;
      scripter.Options.DriAll = true;
      scripter.Options.DriAllKeys = true;
      scripter.Options.DriIndexes = true;
      scripter.Options.ClusteredIndexes = true;
      scripter.Options.NonClusteredIndexes = true;
      scripter.Options.ToFileOnly = true;

      string userDefinedFunctionsPath = Path.Combine(_path, "UserDefinedFunctions");

      Dictionary<string, UserDefinedFunction> sqlUDF = new Dictionary<string, UserDefinedFunction>();

      foreach (UserDefinedFunction udf in _db.UserDefinedFunctions)
      {
        if (!udf.IsSystemObject)
        {
          sqlUDF.Add(udf.Name, udf);
        }
      }

      if (!Directory.Exists(userDefinedFunctionsPath))
      {
        Directory.CreateDirectory(userDefinedFunctionsPath);
      }
      else
      {
        string[] files = Directory.GetFiles(userDefinedFunctionsPath, "*.sql");

        foreach (string file in files)
        {
          if (!sqlUDF.ContainsKey(Path.GetFileNameWithoutExtension(file)))
            File.Delete(file);
        }
      }

      int index = 0;

      foreach (KeyValuePair<string, UserDefinedFunction> kvp in sqlUDF)
      {
        UserDefinedFunction udf = kvp.Value;
        DateTime udflastmodified;

        //SQL version below 9 (eg SQL 2000) dows not support property DateLastModified
        if (_sqlmajorversion < 9)
          udflastmodified = System.DateTime.Now;
        else
          udflastmodified = udf.DateLastModified;

        if (udflastmodified > _lastUpdate)
        {
          if (udflastmodified > _newLastUpdate)
            _newLastUpdate = udflastmodified;

          scripter.Options.FileName = Path.Combine(userDefinedFunctionsPath, udf.Name + ".sql");
          scripter.Script(new Urn[] { udf.Urn });
          index++;
        }
      }

      Console.WriteLine(index);
    }

    //jobs
    public void ScriptJobs()
    {
      Console.Write("Scripting Jobs...");

      Scripter scripter = new Scripter();
      scripter.Server = _server;
      scripter.Options.IncludeHeaders = false;
      scripter.Options.SchemaQualify = true;
      scripter.Options.SchemaQualifyForeignKeysReferences = true;
      scripter.Options.NoCollation = true;
      scripter.Options.DriAllConstraints = true;
      scripter.Options.DriAll = true;
      scripter.Options.DriAllKeys = true;
      scripter.Options.DriIndexes = true;
      scripter.Options.ClusteredIndexes = true;
      scripter.Options.NonClusteredIndexes = true;
      scripter.Options.ToFileOnly = true;

      string JobsPath = Path.Combine(_path, "Jobs");

      Dictionary<string, Job> sqlJobs = new Dictionary<string, Job>();


      //foreach (Job job in _server.JobServer.Jobs)
      //{
      //  Console.WriteLine("-- Script for Job = {0}", job.Name);
      //  foreach (string str in job.Script())
      //    Console.WriteLine("{0}", str);
      //}


      foreach (Job job in _server.JobServer.Jobs)
      {
        sqlJobs.Add(job.Name, job);
      }

      if (!Directory.Exists(JobsPath))
      {
        Directory.CreateDirectory(JobsPath);
      }
      else
      {
        string[] files = Directory.GetFiles(JobsPath, "*.sql");

        foreach (string file in files)
        {
          if (!sqlJobs.ContainsKey(Path.GetFileNameWithoutExtension(file)))
            File.Delete(file);
        }
      }

      int index = 0;

      foreach (KeyValuePair<string, Job> kvp in sqlJobs)
      {
        Job job = kvp.Value;
        DateTime joblastmodified;

        //SQL version below 9 (eg SQL 2000) dows not support property DateLastModified
        if (_sqlmajorversion < 9)
          joblastmodified = System.DateTime.Now;
        else
          joblastmodified = job.DateLastModified;

        if (joblastmodified > _lastUpdate)
        {
          if (joblastmodified > _newLastUpdate)
            _newLastUpdate = joblastmodified;

          scripter.Options.FileName = Path.Combine(JobsPath, job.Name + ".sql");
          scripter.Script(new Urn[] { job.Urn });
          index++;
        }
      }

      Console.WriteLine(index);
    }

    public void SaveLastUpdate()
    {
      File.WriteAllText(_lastUpdateFilename, _newLastUpdate.ToBinary().ToString());
    }
  }
}


/*
using System;
using System.Collections;
using System.Collections.Specialized;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Common;

public class Junk
{
    public static void Main(string[] args)
    {
        Server srv = new Server("LINCHIS-LT1");

        foreach (Job job in srv.JobServer.Jobs)
        {
            Console.WriteLine("-- Script for Job = {0}", job.Name);
            foreach (string str in job.Script())
                Console.WriteLine("{0}", str);
        }

    }
}  // class Junk
 
  
  
  
 */