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