public List <DependentData> FindDependencies(string dependentName)
{
string schemaName = "dbo";
string tableName = dependentName;
if (dependentName.Contains("."))
{
schemaName = dependentName.Split('.')[0];
tableName = dependentName.Split('.')[1];
}
List <DependentData> retVal = new List <DependentData>();
//First we run this to determine type of object.
string ctext = string.Format(@"
select sch_do.name + '.' + do.name as Name, do.type as Type,1
from sys.objects as o
join sys.objects as do on do.object_id = o.object_id
join sys.schemas as sch_o on sch_o.schema_id = o.schema_id
join sys.schemas as sch_do on sch_do.schema_id = do.schema_id
where
o.name = '{0}' and sch_o.name = '{1}' and do.name = '{0}' and sch_o.name = '{1}'
union
select sch_do.name + '.' + do.name as Name, do.type as Type,2
from sys.objects as o
join sys.sql_expression_dependencies as d on d.referencing_id = o.object_id
join sys.objects as do on do.name = d.referenced_entity_name
join sys.schemas as sch_o on sch_o.schema_id = o.schema_id
join sys.schemas as sch_do on sch_do.schema_id = do.schema_id
where
(o.name = '{0}' and sch_o.name = '{1}') and(do.name != '{0}' or sch_o.name != '{1}')
union
select sch_do.name + '.' + do.name as Name, do.type as Type,3
from sys.objects as o
join sys.triggers as d on d.parent_id = o.object_id
join sys.objects as do on do.object_id = d.object_id
join sys.schemas as sch_o on sch_o.schema_id = o.schema_id
join sys.schemas as sch_do on sch_do.schema_id = do.schema_id
where
(o.name = '{0}' and sch_o.name = '{1}') and(do.name != '{0}' or sch_o.name != '{1}') order by 3", tableName, schemaName);
_cmd.CommandText = ctext;
_cmd.Connection.Open();
string objectname = string.Empty;
string objecttype = string.Empty;
DependentData currentItem = null;
bool firstRow = true;
//first row will return the type of the selected object and set as visited.
try
{
_reader = _cmd.ExecuteReader();
if (_reader.HasRows)
{
while (_reader.Read())
{
objectname = _reader.GetSqlString(0).Value;
objecttype = _reader.GetSqlString(1).Value;
if (firstRow)
{
firstRow = false;
currentItem = DependentsList.FirstOrDefault(w => w.dependentName == dependentName.Trim() && w.dependentType == objecttype.Trim());
if (currentItem != null)
{
//update the current item, setting the isvisited to true.
//This can happen if it was loaded as part of another call
currentItem.isVisited = true;
}
else if (!DependentsList.Any(a => a.dependentName == objectname && a.dependentType == objecttype))
{
currentItem = new DependentData()
{
dependentName = objectname, dependentType = objecttype, isVisited = true
};
DependentsList.Add(currentItem);
}
}
//return current set of dependencies so we can display on bottom.
retVal.Add(new DependentData()
{
dependentName = objectname, dependentType = objecttype, isVisited = DependentsList.Any(a => a.dependentName == objectname && a.dependentType == objecttype)
});
if (!DependentsList.Any(a => a.dependentName == objectname && a.dependentType == objecttype))
{
DependentsList.Add(new DependentData()
{
dependentName = objectname, dependentType = objecttype, isVisited = false
});
}
}
}
}
catch (Exception ex)
{
var t = ex;
}
finally
{
if (_reader != null)
{
_reader.Close();
}
_cmd.Connection.Close();
}
return(retVal);
}
public void Read(string spName)
{
Helpers.CurrProcName = spName;
Console.WriteLine(spName);
DDbo._cmd = _cmd;
RDbo.sqlCommand = _cmd;
List <DependentData> currDependencies = DDbo.FindDependencies(spName.Trim());
DependentData currDependency = currDependencies.First();
DBText = RDbo.GetDBText(currDependency.dependentName, currDependency.dependentType, currDependencies.Skip(1).ToList());
DBText.AppendLine("");
if (currDependencies.Count > 1)
{
DBText.AppendLine("-------------- Dependent Objects --------------");
DBText.AppendLine("");
foreach (DependentData item in currDependencies.Skip(1))
{
if (_outputtype.ToLower() == "htm")
{
//we can add a link, otherwise leave
DBText.AppendLine(string.Format("Object " + Helpers.AnchorHelper("<a href='{0}.htm' >{0}</a>, Type {1}", item.dependentName, item.dependentType)));
}
else
{
DBText.AppendLine(string.Format("Object {0}, Type {1}", item.dependentName, item.dependentType));
}
}
}
if (_outputtype.ToLower() == "htm")
{
//Write to the template file
Helpers.CurrProcText = DBText.ToString();
PageHTM template = new PageHTM();
string pageContent = template.TransformText();
File.WriteAllText(string.Format("{0}.htm", spName), pageContent);
}
else
{
File.WriteAllText(string.Format("{0}.txt", spName), DBText.ToString());
}
//Lets see if we have more work to do
foreach (var procedure in currDependencies.Skip(1))
{
//only visit once
if (!procedure.isVisited)
{
Read(procedure.dependentName);
}
}
}
