Geek asks and answers

SQL - FK column detail search

I have SQL that contains some details about a table.

SELECT Column_Name, Is_Nullable, Data_Type, Character_Maximum_Length
FROM Information_Schema.Columns
WHERE Table_Name='GenSchool'

This has been working fine so far and returns row by column in the table. However, I want it to also return some foreign key details. For example, it GenSchoolhas a column SchoolTypethat has FK up GenSchoolType.Code.

Like the columns selected above, I need a query to return the table name FK and the column name of the linked table or NULL where the column does not have FK.

This is returned from the request above.

Code            NO  nvarchar    10
CodeDescription YES nvarchar    80
Deleted         NO  bit         NULL
Type            NO  nvarchar    20

And I would like for him to return something like

Code            NO  nvarchar    10      NULL             NULL
CodeDescription YES nvarchar    80      NULL             NULL
Deleted         NO  bit         NULL    NULL             NULL
Type            NO  nvarchar    20      GenSchoolType    Code

I’ve been trying for centuries to use internal joins in tables sys, but I’m not going anywhere. If you need me to show what I tried, I can.

Thanks in advance.

+3
source share
4

, , , .

with fks as ( 
    select fk.name AS 'FKName', o_p.name AS 'ParentNameTable', 
        c_p.name AS 'ParentNameColumn', o_r.name AS 'ReferencedNameTable', 
        c_r.name AS 'ReferencedNameColumn', fkc.* 
    from sys.foreign_key_columns fkc 
    inner join sys.foreign_keys fk on fk.object_id = fkc.constraint_object_id 
    inner join sys.objects o_p on o_p.object_id = fkc.parent_object_id 
    inner join sys.objects o_r on o_r.object_id = fkc.referenced_object_id 
    inner join sys.columns c_p on c_p.object_id = fkc.parent_object_id and c_p.column_id = fkc.parent_column_id 
    inner join sys.columns c_r on c_r.object_id = fkc.referenced_object_id and c_r.column_id = fkc.referenced_column_id 
) 
select c.Table_Name, c.Column_Name, c.Is_Nullable, c.Data_Type,
    c.Character_Maximum_Length, COLUMNPROPERTY(OBJECT_ID(@table), 
    c.Column_Name,'IsIdentity') AS 'Is_Identity', 
    fks.FKName, fks.ReferencedNameTable, fks.ReferencedNameColumn 
from information_schema.columns c 
left join fks on c.Table_Name = fks.ParentNameTable and c.Column_Name = fks.ParentNameColumn 
where c.table_name = @table

  • Nullable
  • FK

, .

+2

information_schema.REFERENTIAL_CONSTRAINTS:

SELECT Column_Name, Is_Nullable, Data_Type, Character_Maximum_Length, rc.REFERENCED_TABLE_NAME
FROM Information_Schema.Columns c
left join information_schema.REFERENTIAL_CONSTRAINTS rc on rc.TABLE_NAME = c.TABLE_NAME
WHERE Table_Name='GenSchool'
+1

Here is the request for the MSDN page related to your request.

SELECT TABLE_NAME, CONSTRAINT_NAME , COLUMN_NAME , ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE    
WHERE CONSTRAINT_NAME IN (    
    SELECT CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS) 
ORDER BY TABLE_NAME, ORDINAL_POSITION
+1
source

Have you tried sp_fkeys

exec sp_fkeys <TableName>
+1
source

More articles:


All Articles