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

时间:2021-09-04 14:04:35

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


2  

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

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

sp_depends 'TableName'

#2


1  

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


0  

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


2  

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

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

sp_depends 'TableName'

#2


1  

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


0  

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