public void LoadSchema()
{
Tables.Clear();
StoredProcedures.Clear();
var selectTables = conn.CreateCommand();
selectTables.CommandText = "select * from INFORMATION_SCHEMA.TABLES;";
using (var reader = selectTables.ExecuteReader())
{
while (reader.Read())
{
Tables.Add(new NbuLibrary.Core.Sql.Table(reader));
}
}
var selectProcs = conn.CreateCommand();
selectProcs.CommandText = string.Format("select * from INFORMATION_SCHEMA.ROUTINES");
using (var reader = selectProcs.ExecuteReader())
{
while (reader.Read())
{
StoredProcedures.Add(new NbuLibrary.Core.Sql.StoredProcedure(reader));
}
}
foreach (var table in Tables)
{
var selectCols = conn.CreateCommand();
selectCols.CommandText = string.Format(@"
select cc.definition, ic.* from INFORMATION_SCHEMA.COLUMNS ic
INNER JOIN sys.columns c ON ic.COLUMN_NAME = c.name AND c.object_id = OBJECT_ID('{0}')
LEFT JOIN sys.computed_columns cc ON c.column_id = cc.column_id
WHERE TABLE_NAME = '{0}'", table.Name);
using (var reader = selectCols.ExecuteReader())
{
while (reader.Read())
{
var col = new NbuLibrary.Core.Sql.Column(reader);
table.Columns.Add(col);
if (!reader.IsDBNull(reader.GetOrdinal(Consts.COLUMN_DEFAULT)))
table.Constraints.Add(new DefaultConstraint(table.Name, col.Name, reader[Consts.COLUMN_DEFAULT] as string));
}
}
var selectConstraints = conn.CreateCommand();
selectConstraints.CommandText = string.Format("SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = '{0}';", table.Name);
using (var reader = selectConstraints.ExecuteReader())
{
while (reader.Read())
{
if ((string)reader[Consts.CONSTRAINT_TYPE] == Constraint.FOREIGN_KEY)
{
table.Constraints.Add(new NbuLibrary.Core.Sql.ForeignKeyConstraint(reader));
}
else
table.Constraints.Add(new NbuLibrary.Core.Sql.Constraint(reader));
}
}
foreach (var con in table.Constraints)
{
var selectConstraintColumns = conn.CreateCommand();
selectConstraintColumns.CommandText = string.Format("SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE {0}='{1}';", NbuLibrary.Core.Sql.Consts.CONSTRAINT_NAME, con.Name);
using (var reader = selectConstraintColumns.ExecuteReader())
{
while (reader.Read())
{
con.Columns.Add((string)reader[NbuLibrary.Core.Sql.Consts.COLUMN_NAME]);
}
}
if (con is ForeignKeyConstraint)
{
var fk = con as ForeignKeyConstraint;
var selectRefCols = conn.CreateCommand();
selectRefCols.CommandText = string.Format(@"select cu.COLUMN_NAME, cu.TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu ON rc.UNIQUE_CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE rc.CONSTRAINT_NAME = '{0}';", fk.Name);
using (var reader = selectRefCols.ExecuteReader())
{
while (reader.Read())
{
if (string.IsNullOrEmpty(fk.RefTable))
fk.RefTable = (string)reader[Consts.TABLE_NAME];
fk.RefColumns.Add((string)reader[Consts.COLUMN_NAME]);
}
}
}
}
var selectUniqueIndexes = conn.CreateCommand();
selectUniqueIndexes.CommandText = string.Format(@"
select name, filter_definition, index_id from sys.indexes
where object_id = OBJECT_ID('[dbo].[{0}]') and is_unique=1 and is_primary_key = 0", table.Name);
List<object[]> idxs = new List<object[]>();
using (var reader = selectUniqueIndexes.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
object[] idx = new object[3];
reader.GetValues(idx);
idxs.Add(idx);
}
}
foreach (var idx in idxs)
{
List<string> cols = new List<string>();
var selectIdxCols = conn.CreateCommand();
selectIdxCols.CommandText = string.Format(@"
select c.name
from sys.index_columns ic
inner join sys.columns c on c.column_id = ic.column_id and c.object_id = ic.object_id
where ic.index_id={0} and ic.object_id = OBJECT_ID('[dbo].[{1}]') ", idx[2], table.Name);
using (var reader = selectIdxCols.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
cols.Add(reader.GetString(0));
}
}
table.Constraints.Add(new UniqueConstraint(table.Name, cols.ToArray()) { Filter = idx[1] as string });
}
}
}
public void LoadSchema()
{
Tables.Clear();
StoredProcedures.Clear();
var selectTables = conn.CreateCommand();
selectTables.CommandText = "select * from INFORMATION_SCHEMA.TABLES;";
using (var reader = selectTables.ExecuteReader())
{
while (reader.Read())
{
Tables.Add(new NbuLibrary.Core.Sql.Table(reader));
}
}
var selectProcs = conn.CreateCommand();
selectProcs.CommandText = string.Format("select * from INFORMATION_SCHEMA.ROUTINES");
using (var reader = selectProcs.ExecuteReader())
{
while (reader.Read())
{
StoredProcedures.Add(new NbuLibrary.Core.Sql.StoredProcedure(reader));
}
}
foreach (var table in Tables)
{
var selectCols = conn.CreateCommand();
selectCols.CommandText = string.Format(@"
select cc.definition, ic.* from INFORMATION_SCHEMA.COLUMNS ic
INNER JOIN sys.columns c ON ic.COLUMN_NAME = c.name AND c.object_id = OBJECT_ID('{0}')
LEFT JOIN sys.computed_columns cc ON c.column_id = cc.column_id
WHERE TABLE_NAME = '{0}'", table.Name);
using (var reader = selectCols.ExecuteReader())
{
while (reader.Read())
{
var col = new NbuLibrary.Core.Sql.Column(reader);
table.Columns.Add(col);
if (!reader.IsDBNull(reader.GetOrdinal(Consts.COLUMN_DEFAULT)))
table.Constraints.Add(new DefaultConstraint(table.Name, col.Name, reader[Consts.COLUMN_DEFAULT] as string));
}
}
var selectConstraints = conn.CreateCommand();
selectConstraints.CommandText = string.Format("SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = '{0}';", table.Name);
using (var reader = selectConstraints.ExecuteReader())
{
while (reader.Read())
{
if ((string)reader[Consts.CONSTRAINT_TYPE] == Constraint.FOREIGN_KEY)
{
table.Constraints.Add(new NbuLibrary.Core.Sql.ForeignKeyConstraint(reader));
}
else
table.Constraints.Add(new NbuLibrary.Core.Sql.Constraint(reader));
}
}
foreach (var con in table.Constraints)
{
var selectConstraintColumns = conn.CreateCommand();
selectConstraintColumns.CommandText = string.Format("SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE {0}='{1}';", NbuLibrary.Core.Sql.Consts.CONSTRAINT_NAME, con.Name);
using (var reader = selectConstraintColumns.ExecuteReader())
{
while (reader.Read())
{
con.Columns.Add((string)reader[NbuLibrary.Core.Sql.Consts.COLUMN_NAME]);
}
}
if (con is ForeignKeyConstraint)
{
var fk = con as ForeignKeyConstraint;
var selectRefCols = conn.CreateCommand();
selectRefCols.CommandText = string.Format(@"select cu.COLUMN_NAME, cu.TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu ON rc.UNIQUE_CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE rc.CONSTRAINT_NAME = '{0}';", fk.Name);
using (var reader = selectRefCols.ExecuteReader())
{
while (reader.Read())
{
if (string.IsNullOrEmpty(fk.RefTable))
fk.RefTable = (string)reader[Consts.TABLE_NAME];
fk.RefColumns.Add((string)reader[Consts.COLUMN_NAME]);
}
}
}
}
var selectUniqueIndexes = conn.CreateCommand();
selectUniqueIndexes.CommandText = string.Format(@"
select name, filter_definition, index_id from sys.indexes
where object_id = OBJECT_ID('[dbo].[{0}]') and is_unique=1 and is_primary_key = 0", table.Name);
List<object[]> idxs = new List<object[]>();
using (var reader = selectUniqueIndexes.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
object[] idx = new object[3];
reader.GetValues(idx);
idxs.Add(idx);
}
}
foreach (var idx in idxs)
{
List<string> cols = new List<string>();
var selectIdxCols = conn.CreateCommand();
selectIdxCols.CommandText = string.Format(@"
select c.name
from sys.index_columns ic
inner join sys.columns c on c.column_id = ic.column_id and c.object_id = ic.object_id
where ic.index_id={0} and ic.object_id = OBJECT_ID('[dbo].[{1}]') ", idx[2], table.Name);
using (var reader = selectIdxCols.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
cols.Add(reader.GetString(0));
}
}
table.Constraints.Add(new UniqueConstraint(table.Name, cols.ToArray()) { Filter = idx[1] as string });
}
}
}
