sp_pkeys and sp_fkeys to find primary keys and foreign keys
There are two very important stored procedures which many SQL Server DBA / Developers do not use to view primary key and foreign key constraints on a table. These two stored procedures can be very helpful in T-SQL scripts, but with SQL Server Management Studio its much more easy to view the primary keys and foreign keys defined on any particular table. The two stored procedures are.
sp_pkeys
sp_fkeys
The first one can be used to view primary keys on any table.
USE Northwind
GO
EXEC sp_pkeys @table_name = 'Orders'
sp_pkeys will query sysindexes table or view to get the primary key information.
To view foreign keys on table you can use sp_fkeys system stored procedure. This procedure can take primary key table or foreign key table as parameter to return the foreign keys.
USE Northwind
GO
EXEC sp_fkeys @fktable_name = 'Orders'
The above code with @fktable_name will show all the foreign keys in table, more like WHERE foreign key table = Table_Name.
USE Northwind
GO
EXEC sp_fkeys @pktable_name = 'Orders'
The second script with @pktable_name will show all the foreign keys from Orders table.
I have seen in many SQL Server forums users complaining about the slow speed of sp_fkeys stored procedure specially when database have lot of tables. To solve this issue you can query the sysreferences table or view in SQL Server 2000 and SQL Server 2005.
SELECT OBJECT_NAME(constid) AS [Foreign Key Name],
OBJECT_NAME(fkeyid) AS [Foreign Key Table],
OBJECT_NAME(rkeyid) AS [Referenced Table] FROM sysreferences
You will need to rewrite the script with a WHERE clause to better fit your needs, to see all the columns in sysreferences tabale/view and usage please check book online.