sp_getreferences

DROP PROCEDURE sp_getreferences
go
CREATE PROCEDURE sp_getreferences @tablename VARCHAR(256)
AS

SELECT irc.constraint_schema+'.'+ccu.table_name AS fkey_table,
ccu.column_name AS fkey_column,
ccu.constraint_name [fkey_name],ikc.table_schema+'.'+ikc.table_name AS referenced_table,
ikc.column_name AS referenced_column
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS irc
ON ccu.constraint_name=irc.constraint_name
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ikc
ON irc.unique_constraint_name=ikc.constraint_name
WHERE ccu.table_name=@tablename; 


SELECT irc.unique_constraint_schema+'.'+ikc.table_name AS [pkey_table_name],
ikc.column_name,ikc.constraint_name,
irc.constraint_name [is_referenced_by_foreign_key],
ccu.table_schema+'.'+ccu.table_name [from_table],
ccu.column_name [fkey_column]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ikc
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS irc
ON ikc.constraint_name=irc.unique_constraint_name
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON ccu.constraint_name=irc.constraint_name
AND ikc.COLUMN_NAME=ccu.COLUMN_NAME
WHERE ikc.table_name=@tablename;