Oracle SQL

select *
from
    all_constraints
where
    r_constraint_name in
    (select       constraint_name
    from
       all_constraints
    where
       table_name='PUT_THE_TABLE_NAME_HERE');

all_constraints是Oracle DB中的一个固有表名。

我将使用SQL Server Management Studio中的数据库图表功能，但既然你排除了它-这在SQL Server 2008中为我工作(没有2005)。

获取引用表和列名的列表…

select 
    t.name as TableWithForeignKey, 
    fk.constraint_column_id as FK_PartNo, c.
    name as ForeignKeyColumn 
from 
    sys.foreign_key_columns as fk
inner join 
    sys.tables as t on fk.parent_object_id = t.object_id
inner join 
    sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where 
    fk.referenced_object_id = (select object_id 
                               from sys.tables 
                               where name = 'TableOthersForeignKeyInto')
order by 
    TableWithForeignKey, FK_PartNo

获取外键约束的名称

select distinct name from sys.objects where object_id in 
(   select fk.constraint_object_id from sys.foreign_key_columns as fk
    where fk.referenced_object_id = 
        (select object_id from sys.tables where name = 'TableOthersForeignKeyInto')
)

通过@Gishu所做的工作，我能够在SQL Server 2005中生成并使用以下SQL

SELECT t.name AS TableWithForeignKey, fk.constraint_column_id AS FK_PartNo, 
       c.name AS ForeignKeyColumn, o.name AS FK_Name 
  FROM sys.foreign_key_columns AS fk
       INNER JOIN sys.tables AS t ON fk.parent_object_id = t.object_id
       INNER JOIN sys.columns AS c ON fk.parent_object_id = c.object_id 
                                  AND fk.parent_column_id = c.column_id
       INNER JOIN sys.objects AS o ON fk.constraint_object_id = o.object_id
  WHERE fk.referenced_object_id = (SELECT object_id FROM sys.tables 
                                        WHERE name = 'TableOthersForeignKeyInto')
  ORDER BY TableWithForeignKey, FK_PartNo;

在一个查询中显示表，列和外键名称。

你可以通过以下查询找到:

 SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table', 
      OBJECT_NAME(FK.parent_object_id) 'Referring Table', FK.name 'Foreign Key', 
      COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
      COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
     FROM sys.foreign_keys AS FK
             INNER JOIN sys.foreign_key_columns AS FKC 
                 ON FKC.constraint_object_id = FK.OBJECT_ID
     WHERE OBJECT_NAME (FK.referenced_object_id) = 'YourTableName'
     AND COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) = 'YourColumnName'
     order by  OBJECT_NAME(FK.parent_object_id)

最初的问题要求将所有外键的列表放入一个高度引用的表中，以便可以删除表。

这个小查询返回将所有外键放入特定表所需的“drop foreign key”命令:

SELECT 
   'ALTER TABLE ['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' '[DropCommand]'
FROM sys.foreign_key_columns fk
    JOIN sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id
    JOIN sys.schemas sch ON referencingTable.schema_id = sch.schema_id
    JOIN sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id
    JOIN sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id
WHERE referencedTable.name = 'MyTableName'

示例输出:

[DropCommand]
ALTER TABLE [dbo].[OtherTable1] DROP CONSTRAINT [FK_OtherTable1_MyTable]
ALTER TABLE [dbo].[OtherTable2] DROP CONSTRAINT [FK_OtherTable2_MyTable]

省略where -子句以获取当前数据库中所有外键的删除命令。

SELECT
  object_name(parent_object_id),
  object_name(referenced_object_id),
  name 
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Table Name')