Beispiel #1
0
        public List <ForeignKeyInfo> GetForeignKeys(string tableName)
        {
            List <ForeignKeyInfo> fks = new List <ForeignKeyInfo>();
            string sql = @"select
                                        a.r_constraint_name Constraint_Name,
                                        a.table_name Sub_Table_Name,
                                        c.column_name Sub_Table_Column_Name,
                                        b.table_name Main_Table_Name,
                                        d.column_name Main_Table_Column_Name
                                        from
                                        user_constraints a,
                                        user_constraints b,
                                        user_cons_columns c,
                                        user_cons_columns d
                                        where
                                            a.r_constraint_name=b.constraint_name
                                        and a.constraint_type='R'
                                        and b.constraint_type='P'
                                        and a.r_owner=b.owner
                                        and a.constraint_name=c.constraint_name
                                        and b.constraint_name=d.constraint_name
                                        and a.owner=c.owner
                                        and a.table_name=c.table_name
                                        and b.owner=d.owner
                                        and b.table_name=d.table_name and a.table_name='" + tableName + "'";

            using (DbDataReader odr = OracleHelper.ExecuteReader(CommandType.Text, sql, null))
            {
                while (odr.Read())
                {
                    ForeignKeyInfo model = new ForeignKeyInfo();
                    if (!odr.IsDBNull(0))
                    {
                        model.ConstraintName = odr.GetString(0);
                    }
                    if (!odr.IsDBNull(1))
                    {
                        model.SubTableName = odr.GetString(1);
                    }
                    if (!odr.IsDBNull(2))
                    {
                        model.SubTableColumnName = odr.GetString(2);
                    }
                    if (!odr.IsDBNull(3))
                    {
                        model.MainTableName = odr.GetString(3);
                    }
                    if (!odr.IsDBNull(4))
                    {
                        model.MainTableColumnName = odr.GetString(4);
                    }
                    fks.Add(model);
                }
            }

            return(fks);
        }
Beispiel #2
0
        /// <summary>
        /// 获取该表下的所有外键
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List <ForeignKeyInfo> GetForeignKeys(string tableName)
        {
            List <ForeignKeyInfo> fks = new List <ForeignKeyInfo>();
            string sql = @"select fk.name as ConstraintName,
		                                        oMain.name  as  MainTableName,  
		                                        MainCol.name as MainTableColumnName,
		                                        oSub.name as SubTableName, 
		                                        SubCol.name as SubTableColumnName from sys.foreign_keys fk      
                                    JOIN sys.all_objects oSub          
		                                        ON (fk.parent_object_id = oSub.object_id)    
                                    JOIN sys.all_objects oMain 
		                                        ON (fk.referenced_object_id = oMain.object_id)    
                                    JOIN sys.foreign_key_columns fkCols         
                                                ON (fk.object_id = fkCols.constraint_object_id)    
                                    JOIN sys.columns SubCol         
                                                ON (oSub.object_id = SubCol.object_id  AND fkCols.parent_column_id = SubCol.column_id)    
                                    JOIN sys.columns MainCol         
                                                ON (oMain.object_id = MainCol.object_id   AND fkCols.referenced_column_id = MainCol.column_id) Where oSub.name='" + tableName + "'";

            using (DbDataReader sdr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
            {
                while (sdr.Read())
                {
                    ForeignKeyInfo model = new ForeignKeyInfo();
                    if (!sdr.IsDBNull(0))
                    {
                        model.ConstraintName = sdr.GetString(0);
                    }
                    if (!sdr.IsDBNull(1))
                    {
                        model.MainTableName = sdr.GetString(1);
                    }
                    if (!sdr.IsDBNull(2))
                    {
                        model.MainTableColumnName = sdr.GetString(2);
                    }
                    if (!sdr.IsDBNull(3))
                    {
                        model.SubTableName = sdr.GetString(3);
                    }
                    if (!sdr.IsDBNull(4))
                    {
                        model.SubTableColumnName = sdr.GetString(4);
                    }
                    fks.Add(model);
                }
            }

            return(fks);
        }