private void AppendFundctionScripts(Server server, Database database
, Dictionary <ScriptType, List <ScriptInfo> > scriptInfos, StringBuilder errorMessages)
{
RaiseMessageEvent("Start building function scripts");
Scripter scripter = new Scripter(server);
ScriptingOptions so = new ScriptingOptions();
so.IncludeIfNotExists = true;
so.IncludeHeaders = false;
so.Permissions = false;
so.ExtendedProperties = false;
so.ScriptDrops = false;
so.IncludeDatabaseContext = false;
so.NoCollation = true;
so.NoFileGroup = true;
so.NoIdentities = false;
scripter.Options = so;
server.SetDefaultInitFields(typeof(Table), "IsSystemObject");
database.UserDefinedFunctions.Refresh();
StringCollection sc;
Queue <ScriptInfo> bodiesQueue = new Queue <ScriptInfo>();
Queue <ScriptInfo> namesQueue = new Queue <ScriptInfo>();
ScriptInfo scriptInfo;
StringBuilder sb = new StringBuilder();
foreach (UserDefinedFunction userDefinedFunction in database.UserDefinedFunctions)
{
if (!userDefinedFunction.IsSystemObject)
{
RaiseMessageEvent("Generate scripts for function " + userDefinedFunction.Name);
scriptInfos[ScriptType.DropFunction].Add(new ScriptInfo(
ScriptType.DropFunction
, "dbo.drop.function." + userDefinedFunction.Name + ".sql"
, @"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + userDefinedFunction.Name + @"]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
DROP FUNCTION [dbo].[" + userDefinedFunction.Name + @"];
END
GO"));
sc = scripter.Script(new Urn[] { userDefinedFunction.Urn });
sb.Clear();
foreach (string str in sc)
{
if (str == "SET ANSI_NULLS ON" ||
str == "SET QUOTED_IDENTIFIER ON")
{
continue;
}
string text =
@"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + userDefinedFunction.Name + @"]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
DROP FUNCTION [dbo].[" + userDefinedFunction.Name + @"];
END
GO
";
scriptInfo = new ScriptInfo(
ScriptType.Function
, "dbo.function." + userDefinedFunction.Name + ".sql"
, text + str + Environment.NewLine + "GO"
, userDefinedFunction.Name
, RemoveSqlComments(str).ToLowerInvariant());
namesQueue.Enqueue(scriptInfo);
bodiesQueue.Enqueue(scriptInfo);
}
}
}
int fullRun = namesQueue.Count;
int counter = 0;
int lastRunCount = 0;
Regex regex;
ScriptInfo current;
ScriptInfo peek;
RaiseMessageEvent("Resolving function references");
bool flag = false;
while (namesQueue.Count != 0)
{
flag = false;
current = namesQueue.Dequeue();
peek = bodiesQueue.Peek();
foreach (ScriptInfo scriptInfoFromNamesQueue in namesQueue)
{
regex = new Regex(@"( |\.)" + scriptInfoFromNamesQueue.Name.ToLowerInvariant() + @"( |\()");
if (scriptInfoFromNamesQueue.Name == peek.Name)
{
continue;
}
if (regex.IsMatch(peek.FormattedText)) // TODO (Roman): this should be a formatted text
{
flag = true;
scriptInfoFromNamesQueue.ReferencedFunctions.Add(peek.Name);
//break;
}
}
if (!flag)
{
scriptInfos[ScriptType.Function].Add(bodiesQueue.Dequeue());
}
else
{
bodiesQueue.Enqueue(bodiesQueue.Dequeue());
namesQueue.Enqueue(current);
}
counter++;
if (counter == fullRun)
{
counter = 0;
if (namesQueue.Count == lastRunCount &&
lastRunCount != 0)
{
RaiseMessageEvent("Circular dependencies detected at functions");
break;
}
lastRunCount = fullRun = namesQueue.Count;
}
}
bool first = true;
foreach (ScriptInfo scriptInfoFromNamesQueue in namesQueue)
{
sb.Clear();
first = true;
sb.Append("Function " + scriptInfoFromNamesQueue.Name + " has a (potential) circular reference to other functions. Referenced functions: ");
foreach (string reference in scriptInfoFromNamesQueue.ReferencedFunctions)
{
sb.Append((first ? string.Empty : ", ") + reference);
first = false;
}
scriptInfos[ScriptType.Function].Add(scriptInfoFromNamesQueue);
RaiseMessageEvent(sb.ToString());
errorMessages.Append(Environment.NewLine);
errorMessages.Append(sb);
}
}
private void AppendTableScripts(Server server, Database database
, Dictionary <ScriptType, List <ScriptInfo> > scriptInfos)
{
RaiseMessageEvent("Start building table scripts");
Scripter scripter = new Scripter(server);
Scripter scripter2 = new Scripter(server);
ScriptingOptions so = new ScriptingOptions();
so.IncludeIfNotExists = true;
so.IncludeHeaders = false;
so.Permissions = false;
so.ExtendedProperties = false;
so.ScriptDrops = false;
so.IncludeDatabaseContext = false;
so.NoCollation = true;
so.NoFileGroup = true;
so.NoIdentities = false;
so.Indexes = true;
so.DriAll = true;
so.DriChecks = true;
scripter.Options = so;
ScriptingOptions so2 = new ScriptingOptions();
so2.IncludeIfNotExists = true;
so2.ScriptDrops = false;
scripter2.Options = so2;
server.SetDefaultInitFields(typeof(Table), "IsSystemObject");
database.Tables.Refresh();
StringCollection sc;
foreach (Table table in database.Tables)
{
if (!table.IsSystemObject)
{
RaiseMessageEvent("Generate scripts for table " + table.Name);
scriptInfos[ScriptType.DropTable].Add(new ScriptInfo(
ScriptType.DropTable
, "dbo.drop.table." + table.Name + ".sql"
, "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[" + table.Name + @"];
END
GO"));
sc = scripter.Script(new Urn[] { table.Urn });
StringBuilder sb = new StringBuilder();
#region do tables, indexes and constraints
foreach (string str in sc)
{
if (str == "SET ANSI_NULLS ON" ||
str == "SET QUOTED_IDENTIFIER ON")
{
continue;
}
if (str.Contains("CREATE TABLE"))
{
scriptInfos[ScriptType.Table].Add(new ScriptInfo(
ScriptType.Table
, "dbo.table." + table.Name + ".sql"
, str + Environment.NewLine + "GO"));
}
else if (str.StartsWith("IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].["))
{
int index = "IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[".Length;
string name = str.Substring(index, str.IndexOf(']', index) - index);
scriptInfos[ScriptType.DropConstraint].Add(new ScriptInfo(
ScriptType.DropConstraint
, "dbo.drop.constraint." + name + ".sql"
, "IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[" + name + @"]') AND parent_object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"]'))
BEGIN
ALTER TABLE [dbo].[" + table.Name + @"] DROP CONSTRAINT " + name + @"
END
GO"));
scriptInfos[ScriptType.Constraint].Add(new ScriptInfo(
ScriptType.Constraint
, "dbo.constraint." + str.Substring(index, str.IndexOf(']', index) - index) + ".sql"
, str + Environment.NewLine + "GO"));
}
else if (str.StartsWith("IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].["))
{
int index = "IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[".Length;
ScriptInfo scriptInfo = scriptInfos[ScriptType.Constraint].Find(c => c.FileName == "dbo.constraint." + str.Substring(index, str.IndexOf(']', index) - index) + ".sql");
if (scriptInfo != null)
{
scriptInfo.Text += Environment.NewLine + Environment.NewLine + str + Environment.NewLine + "GO";
}
else
{
scriptInfos[ScriptType.Constraint].Add(new ScriptInfo(
ScriptType.Constraint
, "dbo.constraint." + str.Substring(index, str.IndexOf(']', index) - index) + ".sql"
, str + Environment.NewLine + "GO"));
}
}
else if (str.StartsWith("\r\nIF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].["))
{
int index = str.IndexOf("AND name = N'") + 13;
string name = str.Substring(index, str.IndexOf('\'', index) - index);
scriptInfos[ScriptType.DropIndex].Add(new ScriptInfo(
ScriptType.DropIndex
, "dbo.drop.index." + name + ".sql"
, @"IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[" + name + @"]') AND name = N'" + table.Name + @"')
BEGIN
ALTER TABLE [dbo].[" + table.Name + @"] DROP CONSTRAINT [" + name + @"]
END
GO"));
scriptInfos[ScriptType.Index].Add(new ScriptInfo(
ScriptType.Index
, "dbo.index." + name + ".sql"
, str.TrimStart("\r\n".ToCharArray()) + Environment.NewLine + "GO"));
}
else if (str.StartsWith("IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].["))
{
int index = "IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[".Length;
string name = str.Substring(index, str.IndexOf(']', index) - index);
scriptInfos[ScriptType.DropCheck].Add(new ScriptInfo(
ScriptType.DropCheck
, "dbo.drop.check." + name + ".sql"
,
@"
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[" + name + @"]') AND parent_object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"]'))
BEGIN
ALTER TABLE [dbo].[" + table.Name + @"] DROP CONSTRAINT [" + name + @"]
END
GO"));
scriptInfos[ScriptType.Check].Add(new ScriptInfo(
ScriptType.Check
, "dbo.check." + name + ".sql"
, str + Environment.NewLine + "GO"));
}
else
{
}
}
#endregion
#region default constraints
foreach (Column column in table.Columns)
{
if (column.DefaultConstraint != null)
{
string dropText =
@"
IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[" + column.DefaultConstraint.Name + @"]') AND parent_object_id = OBJECT_ID(N'[dbo].[" + table.Name + @"]'))
BEGIN
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[" + column.DefaultConstraint.Name + @"]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[" + table.Name + @"] DROP CONSTRAINT [" + column.DefaultConstraint.Name + @"]
END
END
GO";
scriptInfos[ScriptType.DropDefault].Add(new ScriptInfo(
ScriptType.DropDefault
, "dbo.drop.default." + column.DefaultConstraint.Name + ".sql"
, dropText));
foreach (string str in scripter2.Script(new Urn[] { column.DefaultConstraint.Urn }))
{
scriptInfos[ScriptType.Default].Add(new ScriptInfo(
ScriptType.Default
, "dbo.default." + column.DefaultConstraint.Name + ".sql"
, str.TrimStart("\r\n".ToCharArray()) + Environment.NewLine + "GO"));
}
}
}
#endregion
}
}
}