Problem:
How to query foreign key constraints on a given
table.
Solution:
There are procedures which can through the output and also
views which can show the information from the object catalog.
All these views store the dependencies information of a
table and some of them store referencing objects as well. To go through all of
them at one place Sp_fkeys is the system procedure which accepts the parent
table or foreign key table and the System procedure sp_foreignKeys will show
the constraints on the linked server tables as well.
Example:
As always let’s explore
few methods on how to get the foreign key constraints on a table using a SQL
query.
--
Using sp_Fkey storedprocedure
EXEC sp_fkeys @fktable_name =
N'History'
,@pktable_owner
= N'dbo';
--
Using Sys.Foreign_keys view
SELECT NAME,
object_name(parent_object_id) AS parent_Table,
object_name(referenced_object_id)
AS refereneced_table
FROM sys.foreign_keys
WHERE object_name(parent_object_id) = 'History'
--using
Sys.Foreing_key_columns_usage
SELECT object_name(constraint_object_id) AS Constraint_name,
object_name(parent_object_id) AS parent_Table,
object_name(referenced_object_id)
AS refereneced_table
FROM sys.foreign_key_columns
WHERE object_name(parent_object_id) = 'History'
--using
Information_schema views
SELECT table_schema,table_name,column_name,KCU.constraint_name
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
KCU
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
RC
ON KCU.CONSTRAINT_CATALOG =
RC.CONSTRAINT_CATALOG
AND KCU.CONSTRAINT_SCHEMA =
RC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE table_name = 'History'
--using
Information_schema views
SELECT table_schema,table_name,column_name,KCU.constraint_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
KCU
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
RC
ON KCU.CONSTRAINT_CATALOG =
RC.CONSTRAINT_CATALOG
AND KCU.CONSTRAINT_SCHEMA =
RC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE table_name = 'History'
0 comments:
Post a Comment