using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using Microsoft.SqlServer.Management.Smo;

// Requires the following references
// Microsoft.SqlServer.ConnectionInfo
// Microsoft.SqlServer.Smo
// Microsoft.SqlServer.SmoEnum
// Microsoft.SqlServer.SqlEnum

public class Sample
{
	public void GenerateScripts()
	{
		// Must be run 32-bit mode or BatchParser will fail.  This is because the server installed is 32bit

		Microsoft.SqlServer.Management.Smo.Server srv = new Microsoft.SqlServer.Management.Smo.Server("server\Instance");
		srv.ConnectionContext.LoginSecure = false;
		srv.ConnectionContext.Login = "User";
		srv.ConnectionContext.Password = "Password";
		Database db = srv.Databases["DatabaseName"];

		string filepath = @"c:\SomePath\SPExport.sql";
		if (System.IO.File.Exists(filepath)) {
			System.IO.File.Delete(filepath);
		}


		// Script out Drop all Current user defined SPs
		using (TextWriter tw = new StreamWriter(filepath, true, System.Text.Encoding.UTF8)) {

			foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure sp in db.StoredProcedures) {
				if (sp.IsSystemObject == false) {
					// We do not want system SPs, only user defined SPs
					string drop = string.Format("IF OBJECTPROPERTY(object_id('dbo.{0}'), N'IsProcedure') = 1{1} DROP PROCEDURE [dbo].[{0}]", sp.Name, System.Environment.NewLine);
					tw.WriteLine(drop);
					tw.WriteLine("GO");
				}
			}
		}

		// Script out drop all current user defined functions
		using (TextWriter tw = new StreamWriter(filepath, true, System.Text.Encoding.UTF8)) {

			foreach (Microsoft.SqlServer.Management.Smo.UserDefinedFunction func in db.UserDefinedFunctions) {
				if (func.IsSystemObject == false) {
					// We do not want system functions, only user defined functions
					string drop = string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')){1}DROP FUNCTION [dbo].[{0}]", func.Name, System.Environment.NewLine);
					tw.WriteLine(drop);
					tw.WriteLine("GO");
				}
			}
		}

		// Script out create all user defined functions
		using (TextWriter tw = new StreamWriter(filepath, true, System.Text.Encoding.UTF8)) {
			foreach (Microsoft.SqlServer.Management.Smo.UserDefinedFunction func in db.UserDefinedFunctions) {
				if (func.IsSystemObject == false) {
					// We do not want system functions, only user defined functions
					foreach (string sqlScript in func.Script()) {
						tw.WriteLine(sqlScript);
						tw.WriteLine("GO");
					}
				}
			}
		}


		// Script out create all user defined Sps
		using (TextWriter tw = new StreamWriter(filepath, true, System.Text.Encoding.UTF8)) {
			foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure sp in db.StoredProcedures) {
				if (sp.IsSystemObject == false) {
					// We do not want system SPs, only user defined SPs
					foreach (string sqlScript in sp.Script()) {
						tw.WriteLine(sqlScript);
						tw.WriteLine("GO");
					}
				}
			}
		}


	}
}