--查询外键约束
select FK_Name as [外键名],Parent_Tab_Name as [外键表],
[外键列]=stuff((select '，'+[Parent_Col_Name] from (
select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
from sys.foreign_keys FK
inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id
inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'
inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id
and Col.parent_column_id = Parent_Col.column_id
inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'
inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id
and Col.referenced_column_id = Referenced_Col.column_id
)t where FK_Name=tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name for xml path('')), 1, 1, ''),
Referenced_Tab_Name as [主键表],
[主键列]=stuff((select '，'+[Referenced_Col_Name] from (
select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
from sys.foreign_keys FK
inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id
inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'
inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id
and Col.parent_column_id = Parent_Col.column_id
inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'
inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id
and Col.referenced_column_id = Referenced_Col.column_id
)t where FK_Name=tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name for xml path('')), 1, 1, '')
--as [外键列]
from (
select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
from sys.foreign_keys FK
inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id
inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'
inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id
and Col.parent_column_id = Parent_Col.column_id
inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'
inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id
and Col.referenced_column_id = Referenced_Col.column_id
)tb
--WHERE tb.Parent_Tab_Name='PB_UserRole' OR tb.Referenced_Tab_Name='PB_UserRole'
group by FK_Name,Parent_Tab_Name,Referenced_Tab_Name
--利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息
SELECT c.TABLE_SCHEMA ,
c.TABLE_NAME ,
c.COLUMN_NAME ,
CASE WHEN ( ( CHARINDEX('char', c.DATA_TYPE) > 0
OR CHARINDEX('binary', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH <> -1
)
THEN c.DATA_TYPE + '('
+ CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'
WHEN ( ( CHARINDEX('CHAR', c.DATA_TYPE) > 0
OR CHARINDEX('binary', c.DATA_TYPE) > 0
)
AND c.CHARACTER_MAXIMUM_LENGTH = -1
) THEN c.DATA_TYPE + '(max)'
WHEN ( CHARINDEX('numeric', c.DATA_TYPE) > 0 )
THEN c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))
+ ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + ')'
ELSE c.DATA_TYPE
END AS DATA_TYPE ,
c.COLUMN_DEFAULT ,
c.IS_NULLABLE ,
c.COLUMN_DEFAULT
FROM [INFORMATION_SCHEMA].[COLUMNS] c
WHERE TABLE_NAME = 'AdminsBase'
ORDER BY c.ORDINAL_POSITION
