private DataTable FetchParentRelations(string tableName)
{
//SELECT fk.CONSTRAINT_NAME, fk.COLUMN_NAME AS CHILD_COLUMN, fk.REFERENCED_TABLE_NAME AS PARENT_TABLE, fk.REFERENCED_COLUMN_NAME AS PARENT_COLUMN
//FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as fk
//WHERE fk.REFERENCED_TABLE_NAME is not null
// and fk.TABLE_NAME = 'employeeterritories'
// AND fk.TABLE_SCHEMA = 'northwind'
DbParameterCollection args = new DbParameterCollection();
StringBuilder select = new StringBuilder();
select.AppendLine("SELECT fk.CONSTRAINT_NAME, fk.COLUMN_NAME AS CHILD_COLUMN, fk.REFERENCED_TABLE_NAME AS PARENT_TABLE, fk.REFERENCED_COLUMN_NAME AS PARENT_COLUMN ");
select.AppendLine("FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as fk ");
select.AppendLine("WHERE fk.REFERENCED_TABLE_NAME is not null ");
select.AppendLine(" AND fk.TABLE_NAME = @tableName ");
args.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
if (!string.IsNullOrEmpty(this.Catalog))
{
select.AppendLine(" AND fk.TABLE_SCHEMA = @schema ");
args.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
}
else
{
select.AppendLine("AND fk.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema') ");
}
DataTable parentData = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), args, CommandType.Text, null, 30);
RemoveExcludedFieldsData(tableName, "COLUMN_NAME", parentData);
return parentData;
}
private DataTable FetchColumnData(string tableName)
{
DbParameterCollection args = new DbParameterCollection();
StringBuilder select = new StringBuilder();
select.AppendLine("SELECT INFORMATION_SCHEMA.COLUMNS.*, 0 AS IsComputed, CHARACTER_MAXIMUM_LENGTH AS ColumnLength, ");
select.AppendLine("case extra when 'auto_increment' then 1 else 0 end AS IsIdentity, ");
select.AppendLine("0 AS IsRowGuidColumn, 0 AS IsPrimaryKey, 0 AS IsForeignKey, 0 AS HasUniqueConstraint, ");
select.AppendLine("null as SequenceName ");
select.AppendLine("FROM INFORMATION_SCHEMA.COLUMNS ");
select.AppendLine("WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @tableName ");
args.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
if (!string.IsNullOrEmpty(this.Catalog))
{
select.AppendLine("AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = @schema ");
args.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
}
else
{
select.AppendLine("AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema') ");
}
select.AppendLine("ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION ");
DataTable fieldsData = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), args, CommandType.Text, null, 30);
RemoveExcludedFieldsData(tableName, "COLUMN_NAME", fieldsData);
return fieldsData;
}
private DataTable FetchConstraintData(string tableName)
{
DbParameterCollection args = new DbParameterCollection();
StringBuilder select = new StringBuilder();
select.AppendLine("SELECT INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME, CONSTRAINT_TYPE ");
select.AppendLine("FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ");
select.AppendLine("INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON ");
select.AppendLine(" INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME ");
select.AppendLine(" AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME ");
select.AppendLine("WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = @tableName ");
args.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
if (!string.IsNullOrEmpty(this.Catalog))
{
select.AppendLine("AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_SCHEMA = @schema ");
args.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
}
else
{
select.AppendLine("AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema') ");
}
DataTable constraintData = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), args, CommandType.Text, null, 30);
return constraintData;
}
/// <summary>Fetches list with basic table and view info.</summary>
public IEnumerable<SqlObjectHeader> FetchTablesAndViews()
{
DbParameterCollection parameters = new DbParameterCollection();
StringBuilder select = new StringBuilder();
select.AppendLine("SELECT TABLE_NAME as ObjectName, CASE TABLE_TYPE WHEN 'BASE TABLE' THEN 0 WHEN 'VIEW' THEN 1 END AS ObjectType ");
select.AppendLine("FROM INFORMATION_SCHEMA.TABLES ");
if (!string.IsNullOrEmpty(this.Catalog))
{
select.AppendLine("WHERE TABLE_SCHEMA = @schema ");
parameters.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
}
else
{
select.AppendLine("WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema') ");
}
select.AppendLine("ORDER BY TABLE_TYPE ASC, TABLE_NAME ASC ");
DataTable tablesAndViews = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), parameters, CommandType.Text, null, 30);
List<SqlObjectHeader> list = new List<SqlObjectHeader>();
foreach (DataRow row in tablesAndViews.Rows)
list.Add(new SqlObjectHeader((string)row["ObjectName"], (SqlObjectType)Convert.ToInt32(row["ObjectType"])));
return list;
}