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");
}
}
}
}
}
}