sqlserver 常用到的架构相关的表芝士

1 “SELECT COLUMN_NAME,TABLE_NAME FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME='WareHouse_Code'”

如何在数据库中查询一个字段 都存在哪些表中

 

 1 WITH  CTE  
 2         AS (  
 3             SELECT  OBJECT_NAME(constraint_object_id) Constraint_Name , OBJECT_NAME(parent_object_id) Table_Name , C.name Column_Name  
 4             FROM    sys.foreign_key_columns FK  
 5             INNER JOIN sys.columns C  
 6             ON      FK.parent_object_id = C.object_id  
 7                     AND FK.parent_column_id = C.column_id  
 8            )  
 9   SELECT  C.Constraint_Name , C.Table_Name as '从表' , C.Column_Name as '从表中的字段' , OBJECT_NAME(FK.referenced_object_id) as '主表' , SC.name  as '主表中的主键'  
10   FROM    CTE C  
11   INNER JOIN sys.foreign_key_columns FK  
12   ON      C.Constraint_Name = OBJECT_NAME(FK.constraint_object_id)  
13   INNER JOIN sys.columns SC  
14   ON      FK.referenced_object_id = SC.object_id  
15           AND FK.referenced_column_id = SC.column_id   
16     group by OBJECT_NAME(FK.referenced_object_id),SC.name,C.Column_Name,C.Table_Name,C.Constraint_Name
17 
18     order by OBJECT_NAME(FK.referenced_object_id)

查看数据库中的哪些表存在主外键关系

 

转载于:https://www.cnblogs.com/ZkbFighting/p/9964883.html