Technical Article

Foreign keys chain in a view. It may be useful.

,

I made this view because I had to generate a sql server database from  an Oracle database and I had to knew the order in which I import tables.
This view, which may be improved, contains the foreign key, the primary and detail table, the column and it's position and may be useful in  many situations

CREATE VIEW VW_FK_Constraints
AS
SELECT s_1.name AS FK_Name, ccu.COLUMN_NAME AS Column_Name, s_2.name AS Detail_Name, s_3.name AS Header_Name, 
sc.colid AS Position
FROM sysobjects s_1 INNER JOIN sysreferences r ON s_1.id = r.constid
INNER JOIN sysobjects s_2 ON s_2.id = r.fkeyid
INNER JOIN sysobjects s_3 ON s_3.id = r.rkeyid
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON ccu.CONSTRAINT_NAME = s_1.name
INNER JOIN syscolumns sc ON sc.name = ccu.COLUMN_NAME AND sc.id = s_2.id

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating