private List <MssqlScriptInfo> GetServiceBrokerServices(Server server, string databaseName) { const string method = nameof(GetServiceBrokerServices); _logger?.LogInformation($"{method} on {server}({databaseName}) begin."); var database = server.Databases[databaseName]; var scriptCollection = new List <MssqlScriptInfo>(database.ServiceBroker.Services.Count); foreach (BrokerService bs in database.ServiceBroker.Services) { if (bs.IsSystemObject) { continue; } var scriptInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server), DatabaseName = databaseName }, Type = "ServiceBrokerService", Name = bs.Name, Body = MapToString(bs.Script(_scriptingOption)) }; scriptCollection.Add(scriptInfo); } _logger?.LogInformation($"{method} on {server}({databaseName}) end."); return(scriptCollection); }
private List <MssqlScriptInfo> GetServiceBrokerRemoteBinding(Server server, string databaseName) { const string method = nameof(GetServiceBrokerRemoteBinding); _logger?.LogInformation($"{method} on {server}({databaseName}) begin."); var database = server.Databases[databaseName]; var scriptCollection = new List <MssqlScriptInfo>(database.ServiceBroker.RemoteServiceBindings.Count); foreach (RemoteServiceBinding rsb in database.ServiceBroker.RemoteServiceBindings) { var scriptInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server), DatabaseName = databaseName }, Type = "ServiceBrokerRemoteBinding", Name = rsb.Name, Body = MapToString(rsb.Script(_scriptingOption)) }; scriptCollection.Add(scriptInfo); } _logger?.LogInformation($"{method} on {server}({databaseName}) end."); return(scriptCollection); }
private List <MssqlScriptInfo> GetStoredProcedures(Server server, string databaseName) { const string method = nameof(GetStoredProcedures); _logger?.LogInformation($"{method} on {server}({databaseName}) begin."); var database = server.Databases[databaseName]; var scriptCollection = new List <MssqlScriptInfo>(database.StoredProcedures.Count); foreach (StoredProcedure sp in database.StoredProcedures) { if (sp.IsSystemObject) { continue; } if (_schemaExcluded.Contains(sp.Schema.ToLower())) { continue; } var scriptInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server), DatabaseName = databaseName }, Type = "StoredProcedure", Schema = sp.Schema, Name = sp.Name, Body = MapToString(sp.Script(_scriptingOption)) }; scriptCollection.Add(scriptInfo); } _logger?.LogInformation($"{method} on {server}({databaseName}) end."); return(scriptCollection); }
private List <MssqlScriptInfo> GetUserDefinedTypes(Server server, string databaseName) { const string method = nameof(GetUserDefinedTypes); _logger?.LogInformation($"{method} on {server}({databaseName}) begin."); var database = server.Databases[databaseName]; var scriptCollection = new List <MssqlScriptInfo>(database.UserDefinedTypes.Count); foreach (UserDefinedType udt in database.UserDefinedTypes) { if (_schemaExcluded.Contains(udt.Schema.ToLower())) { continue; } var scriptInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server), DatabaseName = databaseName }, Type = "UserDefinedTableType", Schema = udt.Schema, Name = udt.Name, Body = MapToString(udt.Script(_scriptingOption)) }; scriptCollection.Add(scriptInfo); } _logger?.LogInformation($"{method} on {server}({databaseName}) end."); return(scriptCollection); }
private List <MssqlScriptInfo> GetPartitionFunctions(Server server, string databaseName) { const string method = nameof(GetPartitionFunctions); _logger?.LogInformation($"{method} on {server}({databaseName}) begin."); var database = server.Databases[databaseName]; var scriptCollection = new List <MssqlScriptInfo>(database.PartitionFunctions.Count); foreach (PartitionFunction pf in database.PartitionFunctions) { var scriptInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server), DatabaseName = databaseName }, Type = "PartitionFunction", Name = pf.Name, Body = MapToString(pf.Script(_scriptingOption)) }; scriptCollection.Add(scriptInfo); } _logger?.LogInformation($"{method} on {server}({databaseName}) end."); return(scriptCollection); }
private List <MssqlScriptInfo> GetEndpoints(Server server) { const string method = nameof(GetEndpoints); _logger?.LogInformation($"{method} on {server} begin."); var scriptCollection = new List <MssqlScriptInfo>(server.Endpoints.Count); foreach (Endpoint ep in server.Endpoints) { if (ep.IsSystemObject) { continue; } var scriptInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server) }, Type = "Endpoint", Schema = ep.Owner, Name = ep.Name, Body = MapToString(ep.Script(_scriptingOption)) }; scriptCollection.Add(scriptInfo); } _logger?.LogInformation($"{method} on {server} end."); return(scriptCollection); }
private List <MssqlScriptInfo> GetServerJob(Server server) { const string method = nameof(GetServerJob); _logger?.LogInformation($"{method} on {server} begin."); var scriptCollection = new List <MssqlScriptInfo>(server.JobServer.Jobs.Count); foreach (Job job in server.JobServer.Jobs) { var scriptInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server) }, Type = "Job", Schema = job.Category, Name = job.Name, Body = MapToString(job.Script(_scriptingOption)) }; scriptCollection.Add(scriptInfo); // Steps foreach (JobStep step in job.JobSteps) { var stepScriptInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server) }, Type = "JobStep", Name = step.Name, Schema = job.Name, Body = MapToString(step.Script(_scriptingOption)) }; scriptCollection.Add(stepScriptInfo); } // Scheduller foreach (JobSchedule schedule in job.JobSchedules) { var schedullerScriptInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server) }, Type = "JobStep", Name = schedule.Name, Schema = job.Name, Body = MapToString(schedule.Script(_scriptingOption)) }; scriptCollection.Add(schedullerScriptInfo); } } _logger?.LogInformation($"{method} on {server} end."); return(scriptCollection); }
private List <MssqlScriptInfo> GetViews(Server server, string databaseName) { const string method = nameof(GetViews); _logger?.LogInformation($"{method} on {server}({databaseName}) begin."); var database = server.Databases[databaseName]; var scriptCollection = new List <MssqlScriptInfo>(database.Views.Count); foreach (View view in database.Views) { if (view.IsSystemObject) { continue; } if (_schemaExcluded.Contains(view.Schema.ToLower())) { continue; } var viewScript = new StringBuilder(); viewScript.AppendLine(MapToString(view.Script(_scriptingOption))); // Index foreach (Microsoft.SqlServer.Management.Smo.Index index in view.Indexes) { if (index.IsClustered) { viewScript.AppendLine(MapToString(index.Script(_scriptingOption))); continue; } var indexScript = new StringBuilder(); indexScript.AppendLine(MapToString(index.Script(_scriptingOption))); var scriptIndexInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server), DatabaseName = databaseName }, Type = "Index", Schema = $"{view.Schema}.{view.Name}", Name = index.Name, Body = indexScript.ToString() }; scriptCollection.Add(scriptIndexInfo); } var scriptInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server), DatabaseName = databaseName }, Type = "View", Schema = view.Schema, Name = view.Name, Body = viewScript.ToString() }; scriptCollection.Add(scriptInfo); } _logger?.LogInformation($"{method} on {server}({databaseName}) end."); return(scriptCollection); }
private List <MssqlScriptInfo> GetTables(Server server, string databaseName) { const string method = nameof(GetTables); _logger?.LogInformation($"{method} on {server}({databaseName}) begin."); var database = server.Databases[databaseName]; var scriptCollection = new List <MssqlScriptInfo>(database.Tables.Count); foreach (Table table in database.Tables) { if (table.IsSystemObject) { continue; } var tableScript = new StringBuilder(); // Tables if (_schemaExcluded.Contains(table.Schema.ToLower())) { continue; } if (table.IsSystemObject) { continue; } tableScript.AppendLine(MapToString(table.Script(_scriptingOption))); // Check constraint foreach (Check check in table.Checks) { tableScript.AppendLine(MapToString(check.Script(_scriptingOption))); } // Foreign Key foreach (ForeignKey foreignKey in table.ForeignKeys) { var foreignKeyScript = new StringBuilder(); foreignKeyScript.AppendLine(MapToString(foreignKey.Script(_scriptingOption))); var scriptForeignKeyInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server), DatabaseName = databaseName }, Type = "ForeignKey", Schema = $"{table.Schema}.{table.Name}", Name = foreignKey.Name, Body = foreignKeyScript.ToString() }; scriptCollection.Add(scriptForeignKeyInfo); } foreach (Trigger trigger in table.Triggers) { var triggerScript = new StringBuilder(); triggerScript.AppendLine(MapToString(trigger.Script(_scriptingOption))); var scriptIndexInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server), DatabaseName = databaseName }, Type = "Trigger", Schema = $"{table.Schema}.{table.Name}", Name = trigger.Name, Body = triggerScript.ToString() }; scriptCollection.Add(scriptIndexInfo); } // Index foreach (Microsoft.SqlServer.Management.Smo.Index index in table.Indexes) { if (index.IsClustered) { tableScript.AppendLine(MapToString(index.Script(_scriptingOption))); continue; } var indexScript = new StringBuilder(); indexScript.AppendLine(MapToString(index.Script(_scriptingOption))); var scriptIndexInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server), DatabaseName = databaseName }, Type = "Index", Schema = $"{table.Schema}.{table.Name}", Name = index.Name, Body = indexScript.ToString() }; scriptCollection.Add(scriptIndexInfo); } var scriptTableInfo = new MssqlScriptInfo { Location = new ScriptInfoLocation { ServerName = GetServerName(server), DatabaseName = databaseName }, Type = "Table", Schema = table.Schema, Name = table.Name, Body = tableScript.ToString() }; scriptCollection.Add(scriptTableInfo); } _logger?.LogInformation($"{method} on {server}({databaseName}) end."); return(scriptCollection); }