编写sql server查询，查看一个表链接了多少个表

Provided there are tables Application, Person, Status, etc...

只要有表格应用、人员、状态等……

Status and Person have a link to Application.

状态和人有一个应用的链接。

Is there a simple sql query that may be written that will give a list of all the tables linked to the Application table (in contrast of using the database diagram).

是否可以编写一个简单的sql查询来给出与应用程序表链接的所有表的列表(与使用数据库关系图相反)。

Application expected results:

应用程序预期结果:

Status
状态
Person
人

Please advise on a feasible solution (if any).

请提供可行的解决方案(如果有的话)。

3 个解决方案

#1

Using SP_depends. There are other ways as well like DMV's.

使用SP_depends。还有其他的方法，比如车管所。

sp_depends 'TableName'

#2

Assuming you're using MSSQL Server then you can run

假设您正在使用MSSQL服务器，那么您可以运行。

EXEC sp_fkeys 'YourTableName'

Otherwise you can use

否则你可以使用

SELECT *
FROM information_schema.referential_constraints
WHERE table_name = <tablename>

to establish the foreign key relationships

建立外键关系。

#3

Another way to check each table name that references to other tables would be this:

检查引用到其他表的每个表名的另一种方法是:

SELECT DISTINCT OBJECT_NAME(f.parent_object_id) AS TableName,
                OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName
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

You can, of course, add more conditions to check for a single table, for example:

当然，您可以添加更多的条件来检查单个表，例如:

WHERE OBJECT_NAME(f.referenced_object_id) = 'Application'

This should give you a result like:

这应该会给你一个这样的结果:

TableName  ReferenceTableName
---------- ------------------
Status     Application
Person     Application

#1