public static AvailabilityGroup GetAG(string agName, Smo.Server server)
{
server.Refresh();
server.AvailabilityGroups.Refresh();
AvailabilityGroup ag = server.AvailabilityGroups[agName];
ag.Refresh();
return(ag);
}
/// <summary>
/// Checks whether the given replica is primary replica on an Availability group or not.
/// </summary>
/// <param name="arName">Replica name that needs to be checked</param>
/// <param name="agName">Availability Group name</param>
/// <param name="server">SMO Server on which being checked.</param>
/// <returns>True if the given replica is primary, otherwise false.</returns>
public static bool IsPrimaryReplica(string arName, string agName, SMO.Server server)
{
server.Refresh();
server.AvailabilityGroups.Refresh();
AvailabilityGroup ag = server.AvailabilityGroups[agName];
ag.Refresh();
return(ag.PrimaryReplicaServerName.Equals(arName));
}
public async Task <string> GetObjectDefinition(SysObject so)
{
using (var cn = new SqlConnection(ConnectionViewModel.ConnectionString()))
{
var srvConnect = new ServerConnection(cn);
var srv = new smo.Server(srvConnect);
srv.Refresh();
SetDefaultInitFields(srv);
smo.Database database = null;
StringBuilder sb = null;
await Task.Factory.StartNew(() =>
{
database = srv.Databases[so.db_name];
// SMO keeps different top level collections for each type of object. You can't go to the DB and say,
// "Give me the object with this URI," even though each object DOES have a URI property. In some cases,
// you have to supply a parent object (e.g. Triggers). So the switch block below figures out how to
// grab each object from the database based on its type.
//
// Worse yet, even though each of them does contain a method called Script() that takes a single parameter
// of type ScriptOptions, there's no base object or interface that defines these objects as something
// that can script itself. To get make this easier, we cheat and just use a dynamic type then call the
// Script() method on that dynamic object. This is pretty hokey, but this only gets called once each
// time the user requests that an object get scripted out, i.e. it's not running in a loop thousands of times.
dynamic scriptableObject = null;
switch (so.type_desc)
{
case "CHECK_CONSTRAINT":
scriptableObject = database.Tables[so.parent_object_name, so.parent_object_schema_name].Checks[so.object_name];
break;
case "DEFAULT_CONSTRAINT":
// We basically ignore default constraints - they get scripted out as part of scripting a table... I think that's why I did this...
//ob = db.Tables[so.parent_object_name, so.parent_object_schema_name]
break;
case "SQL_STORED_PROCEDURE":
scriptableObject = database.StoredProcedures[so.object_name, so.schema_name];
break;
case "USER_TABLE":
scriptableObject = database.Tables[so.object_name, so.schema_name];
break;
case "SQL_INLINE_TABLE_VALUED_FUNCTION":
case "SQL_SCALAR_FUNCTION":
case "SQL_TABLE_VALUED_FUNCTION":
scriptableObject = database.UserDefinedFunctions[so.object_name, so.schema_name];
break;
case "SQL_TRIGGER":
scriptableObject = database.Tables[so.parent_object_name, so.parent_object_schema_name].Triggers[so.object_name];
break;
case "VIEW":
scriptableObject = database.Views[so.object_name, so.schema_name];
break;
default:
throw new InvalidOperationException(String.Format("Unknown DB object type of «{0}» was encountered. " +
"I don't know what to do with objects of that type.", so.type_desc));
}
sb = new StringBuilder();
if (scriptableObject == null)
{
sb.Append("The specified object was not found on the server.");
}
else
{
sb.AppendFormat(@"-- {0} :: {1}.{2}.{3}{4}", so.server_name, so.db_name, so.schema_name, so.object_name, Environment.NewLine);
sb.AppendFormat(@"-- {0}{1}", so.type_desc, Environment.NewLine);
sb.AppendFormat(@"-- Script Created: {0:yyyy-MM-dd HH\:mm\:ss zz}{1}", DateTimeOffset.Now, Environment.NewLine);
sb.AppendFormat(@"-- Object Created: {0:yyyy-MM-dd HH\:mm\:ss}{1}", so.create_date, Environment.NewLine);
sb.AppendFormat(@"-- Object LastMod: {0:yyyy-MM-dd HH\:mm\:ss}{1}", so.modify_date, Environment.NewLine);
sb.AppendFormat("use {0}{1}go{1}", QuoteName(so.db_name), Environment.NewLine);
// Script the DROP surrounded by multiline comments
ProcessScriptResults(scriptableObject.Script(scriptDropOptions), sb, "/*", "*/");
// now script the actual body of the object
ProcessScriptResults(scriptableObject.Script(scriptCreateOptions), sb);
}
});
return(sb.ToString());
}
}
