select * from sys.objects where parent_object_id=object_id('tbname')
SELECT a.NAME AS 外键名称 , OBJECT_NAME(a.[parent_object_id]) 外键表, OBJECT_NAME(a.[referenced_object_id]) 主键表, c.NAME AS 主键列 FROM sys.foreign_keys AS a JOIN sys.foreign_key_columns AS b ON a.[object_id]=b.[constraint_object_id] JOIN sys.columns AS c ON b.[referenced_object_id]=c.[object_id] AND b.[referenced_column_id]=c.[column_id] JOIN sys.columns AS d ON b.[parent_object_id]=d.[object_id] AND b.[parent_column_id]=d.[column_id] WHERE OBJECT_NAME(a.parent_object_id)='tb'
select s.id,k.fkeyid,k.fkey,c.name from sysobjects s inner join sysforeignkeys k on k.constid=s.id inner join syscolumns c on c.id=k.fkeyid and k.fkey=c.colid where OBJECT_NAME(parent_obj)='tablename' and s.xtype='F'
select c.name,OBJECT_NAME(f.referenced_object_id) from sysobjects s inner join sysforeignkeys k on k.constid=s.id inner join syscolumns c on c.id=k.fkeyid and k.fkey=c.colid inner join sys.foreign_keys f on s.name=f.name where OBJECT_NAME(parent_obj)='HireMG' and s.xtype='F'
大侠,这个SQL语句只能查询出主外键关系名 和外键表名 如何查询出一个表中的外键字段名呢?
OBJECT_NAME(a.[parent_object_id]) 外键表,
OBJECT_NAME(a.[referenced_object_id]) 主键表,
c.NAME AS 主键列
FROM sys.foreign_keys AS a
JOIN sys.foreign_key_columns AS b ON a.[object_id]=b.[constraint_object_id]
JOIN sys.columns AS c ON b.[referenced_object_id]=c.[object_id] AND b.[referenced_column_id]=c.[column_id]
JOIN sys.columns AS d ON b.[parent_object_id]=d.[object_id] AND b.[parent_column_id]=d.[column_id]
WHERE OBJECT_NAME(a.parent_object_id)='tb'
inner join sysforeignkeys k on k.constid=s.id
inner join syscolumns c on c.id=k.fkeyid and k.fkey=c.colid
where OBJECT_NAME(parent_obj)='tablename' and s.xtype='F'
inner join sysforeignkeys k on k.constid=s.id
inner join syscolumns c on c.id=k.fkeyid and k.fkey=c.colid
inner join sys.foreign_keys f on s.name=f.name
where OBJECT_NAME(parent_obj)='HireMG' and s.xtype='F'