private string RetreiveSelectAll(int procId)
{
string strSelectAll = string.Format(@"SELECT distinct
T.name as Ref
FROM
sys.sql_dependencies D
LEFT JOIN sys.objects T
ON D.referenced_major_id = T.object_id
LEFT JOIN sys.objects P
ON D.object_id = P.object_id
LEFT JOIN sys.columns C
ON D.referenced_major_id = C.object_id
AND D.referenced_minor_id = C.column_id
WHERE
1=1
and D.is_select_all = 1
AND P.type = 'P'
and P.object_id = '{0}'
", procId);
string result = string.Empty;
using (SQLQueryHelper sql = new SQLQueryHelper(Connection))
{
IDataReader reader = sql.ExecuteReader(strSelectAll);
while (reader.Read())
{
result += (string)reader.GetValue(0) + ";";
}
reader.Close();
}
return(result);
}
private void RetreiveAllObjects(List <DataTable> result)
{
string str = @"SELECT
o.object_id as ID,
convert(sysname,o.name) as NAME,
P.[rows] as ROW_COUNT
FROM
sys.all_objects o, sys.tables T,sys.partitions P
WHERE
T.object_id = P.object_id
and o.object_id = T.object_id
and o.type ='U'
ORDER BY 2
";
DataTable table = new DataTable("TABLES");
table.Columns.Add("ID", typeof(int));
table.Columns.Add("NAME", typeof(string));
table.Columns.Add("ROW_COUNT", typeof(long));
table.Columns.Add("NAMING", typeof(bool));
table.Columns.Add("IX", typeof(int)); //counting
table.Columns.Add("PK", typeof(int));
table.Columns.Add("FK", typeof(int));
table.Columns.Add("IX_LIST", typeof(string)); //list of object in error
table.Columns.Add("PK_LIST", typeof(string));
table.Columns.Add("FK_LIST", typeof(string));
int id;
string name;
using (SQLQueryHelper sql = new SQLQueryHelper(Connection))
{
IDataReader reader = sql.ExecuteReader(str);
while (reader.Read())
{
DataRow dr = table.NewRow();
id = (int)reader.GetValue(0);
dr["ID"] = id;
name = (string)reader.GetValue(1);
dr["NAME"] = name;
dr["ROW_COUNT"] = (long)reader.GetValue(2);
Match m2 = _TableNameMatch.Match(name);
dr["NAMING"] = m2.Success;
table.Rows.Add(dr);
}
reader.Close();
}
result.Add(table);
}
/// <summary>
/// Retreive the sql code of a given command
/// </summary>
/// <param name="connectionString"></param>
/// <param name="command"></param>
/// <returns></returns>
public string GetCommandText(string connectionString, CommandSchema command)
{
StringBuilder builder = new StringBuilder();
string str = string.Format(_Queries.GetQuery("CommandText"), command.Database.Name, command.Name);
using (SQLQueryHelper query = new SQLQueryHelper(connectionString))
{
SqlDataReader reader = query.ExecuteReader(str);
while (reader.Read())
{
builder.Append(reader.GetString(0));
}
reader.Close();
}
return(builder.ToString());
}
/// <summary>
/// Return the SQL script that compose the given view
/// </summary>
/// <param name="connectionString"></param>
/// <param name="view"></param>
/// <returns></returns>
public string GetViewText(string connectionString, ViewSchema view)
{
StringBuilder builder = new StringBuilder();
string str = string.Format(_Queries.GetQuery("ViewText"), view.Name, view.Database.Name, view.Owner);
using (SQLQueryHelper query = new SQLQueryHelper(connectionString))
{
SqlDataReader reader = query.ExecuteReader(str);
while (reader.Read())
{
builder.Append(reader.GetString(0));
}
reader.Close();
}
return(builder.ToString());
}
private DataTable RetreiveProcedures()
{
string str = @"SELECT
P.object_id,
convert(sysname,P.name) as NAME,
c.Text
FROM
sys.procedures P, sys.syscomments C
where P.object_id = C.id
";
DataTable table = new DataTable("Procedures");
table.Columns.Add("ID", typeof(int));
table.Columns.Add("NAME", typeof(string));
table.Columns.Add("LENGTH", typeof(string));
table.Columns.Add("NAMING", typeof(bool));
table.Columns.Add("CURSOR", typeof(int));
table.Columns.Add("NOT_IN", typeof(int));
table.Columns.Add("NOT_EXIST", typeof(int));
table.Columns.Add("CASE_WHEN", typeof(int));
table.Columns.Add("DISTINCT", typeof(int));
table.Columns.Add("GOTO", typeof(int));
table.Columns.Add("COUNT", typeof(int));
table.Columns.Add("SUP_EGAL", typeof(int));
table.Columns.Add("INF_EGAL", typeof(int));
table.Columns.Add("TEMP_TABLE", typeof(int));
table.Columns.Add("TOP", typeof(int));
table.Columns.Add("IN_SELECT", typeof(int));
table.Columns.Add("SELECT_ALL", typeof(string));
int id;
string name;
string content;
using (SQLQueryHelper sql = new SQLQueryHelper(Connection))
{
IDataReader reader = sql.ExecuteReader(str);
while (reader.Read())
{
DataRow dr = table.NewRow();
id = (int)reader.GetValue(0);
dr["ID"] = id;
name = (string)reader.GetValue(1);
dr["NAME"] = name;
content = (string)reader.GetValue(2);
dr["LENGTH"] = content.Length;
dr["COUNT"] = _PerfPatern[0].Match(content).Groups.Count;
dr["CURSOR"] = _PerfPatern[1].Matches(content).Count;
dr["NOT_EXIST"] = _PerfPatern[2].Matches(content).Count;
dr["DISTINCT"] = _PerfPatern[3].Matches(content).Count;
dr["NOT_IN"] = _PerfPatern[4].Matches(content).Count;
dr["CASE_WHEN"] = _PerfPatern[5].Matches(content).Count;
dr["GOTO"] = _PerfPatern[6].Matches(content).Count;
dr["SUP_EGAL"] = _PerfPatern[7].Matches(content).Count;
dr["INF_EGAL"] = _PerfPatern[8].Matches(content).Count;
dr["TEMP_TABLE"] = _PerfPatern[10].Matches(content).Count;
dr["TOP"] = _PerfPatern[9].Match(content).Groups.Count;
dr["IN_SELECT"] = _PerfPatern[11].Matches(content).Count;
Match m2 = _ProcNameMatch.Match(name);
dr["NAMING"] = m2.Success;
dr["SELECT_ALL"] = RetreiveSelectAll(id);
table.Rows.Add(dr);
}
reader.Close();
}
return(table);
}
