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