Find possible missing foreign key relationships

,

This procedure is intended for single column primary key relationships.
First we will create two table functions. The first finds a list of all tables with their primary key columns. The second finds all existing foreign key relationships with their table and column names. Finally we find all tables with primary key column names that do not have an existing foreign key relationship.

/* First create a table function that  
  returns the primary key tables and columns */

If Exists (Select name from sysobjects where name = 'tblfn_PrimaryKeys')
	Drop Function tblfn_PrimaryKeys
GO

CREATE Function dbo.tblfn_PrimaryKeys()
RETURNS @Values table (TableName varchar(128), ColumnName varchar(128))
AS

/* Retrieves the table name and primary key columns for each table. */


BEGIN
Insert Into @Values
select	pk.table_name, c.COLUMN_NAME
	from	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk 
		INNER JOIN	INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
		ON c.TABLE_NAME = pk.TABLE_NAME
		and	c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
	where 	CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY pk.table_name, c.column_name

RETURN

END
GO

/* Next create a table function that  
  returns the foreign key names, tables, and columns */

If Exists (Select name from sysobjects where name = 'tblfn_ForeignKeys')
	Drop Function tblfn_ForeignKeys
GO


CREATE Function dbo.tblfn_ForeignKeys()
Returns @ForeignKey table (ForeignKeyName varchar(128), PrimaryTable varchar(128), PrimaryColumn varchar(128),
	ForeignTable varchar(128),ForeignColumn varchar(128))

AS

/* Returns all foreign keys with the primary and foreign tables and column information. */
BEGIN
Insert Into @ForeignKey
Select sfko.name AS ForeignKeyName, sro.name AS PrimaryTable,  
	src.name AS PrimaryColumn, so.name AS ForeignTable, sc.name AS ForeignColumn
From sysforeignkeys sfk left outer join sysobjects so on sfk.fkeyid = so.id
	LEFT OUTER JOIN sysobjects sro on sfk.rkeyid = sro.id
	LEFT OUTER JOIN syscolumns src on sro.id = src.id and
	src.colid = sfk.rkey 
	LEFT OUTER JOIN syscolumns sc on so.id = sc.id and
	sc.colid = sfk.fkey 
	LEFT OUTER JOIN sysobjects sfko on sfk.constid = sfko.id
ORDER BY sro.name, so.name


Return 
END

GO
/* Finally, write the query to view all possible missing foreign keys */

Select T.Table_Name AS TableName, Column_Name AS ColumnName
	From INFORMATION_SCHEMA.Columns C LEFT OUTER JOIN 
		INFORMATION_SCHEMA.Tables T ON C.Table_Name = T.Table_Name
	WHERE T.table_type = 'Base table' and T.table_schema = 'dbo'  
	and T.Table_Name + Column_Name not in (
		Select ForeignTable + ForeignColumn
		From dbo.tblfn_ForeignKeys())
	 and T.Table_Name + Column_Name not in (
		Select TableName + ColumnName
		From dbo.tblfn_PrimaryKeys())
	 AND Column_Name in (
		Select ColumnName
		From dbo.tblfn_PrimaryKeys()) 
	ORDER BY T.Table_Name, Column_Name

Rate

5 (2)

Share

Share

Rate

5 (2)