/// <summary> /// Removes duplicate table constraints, PK > UK > IX /// </summary> private void NormalizeTablesConstraintKeys(List <DbTable> result, SQLServerVersions sqlVersion) { // look in tables list foreach (DbTable table in result) { if (table.Indexes.Count == 0) { continue; } StringCollection duplicateConstraints = new StringCollection(); // looping the contraints keys for (int j = table.Indexes.Count - 1; j >= 0; j--) { var thisIndex = table.Indexes[j]; // no primary keys are allowed if (thisIndex.Keys.Count > 0 && thisIndex.IsPrimaryKey) { // There is no need in keeping the primary key table.Indexes.RemoveAt(j); continue; } // If this is a single column index, first look in the foreign keys! if (thisIndex.Keys.Count == 1) { int index = table.ForeignKeys.FindIndex(x => x.LocalColumnName == thisIndex.Keys[0].KeyColumnName); // this is a foreign key and should not be here if (index != -1) { table.Indexes.RemoveAt(j); continue; } } //// if this is not a unique key //// seach for a unique one if it is there //if (thisIndex.IsUnique == false) //{ // index = table.Indexes.FindIndex(x => // x.KeyColumnName == thisIndex.KeyColumnName // && x.IsUnique == true); // if (index != -1) // { // // the same and the Unique key is already there! // table.Indexes.RemoveAt(j); // continue; // } //} //else //{ // var notUniqueKeys = table.Indexes.FindAll(x => // x.KeyColumnName == thisIndex.KeyColumnName // && x.IsUnique == false); // if (notUniqueKeys != null && notUniqueKeys.Count > 0) // { // // remove them // notUniqueKeys.ForEach(x => table.Indexes.Remove(x)); // continue; // } //} //// look for duplication constraint key //if (duplicateConstraints.Contains(thisIndex.KeyColumnName)) //{ // // the column with index is already there // table.Indexes.RemoveAt(j); // continue; //} //// all to the constraint key list //duplicateConstraints.Add(thisIndex.KeyColumnName); } } }
/// <summary> /// Reads specified table foreign keys. /// </summary> private void ApplyTablesForeignKeys(List <DbTable> tables, SQLServerVersions sqlServer) { /* * sql 2005 format * SELECT CONVERT(SYSNAME, DB_NAME()) AS PKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O1.schema_id)) AS PKTABLE_OWNER, CONVERT(SYSNAME, * O1.name) AS PKTABLE_NAME, CONVERT(SYSNAME, C1.name) AS PKCOLUMN_NAME, CONVERT(SYSNAME, DB_NAME()) AS FKTABLE_QUALIFIER, * CONVERT(SYSNAME, SCHEMA_NAME(O2.schema_id)) AS FKTABLE_OWNER, CONVERT(SYSNAME, O2.name) AS FKTABLE_NAME, CONVERT(SYSNAME, C2.name) * AS FKCOLUMN_NAME, CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END) AS UPDATE_RULE, * CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END) AS DELETE_RULE, CONVERT(SYSNAME, * OBJECT_NAME(F.object_id)) AS FK_NAME, CONVERT(SYSNAME, I.name) AS PK_NAME, CONVERT(SMALLINT, 7) AS DEFERRABILITY, F.delete_referential_action, * F.update_referential_action * FROM sys.all_objects AS O1 INNER JOIN * sys.foreign_keys AS F INNER JOIN * sys.foreign_key_columns AS K ON K.constraint_object_id = F.object_id INNER JOIN * sys.indexes AS I ON F.referenced_object_id = I.object_id AND F.key_index_id = I.index_id ON O1.object_id = F.referenced_object_id INNER JOIN * sys.all_objects AS O2 ON F.parent_object_id = O2.object_id INNER JOIN * sys.all_columns AS C1 ON F.referenced_object_id = C1.object_id AND K.referenced_column_id = C1.column_id INNER JOIN * sys.all_columns AS C2 ON F.parent_object_id = C2.object_id AND K.parent_column_id = C2.column_id */ // GENERAL command format string foreignKeySql = @"SELECT OBJECT_NAME(f.constid) AS 'ForeignKey', OBJECT_NAME(f.fkeyid) AS 'FKTable', c1.name AS 'FKColumnName', OBJECT_NAME(f.rkeyid) AS 'PKTable', c2.name AS 'PKColumnName' , -1 as update_referential_action, -1 as delete_referential_action FROM sysforeignkeys AS f INNER JOIN syscolumns AS c1 ON f.fkeyid = c1.id AND f.fkey = c1.colid INNER JOIN syscolumns AS c2 ON f.rkeyid = c2.id AND f.rkey = c2.colid ORDER BY 'FKTable', c1.colid "; // NEW command format if (sqlServer > SQLServerVersions.SQL2000) { foreignKeySql = @"SELECT CONVERT(SYSNAME, DB_NAME()) AS PKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O1.schema_id)) AS PKTABLE_OWNER, CONVERT(SYSNAME, O1.name) AS 'PKTable', CONVERT(SYSNAME, C1.name) AS 'PKColumnName', CONVERT(SYSNAME, DB_NAME()) AS FKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O2.schema_id)) AS FKTABLE_OWNER, CONVERT(SYSNAME, O2.name) AS 'FKTable', CONVERT(SYSNAME, C2.name) AS 'FKColumnName', CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END) AS UPDATE_RULE, CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END) AS DELETE_RULE, CONVERT(SYSNAME, OBJECT_NAME(F.object_id)) AS 'ForeignKey', CONVERT(SYSNAME, I.name) AS PK_NAME, CONVERT(SMALLINT, 7) AS DEFERRABILITY, F.delete_referential_action, F.update_referential_action FROM sys.all_objects AS O1 INNER JOIN sys.foreign_keys AS F INNER JOIN sys.foreign_key_columns AS K ON K.constraint_object_id = F.object_id INNER JOIN sys.indexes AS I ON F.referenced_object_id = I.object_id AND F.key_index_id = I.index_id ON O1.object_id = F.referenced_object_id INNER JOIN sys.all_objects AS O2 ON F.parent_object_id = O2.object_id INNER JOIN sys.all_columns AS C1 ON F.referenced_object_id = C1.object_id AND K.referenced_column_id = C1.column_id INNER JOIN sys.all_columns AS C2 ON F.parent_object_id = C2.object_id AND K.parent_column_id = C2.column_id ORDER BY 'FKTable', C2.column_id"; } try { using (var adapter = new SqlDataAdapter(foreignKeySql, (SqlConnection)_dbConnection)) { adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; // description data table using (var keysData = new DataTable()) { // Just to avoid stupid "Failed to enable constraints" error! using (var tempDs = new DataSet()) { // Avoiding stupid "Failed to enable constraints" error! tempDs.EnforceConstraints = false; tempDs.Tables.Add(keysData); // Get from db adapter.Fill(keysData); } if (keysData.Rows.Count > 0) { foreach (DataRow keysDataRow in keysData.Rows) { var foreignKeyTableName = keysDataRow["FKTable"].ToString(); var primaryKeyTableName = keysDataRow["PKTable"].ToString(); var foreignKeyTable = FindTable(tables, foreignKeyTableName); var primaryKeyTable = FindTable(tables, primaryKeyTableName); // one-to-many foreign relation will be added if (primaryKeyTable != null) { // foreign key many end var manyMultiplicityKey_Local = new DbForeignKey() { ForeignKeyName = keysDataRow["ForeignKey"].ToString(), LocalColumnName = keysDataRow["PKColumnName"].ToString(), ForeignColumnName = keysDataRow["FKColumnName"].ToString(), ForeignTableName = keysDataRow["FKTable"].ToString(), Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne }; // check if it is already there if (primaryKeyTable.ForeignKeys.Exists( x => x.Multiplicity == DbForeignKey.ForeignKeyMultiplicity.ManyToOne && x.ForeignKeyName == manyMultiplicityKey_Local.ForeignKeyName)) { continue; } manyMultiplicityKey_Local.UpdateAction = ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString())); manyMultiplicityKey_Local.DeleteAction = ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString())); // to the list primaryKeyTable.ForeignKeys.Add(manyMultiplicityKey_Local); // apply local column DbColumn localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKey_Local.LocalColumnName); manyMultiplicityKey_Local.LocalColumn = localColumn; if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = primaryKeyTable; } if (foreignKeyTable != null) { // foreign table of that! manyMultiplicityKey_Local.ForeignTable = foreignKeyTable; // apply foreign column DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKey_Local.ForeignColumnName); manyMultiplicityKey_Local.ForeignColumn = foreignColumn; } else { manyMultiplicityKey_Local.ForeignTable = null; manyMultiplicityKey_Local.ForeignColumn = null; } } // one-to-? foreign relation will be added if (foreignKeyTable != null) { // foreign key many end var oneMultiplicityKey_Foreign = new DbForeignKey() { ForeignKeyName = keysDataRow["ForeignKey"].ToString(), LocalColumnName = keysDataRow["FKColumnName"].ToString(), ForeignColumnName = keysDataRow["PKColumnName"].ToString(), ForeignTableName = keysDataRow["PKTable"].ToString(), Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany }; // check if it is already there if (foreignKeyTable.ForeignKeys.Exists( x => x.Multiplicity == DbForeignKey.ForeignKeyMultiplicity.OneToMany && x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName)) { continue; } oneMultiplicityKey_Foreign.UpdateAction = ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString())); oneMultiplicityKey_Foreign.DeleteAction = ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString())); // to the list foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey_Foreign); // apply local column DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.LocalColumnName); oneMultiplicityKey_Foreign.LocalColumn = localColumn; if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = primaryKeyTable; } if (primaryKeyTable != null) { // foreign table of that! oneMultiplicityKey_Foreign.ForeignTable = primaryKeyTable; // apply foreign column DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.ForeignColumnName); oneMultiplicityKey_Foreign.ForeignColumn = foreignColumn; } else { oneMultiplicityKey_Foreign.ForeignTable = null; oneMultiplicityKey_Foreign.ForeignColumn = null; } } }// all foreign keys // look for one-to-one situation! } } } } catch { // Seems this version of SQL Server doesn't support this query! // don't stop here! } }
/// <summary> /// Reads tables index keys /// </summary> private void ApplyTablesConstraintKeys(List <DbTable> tables, SQLServerVersions sqlVersion) { // Fail early for versions we don't support if (sqlVersion == SQLServerVersions.SQL2000 || sqlVersion == SQLServerVersions.SQL2000Below) { return; } // Identify indexes and all indexed columns string constraintKeySql = @" -- First query - identify all the indexes in the database SELECT o.name AS TableName , i.index_id , i.object_id , i.name AS IndexName , i.is_unique AS IsUnique , i.is_primary_key AS IsPrimaryKey , i.ignore_dup_key AS IgnoreDuplicateKey , i.is_unique_constraint AS IsUniqueConstraintKey , i.is_disabled AS Disabled FROM sys.objects o INNER JOIN sys.indexes i ON i.object_id = o.object_id WHERE o.is_ms_shipped = 0 AND o.type = 'U' order by i.name; -- Second query - identify all columns for these indexes SELECT i.index_id , i.object_id , c.name as ColumnName , ic.* FROM sys.indexes i INNER JOIN sys.index_columns ic ON ic.object_id = i.object_id and ic.index_id = i.index_id INNER JOIN sys.columns c ON c.object_id = i.object_id AND c.column_id = ic.column_id order by i.name, ic.index_column_id; "; // Load from the database DataSet ds = new DataSet(); using (SqlDataAdapter adapter = new SqlDataAdapter(constraintKeySql, _dbConnection)) { adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; adapter.Fill(ds); } // Okay, now let's look through all the indexes we found foreach (DataRow row in ds.Tables[0].Rows) { // Find the matching table var table = (from t in tables where String.Equals(t.TableName, row["TableName"].ToString()) select t).FirstOrDefault(); if (table == null) { continue; } // What information do we have about this index? var IsUnique = Convert.ToBoolean(row["IsUnique"].ToString()); var IndexName = row["IndexName"].ToString(); var IndexId = (int)row["index_id"]; var ObjectId = (int)row["object_id"]; var IsPrimaryKey = Convert.ToBoolean(row["IsPrimaryKey"].ToString()); // Convert all columns to constraint keys var columns = (from DataRow r2 in ds.Tables[1].Rows where (int)r2["index_id"] == IndexId && (int)r2["object_id"] == ObjectId select new DbConstraintKey() { KeyColumnName = r2["ColumnName"].ToString(), KeyColumn = table.FindColumnDb(r2["ColumnName"].ToString()), }).ToList(); // Add either a simple or a complex key if (columns.Any()) { table.AddIndex(new DbIndex() { Keys = columns, IsUnique = IsUnique, IndexName = IndexName, IsPrimaryKey = IsPrimaryKey }); } } // Finally - scan through each table that doesn't have a primary key and see if we can find one //foreach (var table in tables) { // if (!table.HasPrimaryKey()) { // // We don't currently have a primary key, let's see if we can find one // System.Diagnostics.Debug.WriteLine("Table " + table.TableName + " does not have a primary key - checking indexes"); // var found = (from DataRow dr in ds.Tables[1].Rows where dr["TableName"] == table.TableName && dr["IsPrimaryKey // keysData.DefaultView.RowFilter = " TableName='" + table.TableName + "' AND IsPrimaryKey=1 "; // DataRowView myrow = null; // foreach (DataRowView keysDataRow in keysData.DefaultView) { // System.Diagnostics.Debug.WriteLine("Table " + table.TableName + " should have a PK, it's named " + keysDataRow["ColumnName"].ToString()); // if (myrow != null) { // System.Diagnostics.Debug.WriteLine("Tables with a dual primary key are not supported."); // myrow = null; // break; // } // myrow = keysDataRow; // } // // If we found one single primary key through this means, use it // if (myrow != null) { // foreach (var column in table.SchemaColumns) { // if (String.Equals(column.FieldNameDb, myrow["ColumnName"].ToString(), StringComparison.CurrentCultureIgnoreCase) // && column.DataTypeDotNet != "System.DateTime") { // System.Diagnostics.Debug.WriteLine("Matched row " + column.FieldNameDb + " and was able to assign a primary key."); // column.PrimaryKey = true; // break; // } // } // } // } //} }
/// <summary> /// Reads tables schema from database /// </summary> private List <DbTable> ReadTables(List <DbView> viewList) { List <DbTable> result = new List <DbTable>(); using (DataTable tables = _dbConnection.GetSchema("TABLES")) { foreach (DataRow row in tables.Rows) { string tableName = row["TABLE_NAME"].ToString(); string ownerName = row["TABLE_SCHEMA"].ToString(); if (!IsTableSelected(tableName)) { continue; } bool jumpToNext = false; // search in views about this for (int i = 0; i < viewList.Count; i++) { if (viewList[i].TableName == tableName) { jumpToNext = true; // we ignore view here break; } } if (jumpToNext) { continue; } // View columns List <DbColumn> columns = ReadColumns(tableName, ownerName); // read columns description if (ReadColumnsDescription) { ApplyColumnsDescription(tableName, ownerName, columns); } // new table var dbTable = new DbTable(tableName, columns); dbTable.EscapedTableName = "[" + tableName + "]"; // table schema dbTable.OwnerName = ownerName; // add to results result.Add(dbTable); } // detect the sql server version SQLServerVersions sqlVersion = DetectSqlServerVersion(_dbConnection); if (ReadConstraintKeys) { // The constraint keys will read here ApplyTablesConstraintKeys(result, sqlVersion); } // it is time to read foreign keys! // foreign keys are requested? if (ReadTablesForeignKeys) { ApplyTablesForeignKeys(result, sqlVersion); } // Normalize the constraints keys NormalizeTablesConstraintKeys(result, sqlVersion); if (ReadTablesForeignKeys) { ApplyDetectedOneToOneRelation(result); } } return(result); }
/// <summary> /// Removes duplicate table constraints, PK > UK > IX /// </summary> private void NormalizeTablesConstraintKeys(List<DbTable> result, SQLServerVersions sqlVersion) { // look in tables list foreach (DbTable table in result) { if (table.ConstraintKeys.Count == 0) { continue; } StringCollection duplicateConstraints = new StringCollection(); // fetching the contraints keys for (int j = table.ConstraintKeys.Count - 1; j >= 0; j--) { var constraintKey = table.ConstraintKeys[j]; // no primary keys are allowed if (constraintKey.KeyColumn != null && constraintKey.KeyColumn.PrimaryKey) { // There is no need in keeping the primary key table.ConstraintKeys.RemoveAt(j); continue; } // first look in the foreign keys! int index = table.ForeignKeys.FindIndex(x => x.LocalColumnName == constraintKey.KeyColumnName); if (index != -1) { // this is a foreign key and should not be here table.ConstraintKeys.RemoveAt(j); continue; } // if this is not a unique key // seach for a unique one if it is there if (constraintKey.IsUnique == false) { index = table.ConstraintKeys.FindIndex(x => x.KeyColumnName == constraintKey.KeyColumnName && x.IsUnique == true); if (index != -1) { // the same and the Unique key is already there! table.ConstraintKeys.RemoveAt(j); continue; } } else { var notUniqueKeys = table.ConstraintKeys.FindAll(x => x.KeyColumnName == constraintKey.KeyColumnName && x.IsUnique == false); if (notUniqueKeys != null && notUniqueKeys.Count > 0) { // remove them notUniqueKeys.ForEach(x => table.ConstraintKeys.Remove(x)); continue; } } // look for duplication constraint key if (duplicateConstraints.Contains(constraintKey.KeyColumnName)) { // the column with index is already there table.ConstraintKeys.RemoveAt(j); continue; } // all to the constraint key list duplicateConstraints.Add(constraintKey.KeyColumnName); } } }
/// <summary> /// Reads specified table foreign keys. /// </summary> private void ApplyTablesForeignKeys(List<DbTable> tables, SQLServerVersions sqlServer) { /* * sql 2005 format SELECT CONVERT(SYSNAME, DB_NAME()) AS PKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O1.schema_id)) AS PKTABLE_OWNER, CONVERT(SYSNAME, O1.name) AS PKTABLE_NAME, CONVERT(SYSNAME, C1.name) AS PKCOLUMN_NAME, CONVERT(SYSNAME, DB_NAME()) AS FKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O2.schema_id)) AS FKTABLE_OWNER, CONVERT(SYSNAME, O2.name) AS FKTABLE_NAME, CONVERT(SYSNAME, C2.name) AS FKCOLUMN_NAME, CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END) AS UPDATE_RULE, CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END) AS DELETE_RULE, CONVERT(SYSNAME, OBJECT_NAME(F.object_id)) AS FK_NAME, CONVERT(SYSNAME, I.name) AS PK_NAME, CONVERT(SMALLINT, 7) AS DEFERRABILITY, F.delete_referential_action, F.update_referential_action FROM sys.all_objects AS O1 INNER JOIN sys.foreign_keys AS F INNER JOIN sys.foreign_key_columns AS K ON K.constraint_object_id = F.object_id INNER JOIN sys.indexes AS I ON F.referenced_object_id = I.object_id AND F.key_index_id = I.index_id ON O1.object_id = F.referenced_object_id INNER JOIN sys.all_objects AS O2 ON F.parent_object_id = O2.object_id INNER JOIN sys.all_columns AS C1 ON F.referenced_object_id = C1.object_id AND K.referenced_column_id = C1.column_id INNER JOIN sys.all_columns AS C2 ON F.parent_object_id = C2.object_id AND K.parent_column_id = C2.column_id */ // GENERAL command format string foreignKeySql = @"SELECT OBJECT_NAME(f.constid) AS 'ForeignKey', OBJECT_NAME(f.fkeyid) AS 'FKTable', c1.name AS 'FKColumnName', OBJECT_NAME(f.rkeyid) AS 'PKTable', c2.name AS 'PKColumnName' , -1 as update_referential_action, -1 as delete_referential_action FROM sysforeignkeys AS f INNER JOIN syscolumns AS c1 ON f.fkeyid = c1.id AND f.fkey = c1.colid INNER JOIN syscolumns AS c2 ON f.rkeyid = c2.id AND f.rkey = c2.colid ORDER BY c1.colid "; // NEW command format if (sqlServer > SQLServerVersions.SQL2000) { foreignKeySql = @"SELECT CONVERT(SYSNAME, DB_NAME()) AS PKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O1.schema_id)) AS PKTABLE_OWNER, CONVERT(SYSNAME, O1.name) AS 'PKTable', CONVERT(SYSNAME, C1.name) AS 'PKColumnName', CONVERT(SYSNAME, DB_NAME()) AS FKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O2.schema_id)) AS FKTABLE_OWNER, CONVERT(SYSNAME, O2.name) AS 'FKTable', CONVERT(SYSNAME, C2.name) AS 'FKColumnName', CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END) AS UPDATE_RULE, CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END) AS DELETE_RULE, CONVERT(SYSNAME, OBJECT_NAME(F.object_id)) AS 'ForeignKey', CONVERT(SYSNAME, I.name) AS PK_NAME, CONVERT(SMALLINT, 7) AS DEFERRABILITY, F.delete_referential_action, F.update_referential_action FROM sys.all_objects AS O1 INNER JOIN sys.foreign_keys AS F INNER JOIN sys.foreign_key_columns AS K ON K.constraint_object_id = F.object_id INNER JOIN sys.indexes AS I ON F.referenced_object_id = I.object_id AND F.key_index_id = I.index_id ON O1.object_id = F.referenced_object_id INNER JOIN sys.all_objects AS O2 ON F.parent_object_id = O2.object_id INNER JOIN sys.all_columns AS C1 ON F.referenced_object_id = C1.object_id AND K.referenced_column_id = C1.column_id INNER JOIN sys.all_columns AS C2 ON F.parent_object_id = C2.object_id AND K.parent_column_id = C2.column_id ORDER BY C2.column_id"; } try { using (var adapter = new SqlDataAdapter(foreignKeySql, (SqlConnection)_dbConnection)) { adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; // description data table using (var keysData = new DataTable()) { // Just to avoid stupid "Failed to enable constraints" error! using (var tempDs = new DataSet()) { // Avoiding stupid "Failed to enable constraints" error! tempDs.EnforceConstraints = false; tempDs.Tables.Add(keysData); // Get from db adapter.Fill(keysData); } if (keysData.Rows.Count > 0) { foreach (DataRow keysDataRow in keysData.Rows) { var foreignKeyTableName = keysDataRow["FKTable"].ToString(); var primaryKeyTableName = keysDataRow["PKTable"].ToString(); var foreignKeyTable = FindTable(tables, foreignKeyTableName); var primaryKeyTable = FindTable(tables, primaryKeyTableName); // one-to-many foreign relation will be added if (primaryKeyTable != null) { // foreign key many end var manyMultiplicityKey_Local = new DbForeignKey() { ForeignKeyName = keysDataRow["ForeignKey"].ToString(), LocalColumnName = keysDataRow["PKColumnName"].ToString(), ForeignColumnName = keysDataRow["FKColumnName"].ToString(), ForeignTableName = keysDataRow["FKTable"].ToString(), Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne }; // check if it is already there if (primaryKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == manyMultiplicityKey_Local.ForeignKeyName)) continue; manyMultiplicityKey_Local.UpdateAction = ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString())); manyMultiplicityKey_Local.DeleteAction = ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString())); // to the list primaryKeyTable.ForeignKeys.Add(manyMultiplicityKey_Local); // apply local column DbColumn localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKey_Local.LocalColumnName); manyMultiplicityKey_Local.LocalColumn = localColumn; if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = primaryKeyTable; } if (foreignKeyTable != null) { // foreign table of that! manyMultiplicityKey_Local.ForeignTable = foreignKeyTable; // apply foreign column DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKey_Local.ForeignColumnName); manyMultiplicityKey_Local.ForeignColumn = foreignColumn; } else { manyMultiplicityKey_Local.ForeignTable = null; manyMultiplicityKey_Local.ForeignColumn = null; } } // one-to-? foreign relation will be added if (foreignKeyTable != null) { // foreign key many end var oneMultiplicityKey_Foreign = new DbForeignKey() { ForeignKeyName = keysDataRow["ForeignKey"].ToString(), LocalColumnName = keysDataRow["FKColumnName"].ToString(), ForeignColumnName = keysDataRow["PKColumnName"].ToString(), ForeignTableName = keysDataRow["PKTable"].ToString(), Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany }; // check if it is already there if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName)) continue; oneMultiplicityKey_Foreign.UpdateAction = ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString())); oneMultiplicityKey_Foreign.DeleteAction = ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString())); // to the list foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey_Foreign); // apply local column DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.LocalColumnName); oneMultiplicityKey_Foreign.LocalColumn = localColumn; if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = primaryKeyTable; } if (primaryKeyTable != null) { // foreign table of that! oneMultiplicityKey_Foreign.ForeignTable = primaryKeyTable; // apply foreign column DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.ForeignColumnName); oneMultiplicityKey_Foreign.ForeignColumn = foreignColumn; } else { oneMultiplicityKey_Foreign.ForeignTable = null; oneMultiplicityKey_Foreign.ForeignColumn = null; } } }// all foreign keys // look for one-to-one situation! } } } } catch { // Seems this version of SQL Server doesn't support this query! // don't stop here! } }
/// <summary> /// Reads tables index keys /// </summary> private void ApplyTablesConstraintKeys(List<DbTable> tables, SQLServerVersions sqlVersion) { if (sqlVersion == SQLServerVersions.SQL2000 || sqlVersion == SQLServerVersions.SQL2000Below) { // not supported return; } //// Table constraints for SQL Server 2005 and above //SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName, sys.indexes.name AS IndexName, sys.indexes.is_unique AS IsUnique, // sys.indexes.is_primary_key AS IsPrimaryKey, sys.indexes.ignore_dup_key AS IgnoreDuplicateKey, sys.indexes.is_unique_constraint AS IsUniqueConstraintKey, // sys.indexes.is_disabled AS Disabled //FROM sys.objects INNER JOIN // sys.indexes INNER JOIN // sys.index_columns INNER JOIN // sys.columns ON sys.index_columns.object_id = sys.columns.object_id AND sys.index_columns.column_id = sys.columns.column_id ON // sys.indexes.object_id = sys.index_columns.object_id AND sys.indexes.index_id = sys.index_columns.index_id ON // sys.objects.object_id = sys.index_columns.object_id //WHERE (sys.objects.is_ms_shipped = 0) AND (sys.objects.type = 'U') string constraintKeySql = "SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName, sys.indexes.name AS IndexName, sys.indexes.is_unique AS IsUnique, " + " sys.indexes.is_primary_key AS IsPrimaryKey, sys.indexes.ignore_dup_key AS IgnoreDuplicateKey, sys.indexes.is_unique_constraint AS IsUniqueConstraintKey, " + " sys.indexes.is_disabled AS Disabled " + " FROM sys.objects INNER JOIN " + " sys.indexes INNER JOIN " + " sys.index_columns INNER JOIN " + " sys.columns ON sys.index_columns.object_id = sys.columns.object_id AND sys.index_columns.column_id = sys.columns.column_id ON " + " sys.indexes.object_id = sys.index_columns.object_id AND sys.indexes.index_id = sys.index_columns.index_id ON " + " sys.objects.object_id = sys.index_columns.object_id " + " WHERE (sys.objects.is_ms_shipped = 0) AND (sys.objects.type = 'U') "; using (SqlDataAdapter adapter = new SqlDataAdapter(constraintKeySql, _dbConnection)) { adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; // description data table using (DataTable keysData = new DataTable()) { // Just to avoid stupid "Failed to enable constraints" error! using (DataSet tempDs = new DataSet()) { // Avoiding stupid "Failed to enable constraints" error! tempDs.EnforceConstraints = false; tempDs.Tables.Add(keysData); // Get from db adapter.Fill(keysData); } if (keysData.Rows.Count > 0) { // find description if there is any foreach (var table in tables) { // filter row keysData.DefaultView.RowFilter = " TableName='" + table.TableName + "' AND IsPrimaryKey=0 "; // fetch findings, if there is any foreach (DataRowView keysDataRow in keysData.DefaultView) { // found table ! DataRow keyRow = keysDataRow.Row; // constraint Key var constraintKey = new DbConstraintKey() { IsUnique = Convert.ToBoolean(keyRow["IsUnique"].ToString()), KeyColumnName = keyRow["ColumnName"].ToString(), KeyName = keyRow["IndexName"].ToString() }; // constraint keys table.ConstraintKeys.Add(constraintKey); // find key column DbColumn keyColumn = table.FindColumnDb(constraintKey.KeyColumnName); constraintKey.KeyColumn = keyColumn; } } } } } }
/// <summary> /// Reads tables index keys /// </summary> private void ApplyTablesConstraintKeys(List <DbTable> tables, SQLServerVersions sqlVersion) { if (sqlVersion == SQLServerVersions.SQL2000 || sqlVersion == SQLServerVersions.SQL2000Below) { // not supported return; } //// Table constraints for SQL Server 2005 and above //SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName, sys.indexes.name AS IndexName, sys.indexes.is_unique AS IsUnique, // sys.indexes.is_primary_key AS IsPrimaryKey, sys.indexes.ignore_dup_key AS IgnoreDuplicateKey, sys.indexes.is_unique_constraint AS IsUniqueConstraintKey, // sys.indexes.is_disabled AS Disabled //FROM sys.objects INNER JOIN // sys.indexes INNER JOIN // sys.index_columns INNER JOIN // sys.columns ON sys.index_columns.object_id = sys.columns.object_id AND sys.index_columns.column_id = sys.columns.column_id ON // sys.indexes.object_id = sys.index_columns.object_id AND sys.indexes.index_id = sys.index_columns.index_id ON // sys.objects.object_id = sys.index_columns.object_id //WHERE (sys.objects.is_ms_shipped = 0) AND (sys.objects.type = 'U') string constraintKeySql = "SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName, sys.indexes.name AS IndexName, sys.indexes.is_unique AS IsUnique, " + " sys.indexes.is_primary_key AS IsPrimaryKey, sys.indexes.ignore_dup_key AS IgnoreDuplicateKey, sys.indexes.is_unique_constraint AS IsUniqueConstraintKey, " + " sys.indexes.is_disabled AS Disabled " + " FROM sys.objects INNER JOIN " + " sys.indexes INNER JOIN " + " sys.index_columns INNER JOIN " + " sys.columns ON sys.index_columns.object_id = sys.columns.object_id AND sys.index_columns.column_id = sys.columns.column_id ON " + " sys.indexes.object_id = sys.index_columns.object_id AND sys.indexes.index_id = sys.index_columns.index_id ON " + " sys.objects.object_id = sys.index_columns.object_id " + " WHERE (sys.objects.is_ms_shipped = 0) AND (sys.objects.type = 'U') "; using (SqlDataAdapter adapter = new SqlDataAdapter(constraintKeySql, _dbConnection)) { adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; // description data table using (DataTable keysData = new DataTable()) { // Just to avoid stupid "Failed to enable constraints" error! using (DataSet tempDs = new DataSet()) { // Avoiding stupid "Failed to enable constraints" error! tempDs.EnforceConstraints = false; tempDs.Tables.Add(keysData); // Get from db adapter.Fill(keysData); } if (keysData.Rows.Count > 0) { // find description if there is any foreach (var table in tables) { // filter row keysData.DefaultView.RowFilter = " TableName='" + table.TableName + "' AND IsPrimaryKey=0 "; // fetch findings, if there is any foreach (DataRowView keysDataRow in keysData.DefaultView) { // table found ! DataRow keyRow = keysDataRow.Row; // constraint Key var constraintKey = new DbConstraintKey() { IsUnique = Convert.ToBoolean(keyRow["IsUnique"].ToString()), KeyColumnName = keyRow["ColumnName"].ToString(), KeyName = keyRow["IndexName"].ToString() }; // constraint keys table.ConstraintKeys.Add(constraintKey); // find key column DbColumn keyColumn = table.FindColumnDb(constraintKey.KeyColumnName); constraintKey.KeyColumn = keyColumn; } } } } } }