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 }); } } }