如何在所有数据库中列出外键 [英] How to list foreign keys in all databases

问题描述

我知道如何在给定的数据库中列出外键

I know how to list foreign keys in a given database

USE mydatabase;
go
SELECT 
    f.name AS ForeignKey, 
    OBJECT_NAME(f.parent_object_id) AS TableName, 
    COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName, 
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 
    COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName 
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id 
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id;
go

但是我希望能够通过t-sql代码从不同的数据库中获得相同的信息.我不想使用"USE database"，但我看不到其他方法.

当我将数据库名称添加到上面的代码并从数据库mydatabase运行它时，

However I want to be able to get the same information from different databases though a t-sql code. I do not want to use "USE database" but I see no other way.

When I add the database name to the above code and run it from database mydatabase,

SELECT 
    f.name AS ForeignKey, 
    OBJECT_NAME(f.parent_object_id, DB_ID('AdventureWorks2008')) AS TableName, 
    COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName, 
    OBJECT_NAME (f.referenced_object_id, DB_ID('AdventureWorks2008')) AS ReferenceTableName, 
    COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName 
FROM AdventureWorks2008.sys.foreign_keys AS f 
INNER JOIN AdventureWorks2008.sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id 
INNER JOIN AdventureWorks2008.sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

我没有获得列名. br/>
有人有获取这些的指针吗?

谢谢您，

I do not get the column names.

Does anyone have a pointer for getting these?

Thank you,

推荐答案

看看以下链接
识别您在其中的所有外键SQL Server数据库 [

Take a look at the following link
Identify all of your foreign keys in a SQL Server database[^]

这篇关于如何在所有数据库中列出外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！