Is that possible to get the key Relationship avilable in my DataBase using query in sql server?. how?.

Question

I have many table in my database it contain many key relation. i need to list out the table and its corresponding key name and the relationship table.

Answer 1

Try

SELECT t.table_schema AS PrimarySchemaName ,
t.TABLE_NAME AS PrimaryKeyTable,
tc.CONSTRAINT_NAME AS PrimaryKey,
COALESCE(tc2.constraint_schema,'N/A') AS ForeignSchemaName,
COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.TABLES t ON tc.TABLE_NAME = t.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME

From here: http://wiki.lessthandot.com/index.php/Find_all_Primary_and_Foreign_Keys_In_A_Database

Answer 2

Another way

EXEC sp_help 'your_table_name'