public static SMReference Generate(DBSchema schema, DBTable table) { var info = new SMReference { SourceTable = "", Name = "constraint_name", Schema = "schema_name", Table = "table_name", Column = "column_name", ReferenceSchema = "reference_schema_name", ReferenceTable = "reference_table_name", ReferenceColumn = "reference_column_name", Filter = @"SELECT KCU1.CONSTRAINT_NAME AS constraint_name ,KCU1.CONSTRAINT_SCHEMA AS schema_name ,KCU1.TABLE_NAME AS table_name ,KCU1.COLUMN_NAME AS column_name ,KCU2.CONSTRAINT_NAME AS reference_constraint_name ,KCU1.CONSTRAINT_SCHEMA AS reference_schema ,KCU2.TABLE_NAME AS reference_table_name ,KCU2.COLUMN_NAME AS reference_column_name FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION " }; if (schema.Connection.Schema != null || table != null) { var schemaFilter = schema.Connection.Schema == null ? null : string.Format("RC.CONSTRAINT_SCHEMA='{0}'", schema.Connection.Schema); var tableFilter = table == null ? null : string.Format("KCU1.TABLE_NAME='{0}'", table.Name); info.Filter += string.Format("\nWHERE {0}{1}{2}", schemaFilter, schemaFilter != null && tableFilter != null ? " AND " : " ", tableFilter); } if (schema.Connection.System == DBSystem.SQLite) { info.Filter = null;//TODO } if (schema.Connection.System == DBSystem.Oracle) { info.Filter = @"SELECT a.constraint_name, c.owner as schema_name, a.table_name, a.column_name, c_pk.constraint_name as reference_constraint_name c.r_owner as reference_schema, c_pk.table_name as rererence_table_name, c_pk.column_name as rererence_column_name FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name WHERE c.constraint_type = 'R'"; } return(info); }
public static void LoadRefrences(DBSchema schema, DBTable table = null) { var info = SMReference.Generate(schema, table); info.Parse(schema, table); }