Friday, 28 March 2014

Find out the Foreign Key column name ,table name & references table, column names?

SELECT 
    F.NAME AS FOREIGNKEY,
    OBJECT_NAME(F.PARENT_OBJECT_ID) AS TABLENAME,
    COL_NAME(FC.PARENT_OBJECT_ID,
    FC.PARENT_COLUMN_ID) AS COLUMNNAME,
    OBJECT_NAME (F.REFERENCED_OBJECT_ID) AS REFERENCETABLENAME,
    COL_NAME(FC.REFERENCED_OBJECT_ID,
    FC.REFERENCED_COLUMN_ID) AS REFERENCECOLUMNNAME
FROM 
    SYS.FOREIGN_KEYS AS F
    INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_ID
    ORDER BY    COL_NAME(FC.REFERENCED_OBJECT_ID,FC.REFERENCED_COLUMN_ID)

No comments:

Post a Comment

SQL_DBA- Ip Address Ping

Start-Transcript - path C:/Script/PingLog.txt -Append   Ping.exe -t spiceworks.com | ForEach { "{0} - {1}" -f (Get-Date), $_ } ...