Report to return all foreign key information

,

The purpose of this task is to generate a report showing all your foreign keys. Generally this would be considered  nothing special only that this script also returns the tables, column foreign key constraint name and the relationship properties.

Here's how to do it:
First of all copy the script and paste it in Query Analyzer

Step 1: Create a temporary table to store table and column data (recommended and part of this script).

Step 2: Execute the final sql statement
Step 3: Drop the local temporary table (optional)

Note: If you have any columns in a table that are referenced more than once as a foreign key you will get duplicity for those columns, which is an excellent way of verifying your relationships too.

/*
Does: 	Step 1: Inserts table and column information into a temporary table
	Step 2: Returns: 	Column 1: FK - table with foreign key
				Column 2: FC - the column name within the foreign key table
				Column 3: FKEY - the column position within the foreign key table
				Column 4: RK - related table with primary key
				Column 5: RC - the column name within the related primary key table
				Column 6: RKEY - the column position within the related primary key table
				Column 7: CONSTRAINT_NAME - the name of the foreign key constraint
				Column 8: UPDATE_RULE - does the relationship have Cascade Update enabled
				Column 9: DELETE_RULE - does the relationship have Cascade Delete enabled
Done: 	20040702
By: 	Maxwell van Zuylen
*/

-- Step 1: Create temporary table
SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME 
INTO #tmpTBLCOL
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME IN (SELECT NAME FROM sysobjects WHERE xtype = 'u') 

-- Step 2: Return results
SELECT 	FO.name AS FK, FC.Column_Name AS FC, FK.FKEY, RO.name AS RK, RC.Column_Name AS RC, FK.RKEY, CN.CONSTRAINT_NAME, RL.UPDATE_RULE, RL.DELETE_RULE
FROM 	dbo.sysforeignkeys FK 
	INNER JOIN dbo.#tmpTBLCOL FC ON FK.fkey = FC.Ordinal_Position 
	INNER JOIN dbo.#tmpTBLCOL RC ON FK.rkey = RC.Ordinal_Position 
	LEFT OUTER JOIN dbo.sysobjects RO ON RC.Table_Name = RO.name AND FK.rkeyid = RO.id 
	LEFT OUTER JOIN dbo.sysobjects FO ON FC.Table_Name = FO.name AND FK.fkeyid = FO.id
	INNER JOIN (SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE) CN ON FO.Name = CN.TABLE_NAME AND FC.Column_Name = CN.COLUMN_NAME
	INNER JOIN (SELECT CONSTRAINT_NAME, UNIQUE_CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS) RL ON CN.CONSTRAINT_NAME = RL.CONSTRAINT_NAME
WHERE 	(NOT (FO.name IS NULL)) AND (NOT (RO.name IS NULL)) --AND FO.name = 'SIPP_Fees'
ORDER BY FO.name


-- Step 3: Remove local temporary table
	-- DROP TABLE #tmpTBLCOL

Rate

5 (1)

Share

Share

Rate

5 (1)