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
selectpk.table_name, c.COLUMN_NAME
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk 
INNER JOININFORMATION_SCHEMA.KEY_COLUMN_USAGE c
ON c.TABLE_NAME = pk.TABLE_NAME
andc.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)