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);
}
/// <summary>
/// Return all primary keys for a given table
/// </summary>
/// <param name="connectionString"></param>
/// <param name="table"></param>
/// <returns></returns>
public PrimaryKeySchema GetTablePrimaryKey(string connectionString, TableSchema table)
{
string commandText = string.Format(_Queries.GetQuery("TablePrimaryKey"), table.Name, table.Database.Name, table.Owner);
DataTable table2 = new SQLQueryHelper(connectionString).ExecuteData(commandText);
if (table2.Rows.Count == 1)
{
string primary = (string)table2.Rows[0]["CONSTRAINT_NAME"];
string commandText1 = string.Format(_Queries.GetQuery("TablePrimaryKeyColumns"), table.Database.Name, table.Name, primary);
DataTable table3 = new SQLQueryHelper(connectionString).ExecuteData(commandText1);
List <string> memberColumns = new List <string>(table3.Rows.Count);
for (int i = 0; i < table3.Rows.Count; i++)
{
memberColumns.Add((string)table3.Rows[i]["COLUMN_NAME"]);
}
return(new PrimaryKeySchema(table, primary, memberColumns));
}
else
{
return(null);
}
}
/// <summary>
/// Return all keys for a given table
/// </summary>
/// <param name="connectionString"></param>
/// <param name="table"></param>
/// <returns></returns>
public List <TableKeySchema> GetTableKeys(string connectionString, TableSchema table)
{
string commandText = string.Format(_Queries.GetQuery("TableKeys"), table.Owner, table.Name);
DataTable table2 = new SQLQueryHelper(connectionString).ExecuteData(commandText);
List <TableKeySchema> schemaArray = new List <TableKeySchema>();
for (int i = 0; i < table2.Rows.Count; i++)
{
string keyName = (string)table2.Rows[i]["ForeignKeyName"];
string foreignKeyTable = (string)table2.Rows[i]["TableName"];
List <string> foreignKeyColumnNameList = new List <string>();
foreignKeyColumnNameList.Add((string)table2.Rows[i]["ColumnName"]);
string primaryKeyTable = (string)table2.Rows[i]["ReferenceTableName"];
List <string> primaryKeyTableColumnNameList = new List <string>();
primaryKeyTableColumnNameList.Add((string)table2.Rows[i]["ReferenceColumnName"]);
schemaArray.Add(new TableKeySchema(table.Database, keyName,
foreignKeyColumnNameList.ToArray(), foreignKeyTable, primaryKeyTableColumnNameList.ToArray(), primaryKeyTable));
}
return(schemaArray);
}
/// <summary>
/// Return all indexes for given table
/// </summary>
/// <param name="connectionString"></param>
/// <param name="table"></param>
/// <returns></returns>
public List <IndexSchema> GetTableIndexes(string connectionString, TableSchema table)
{
DataTable tableIndex = new SchemaHelper().GetIndexes(connectionString, table.Database.Name, table.Owner, table.Name, null);
List <IndexSchema> list = new List <IndexSchema>(tableIndex.Rows.Count);
for (int i = 0; i < tableIndex.Rows.Count; i++)
{
string name = (string)tableIndex.Rows[i]["index_name"];
DataTable tableCol = new SchemaHelper().GetIndexColumns(connectionString, table.Database.Name, table.Owner, table.Name, name, null);
List <string> listRelatedCol = new List <string>();
for (int j = 0; j < tableCol.Rows.Count; j++)
{
listRelatedCol.Add((string)tableCol.Rows[j]["column_name"]);
}
string commandText = string.Format(_Queries.GetQuery("IndexExtended"), name);
DataTable tableSys = new SQLQueryHelper(connectionString).ExecuteData(commandText);
bool isPrimaryKey = (bool)tableSys.Rows[0]["is_primary_key"];
bool isUnique = (bool)tableSys.Rows[0]["is_unique"];
bool isClustered = (bool)((string)tableSys.Rows[0]["type_desc"] == "CLUSTERED" ? true : false);
list.Add(new IndexSchema(table, name, isPrimaryKey, isUnique, isClustered, listRelatedCol));
}
return(list);
}
/// <summary>
/// Return all tables for the given DatabaseSchema object
/// </summary>
/// <param name="connectionString"></param>
/// <param name="database">DatabaseSchema</param>
/// <returns></returns>
public List <TableSchema> GetTables(string connectionString, DatabaseSchema database)
{
string str = string.Format(_Queries.GetQuery("TableSchema"), database.Name);
DataTable table = new SQLQueryHelper(connectionString).ExecuteData(str);
List <TableSchema> schemaArray = new List <TableSchema>(table.Rows.Count);
for (int i = 0; i < table.Rows.Count; i++)
{
List <ExtendedProperty> list = null;
string strext = string.Format(_Queries.GetQuery("ExtendedProperty"), (string)table.Rows[i][0]);
DataTable dtEx = new SQLQueryHelper(connectionString).ExecuteData(strext);
if (dtEx.Rows.Count == 1)
{
list = new List <ExtendedProperty>();
list.Add(new ExtendedProperty("Description", (string)dtEx.Rows[0]["value"], DbType.String));
}
else
{
list = null;
}
schemaArray.Add(new TableSchema(database, (string)table.Rows[i][0], (string)table.Rows[i][1], (DateTime)table.Rows[i][2], list));
}
return(schemaArray);
}
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>
/// Return all views in a given databse
/// </summary>
/// <param name="connectionString"></param>
/// <param name="database"></param>
/// <returns></returns>
public List <ViewSchema> GetViews(string connectionString, DatabaseSchema database)
{
string str = string.Format(_Queries.GetQuery("ViewSchema"), database.Name);
DataTable table = new SQLQueryHelper(connectionString).ExecuteData(str);
List <ViewSchema> schemaArray = new List <ViewSchema>(table.Rows.Count);
for (int i = 0; i < table.Rows.Count; i++)
{
schemaArray.Add(new ViewSchema(database, (string)table.Rows[i][0], (string)table.Rows[i][1], (DateTime)table.Rows[i][2]));
}
return(schemaArray);
}
/// <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);
}