主页 / dba / 问题 / 156359
Accepted
Zameer Ansari
Asked: 2016-11-26 03:46:37 +0800 CST

查找数据库中所有没有外键的表

  • 772

在 SQL Server 中，我们如何找到所有不包含以下任何一项的表：

  • 外键约束
  • 其他表的外键引用
sql-server foreign-key

4 个回答

  1. Best Answer

    SwapnilBhate 的答案将返回由外键引用的表。添加进一步LEFT JOIN将排除这些：

    SELECT tbl.name 
FROM sys.tables AS tbl
    LEFT JOIN sys.foreign_key_columns AS fKey 
        ON tbl.object_id = fKey.parent_object_id
    LEFT JOIN sys.foreign_key_columns AS rKey 
        ON tbl.object_id = rKey.referenced_object_id
WHERE fKey.parent_object_id IS NULL 
    AND rKey.referenced_object_id IS NULL;
    • 12

  2. 您可以使用该sp_fkeys过程来获取逻辑外键信息。但限制是您必须通过将表名作为输入参数来执行。更多信息可以在下面的 MSDN 库中找到。

    sp_fkeys

    或者，您可以使用以下查询从 sys.tables 中获取所有表，并检查 sys.foreign_key_columns 中是否存在相同的表 object_id。

    SELECT tbl.name
FROM sys.tables AS tbl
    LEFT JOIN sys.foreign_key_columns AS fKey
    ON tbl.object_id = fKey.parent_object_id
WHERE fKey.parent_object_id IS NULL
    • 5

  3. 为了完整起见，您还可以查询sys.foreign_keys而不是sys.foreign_key_columns来查找既未引用又未引用其他表的表：

    SELECT
  schema_name = s.name,
  table_name  = t.name
FROM
  sys.tables AS t
  INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
  LEFT JOIN sys.foreign_keys AS fk
    ON t.object_id IN (fk.parent_object_id, fk.referenced_object_id)
WHERE
  t.is_ms_shipped = 0
  AND fk.object_id IS NULL
;

    该查询还显示了每个表的模式名称，因为有时系统不仅仅是使用默认的dbo模式构建的。

    我还在is_ms_shipped = 0查询中包含了过滤器，因为我假设您想从搜索中排除预定义/系统表。

    • 2

  4. 这也将为您提供参考表：

    SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS Where COLUMN_NAME like '%id' AND  COLUMN_NAME <> 'Id' AND DATA_TYPE = 'int'
    AND TABLE_NAME + COLUMN_NAME NOT IN (SELECT OBJECT_NAME(fk.parent_object_id) + cpa.name
                                        FROM   sys.foreign_keys fk
                                        INNER JOIN sys.foreign_key_columns fkc ON  fkc.constraint_object_id = fk.object_id
                                        INNER JOIN sys.columns cpa ON  fkc.parent_object_id = cpa.object_id AND fkc.parent_column_id = cpa.column_id 
                                        INNER JOIN sys.columns cref ON  fkc.referenced_object_id = cref.object_id AND fkc.referenced_column_id = cref.column_id)
    order by TABLE_NAME, COLUMN_NAME
    • 0

相关问题