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 } } }