private void DropReferencedStoredProcedures(string schema, string name)
{
string query = $@"
SELECT
OBJECT_NAME(referencing_id) AS Name,
OBJECT_SCHEMA_NAME(referencing_id) as SchemaName
FROM
sys.sql_expression_dependencies
WHERE
referenced_entity_name = '{name}'
AND referenced_schema_name = '{schema}'
AND referenced_class_desc = 'TYPE'" ;
List <string> dropStatements = new List <string>();
using var connection = CreateConnection();
using DbCommand command = CreateRawCommand(connection, query);
using IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string name1, schema1;
name1 = DataReaderUtility.GetValue <string>(reader, "Name");
schema1 = DataReaderUtility.GetValue <string>(reader, "SchemaName");
dropStatements.Add($"DROP {(name1.StartsWith("f") ? "FUNCTION" : "PROCEDURE")} [{schema1}].[{name1}];");
}
dropStatements.ForEach(o =>
{
_task._logProcessor.Log(Source, " " + o);
//Console.WriteLine(" " + o);
ExecuteNonQuery(o);
});
}
public StoredProcedure(IDataReader reader)
{
Schema = DataReaderUtility.GetValue <string>(reader, "Schema");
SchemaStoredProcedureName = DataReaderUtility.GetValue <string>(reader, "SchemaStoredProcedureName");
StoredProcedureName = DataReaderUtility.GetValue <string>(reader, "StoredProcedureName");
FullText = DataReaderUtility.GetValue <string>(reader, "FullText");
CreatedDate = DataReaderUtility.GetValue <DateTime>(reader, "CreatedDate");
LastAlteredDate = DataReaderUtility.GetValue <DateTime>(reader, "LastAlteredDate");
}
private void LoadExistingStoredProcedures()
{
using var connection = CreateConnection();
using var command = CreateRawCommand(connection, ListAllStoredProceduresQuery);
using IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
StoredProcedure entity = new StoredProcedure();
entity.Schema = DataReaderUtility.GetValue <string>(reader, "Schema");
entity.SchemaStoredProcedureName = DataReaderUtility.GetValue <string>(reader, "SchemaStoredProcedureName");
entity.StoredProcedureName = DataReaderUtility.GetValue <string>(reader, "StoredProcedureName");
entity.CreatedDate = DataReaderUtility.GetValue <DateTime>(reader, "CreatedDate");
entity.LastAlteredDate = DataReaderUtility.GetValue <DateTime>(reader, "LastAlteredDate");
_existingStoredProcedures.Add(entity);
}
}
private DatabaseObjectHash[] ReadObjectHashes(IDataReader reader)
{
List <DatabaseObjectHash> entities = new List <DatabaseObjectHash>();
while (reader.Read())
{
entities.Add(new DatabaseObjectHash
{
SchemaName = DataReaderUtility.GetValue <string>(reader, "SchemaName"),
ObjectName = DataReaderUtility.GetValue <string>(reader, "ObjectName"),
ObjectType = DataReaderUtility.GetValue <string>(reader, "ObjectType"),
ObjectHash = DataReaderUtility.GetValue <string>(reader, "ObjectHash"),
CreatedDate = DataReaderUtility.GetValue <DateTime>(reader, "CreatedDate"),
LastUpdated = DataReaderUtility.GetValue <DateTime>(reader, "LastUpdated"),
FileName = DataReaderUtility.GetValue <string>(reader, "FileName")
});
}
return(entities.ToArray());
}
public UserType(IDataReader reader)
{
Schema = DataReaderUtility.GetValue <string>(reader, "Schema");
Name = DataReaderUtility.GetValue <string>(reader, "Name");
}
public ChangeScript(IDataReader reader)
{
ChangeNumber = DataReaderUtility.GetValue <int>(reader, "ChangeNumber");
DeltaSet = DataReaderUtility.GetValue <string>(reader, "DeltaSet");
}