In few of my past projects I have always encountered the scenario multiple times; when for few glitches in the application I had to quite frequently change the SQL object attributes like table column names, stored procedure logic and more.
And for one or more changes to move into production involved generation of release script for the deployment each time which has been tedious so far and missing out on one or more set of changes has lead to multiple troubled releases.
Thus to address the above problem I have written a tool for myself to help me take care of deployment script generation (Create/Rollback) for the set of SQL objects, and associated preventive statements to make the production SQL scripts robust as possible. Also this tool generates a rollback script for the same set of objects, objects in-case the user/process calls for it.
Description:
This tool takes in your database details from which the SQL objects needs to be scripted, where the scripted files have to be stored and the list of SQL objects which need to be scripted in a text file.
1. Input file:
This file contains the list of SQL objects which needs to be scripted.
This file contain the set of markers which identify the SQL objects type and appropriate code type to be generated for them.
Example: sqlObjects.txt
<#TABLE#>dbo.TableAlpha
-Script the table with preventive code in place
<#TABLEWTIRGGERS#>dbo.TableBeta
-Script the table and its associated triggers with preventive code in place
<#STOREDPROCEDURE#>dbo.SP00Gamma
-Script the stored procedure with preventive code in place
<#USERDEFINEDFUNCTION#>dbo.FN00Theta
-Script user defined function with preventive code in place
<#TRIGGER#>dbo.TableBeta|TriggerName
-Script the trigger only associated with the specified table before marker with preventive code in place
2. Application configuration file:
Please specify the required details for the tool to generate scripts.
The tool uses the same scripting engine which SQL server management studio uses which produces the seamless result as required and easy enough to customise also. (Using the SQL Server Management Objects (SMO) libraries)
Tool Code:
class Program
{
static void Main(string[] args)
{
Server myServer = new Server(ConfigurationManager.AppSettings["sqlInstance"]);
//Using windows authentication
myServer.ConnectionContext.LoginSecure = true;
myServer.ConnectionContext.Connect();
if (myServer.ConnectionContext.IsOpen)
{
Database myDatabase = myServer.Databases[ConfigurationManager.AppSettings["sqlDatabase"]];
string createPath = ConfigurationManager.AppSettings["createScriptPath"];
string rollBackPath = ConfigurationManager.AppSettings["rollbackScriptPath"];
if (File.Exists(createPath))
{
File.Delete(createPath);
}
if (File.Exists(rollBackPath))
{
File.Delete(rollBackPath);
}
Scripter scripter = new Scripter(myServer);
ScriptingOptions createOptions = new ScriptingOptions();
createOptions.AnsiPadding = true;
createOptions.ScriptBatchTerminator = true;
createOptions.DriAll = true;
createOptions.ToFileOnly = true;
createOptions.FileName = createPath;
createOptions.IncludeDatabaseContext = true;
createOptions.IncludeIfNotExists = true;
createOptions.AppendToFile = true;
createOptions.AnsiFile = true;
ScriptingOptions rollBackOptions = new ScriptingOptions();
rollBackOptions.AnsiPadding = true;
rollBackOptions.ScriptBatchTerminator = true;
rollBackOptions.DriAll = true;
rollBackOptions.ToFileOnly = true;
rollBackOptions.FileName = rollBackPath;
rollBackOptions.IncludeDatabaseContext = true;
rollBackOptions.IncludeIfNotExists = true;
rollBackOptions.AppendToFile = true;
rollBackOptions.ScriptDrops = true;
rollBackOptions.AnsiFile = true;
StreamReader file = new StreamReader(ConfigurationManager.AppSettings["sqlObjects"]);
string line;
int counter = 0;
while ((line = file.ReadLine()) != null)
{
Console.WriteLine("Scripting : " + line);
//Insert Comments
string objectName;
if(line.Contains("<#TABLEWTIRGGERS#>"))
{
objectName = line.Replace("<#TABLEWTIRGGERS#>", "");
AppendTextToFile(createPath, "-- ***********************");
AppendTextToFile(createPath, "-- CREATE Script for : " + objectName);
AppendTextToFile(createPath, "-- ***********************");
AppendTextToFile(rollBackPath, "-- ***********************");
AppendTextToFile(rollBackPath, "-- DROP Script for : " + objectName);
AppendTextToFile(rollBackPath, "-- ***********************");
//Script tables
ScriptTables(myDatabase, createOptions, rollBackOptions, objectName.Split('.')[1], objectName.Split('.')[0]);
//Script Tiggers
ScriptTriggers(myDatabase, createOptions, rollBackOptions, objectName.Split('.')[1], null,
objectName.Split('.')[0]);
}
else if (line.Contains("<#TABLE#>"))
{
objectName = line.Replace("<#TABLE#>", "");
AppendTextToFile(createPath, "-- ***********************");
AppendTextToFile(createPath, "-- CREATE Script for : " + objectName);
AppendTextToFile(createPath, "-- ***********************");
AppendTextToFile(rollBackPath, "-- ***********************");
AppendTextToFile(rollBackPath, "-- DROP Script for : " + objectName);
AppendTextToFile(rollBackPath, "-- ***********************");
//Script tables
ScriptTables(myDatabase, createOptions, rollBackOptions, objectName.Split('.')[1], objectName.Split('.')[0]);
}
else if (line.Contains("<#TRIGGER#>"))
{
objectName = line.Replace("<#TRIGGER#>", "");
string tableName = objectName.Split('|')[0];
string trigName = objectName.Split('|')[1];
AppendTextToFile(createPath, "-- ***********************");
AppendTextToFile(createPath, "-- CREATE Script for : " + objectName);
AppendTextToFile(createPath, "-- ***********************");
AppendTextToFile(rollBackPath, "-- ***********************");
AppendTextToFile(rollBackPath, "-- DROP Script for : " + objectName);
AppendTextToFile(rollBackPath, "-- ***********************");
//Script Tiggers
ScriptTriggers(myDatabase, createOptions, rollBackOptions, tableName.Split('.')[1], trigName,
tableName.Split('.')[0]);
}
else if (line.Contains("<#STOREDPROCEDURE#>"))
{
objectName = line.Replace("<#STOREDPROCEDURE#>", "");
AppendTextToFile(createPath, "-- ***********************");
AppendTextToFile(createPath, "-- CREATE Script for : " + objectName);
AppendTextToFile(createPath, "-- ***********************");
AppendTextToFile(rollBackPath, "-- ***********************");
AppendTextToFile(rollBackPath, "-- DROP Script for : " + objectName);
AppendTextToFile(rollBackPath, "-- ***********************");
//Script Stored Procedures
ScriptStoredProcedure(myDatabase, createOptions, rollBackOptions, objectName.Split('.')[1], objectName.Split('.')[0]);
}
else if (line.Contains("<#USERDEFINEDFUNCTION#>"))
{
objectName = line.Replace("<#USERDEFINEDFUNCTION#>", "");
AppendTextToFile(createPath, "-- ***********************");
AppendTextToFile(createPath, "-- CREATE Script for : " + objectName);
AppendTextToFile(createPath, "-- ***********************");
AppendTextToFile(rollBackPath, "-- ***********************");
AppendTextToFile(rollBackPath, "-- DROP Script for : " + objectName);
AppendTextToFile(rollBackPath, "-- ***********************");
//Script User Defined Functions
ScriptUserDefinedFunctions(myDatabase, createOptions, rollBackOptions, objectName.Split('.')[1], objectName.Split('.')[0]);
}
counter++;
}
file.Close();
}
}
private static void AppendTextToFile(string path, string text)
{
// This text is added only once to the file.
if (!File.Exists(path))
{
// Create a file to write to.
using (StreamWriter sw = File.CreateText(path))
{
sw.WriteLine(text);
}
}
else
{
using (StreamWriter sw = File.AppendText(path))
{
sw.WriteLine(text);
}
}
}
private static void ScriptTables(Database sDatabase, ScriptingOptions cOption, ScriptingOptions rOption, string tableName, string schemaName)
{
Table table = sDatabase.Tables[tableName, schemaName];
table.Script(cOption);
table.Script(rOption);
}
private static void ScriptStoredProcedure(Database sDatabase, ScriptingOptions cOption, ScriptingOptions rOption, string procName, string schemaName)
{
StoredProcedure storedProcedure = sDatabase.StoredProcedures[procName, schemaName];
storedProcedure.Script(cOption);
storedProcedure.Script(rOption);
}
private static void ScriptUserDefinedFunctions(Database sDatabase, ScriptingOptions cOption, ScriptingOptions rOption, string funcName, string schemaName)
{
UserDefinedFunction userFunction = sDatabase.UserDefinedFunctions[funcName, schemaName];
userFunction.Script(cOption);
userFunction.Script(rOption);
}
private static void ScriptTriggers(Database sDatabase, ScriptingOptions cOption, ScriptingOptions rOption,string tableName , string trigName, string schemaName)
{
Table table = sDatabase.Tables[tableName, schemaName];
foreach (Trigger trig in table.Triggers)
{
if (trigName == null)
{
trig.Script(cOption);
trig.Script(rOption);
}
else if (trigName == trig.Name)
{
trig.Script(cOption);
trig.Script(rOption);
}
}
}
}
Output:
createScript.sql
-- *********************** -- CREATE Script for : dbo.TableBeta -- *********************** USE [alpha] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableBeta]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TableBeta]( [activityLevelId] [numeric](15, 0) IDENTITY(1,1) NOT NULL, [activityLevelCode] [varchar](3) COLLATE Latin1_General_CS_AS NULL, [description] [varchar](16) COLLATE Latin1_General_CS_AS NOT NULL, [activityLevelValue] [int] NULL, [display] [tinyint] NULL, [sortOrder] [int] NULL, [lastUpdatedUser] [varchar](8) COLLATE Latin1_General_CS_AS NULL, [lastUpdatedDate] [datetime] NOT NULL, CONSTRAINT [ActivityLevel$activityLevelId] PRIMARY KEY CLUSTERED ( [activityLevelId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [alpha_Group1] ) ON [alpha_Group1] END GO SET ANSI_PADDING ON GO
rollbackScript.sql
-- *********************** -- DROP Script for : dbo.TableBeta -- *********************** USE [alpha] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableBeta]') AND type in (N'U')) DROP TABLE [dbo].[TableBeta] GO -- *********************** -- DROP Script for : dbo.SP00BetaProc -- *********************** USE [alpha] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP00BetaProc]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SP00BetaProc] GO
Downloads:
Download
Refrences:
http://msdn.microsoft.com/en-us//library/ms162169.aspx
