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中的一个固有表名。

SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
       PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
       FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
       FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
       -- Force the column to be non-nullable (see SQL BU 325751)
       --KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade') 
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
       PK_NAME = CONVERT(SYSNAME,I.NAME),
       DEFERRABILITY = CONVERT(SMALLINT,7)   -- SQL_NOT_DEFERRABLE
FROM   SYS.ALL_OBJECTS O1,
       SYS.ALL_OBJECTS O2,
       SYS.ALL_COLUMNS C1,
       SYS.ALL_COLUMNS C2,
       SYS.FOREIGN_KEYS F
       INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
         ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
       INNER JOIN SYS.INDEXES I
         ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
             AND F.KEY_INDEX_ID = I.INDEX_ID)
WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
       AND C2.COLUMN_ID = K.PARENT_COLUMN_ID

上面有一些不错的答案。但我更喜欢一个问题就能得到答案。 这段代码来自sys. .Sp_helpconstraint (sys proc)

这是微软查找是否有与tbl关联的外键的方法。

--setup variables. Just change 'Customer' to tbl you want
declare @objid int,
    @objname nvarchar(776)
select @objname = 'Customer'    
select @objid = object_id(@objname)

if exists (select * from sys.foreign_keys where referenced_object_id = @objid)
    select 'Table is referenced by foreign key' =
        db_name() + '.'
        + rtrim(schema_name(ObjectProperty(parent_object_id,'schemaid')))
        + '.' + object_name(parent_object_id)
        + ': ' + object_name(object_id)
    from sys.foreign_keys 
    where referenced_object_id = @objid 
    order by 1

答案看起来像这样:test_db_name.dbo。账户:FK_Account_Customer

也试一试。

EXEC sp_fkeys 'tableName', 'schemaName'

使用sp_fkeys，不仅可以通过pk表名和模式过滤结果，还可以通过fk表名和模式过滤结果。链接

通过@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;

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

with tab_list as (
    select t.name AS Table_Name, t.object_id, s.name AS Table_Schema  from sys.tables t, sys.schemas s 
     where t.schema_id = s.schema_id
       and s.name = 'your schema') 
select IIF(col.column_id = 1, tab.TABLE_SCHEMA + '.' + tab.TABLE_NAME, NULL) Table_Name,
       col.Name AS Column_Name, IIF(col.IS_NULLABLE= 0, 'NOT NULL', '') Nullable, st.name Type,
       CASE WHEN st.name = 'decimal' THEN CONVERT(NVARCHAR(4000), col.Precision) + ',' + CONVERT(NVARCHAR(4000), col.Scale) 
            WHEN col.max_length = -1 THEN 'max'
            WHEN st.name in ('int', 'bit', 'bigint', 'datetime2') THEN NULL
       ELSE CONVERT(NVARCHAR(4000), col.max_length / 2)
       END
       AS Length,
       ss.name + '.' + stab.name Referenced_Table, scol.name Referenced_Column 
from sys.COLUMNS col  
    INNER JOIN tab_list tab ON col.object_id = tab.object_id
    INNER JOIN sys.types st ON col.system_type_id = st.system_type_id AND col.user_type_id = st.user_type_id 
    LEFT JOIN [sys].[foreign_key_columns] sfkc ON col.object_id = sfkc.parent_object_id AND col.column_id = sfkc.parent_column_id
    LEFT JOIN sys.tables stab ON sfkc.referenced_object_id = stab.object_id
    LEFT JOIN sys.columns scol ON sfkc.referenced_object_id = scol.object_id AND sfkc.referenced_column_id = scol.column_id 
    LEFT JOIN sys.schemas ss ON ss.schema_id = stab.schema_id