This script is dependent on how well the developers involved with your database stuck to naming conventions. It's not 100% accurate, but has saved me a lot of time trying to identify missing foreign key constraints.
Example:
A lookup table for address types
CREATE TABLE [dbo].[AddressType](
[AddressTypeID] [tinyint] NOT NULL,
[AddressTypeDesc] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_AddressType] PRIMARY KEY CLUSTERED
(
[AddressTypeID] ASC
)
A table containing addresses for customers
CREATE TABLE [dbo].[Address](
[AddressID] [int] IDENTITY(1,1) NOT NULL,
[AddressLine1] [nvarchar](50) NOT NULL,
[AddressLine2] [nvarchar](50) NULL,
[City] [nvarchar](50) NOT NULL,
[StateorProvince] [nvarchar](50) NOT NULL,
[Zip] [nvarchar](10) NOT NULL,
[Country] [nvarchar](50) NULL,
[County] [nvarchar](20) NULL,
[CustomerID] [int] NULL,
[AddressTypeID] [tinyint] NULL,
CONSTRAINT [PK_Address_1] PRIMARY KEY NONCLUSTERED
(
[AddressID] ASC
)
Based on naming convention, the script combined with the view it creates would know that the column [Address].[AddressTypeID] should have a fkey pointing to the address type lookup table. It knows based in information_schema that the key doesn't yet exist. It will then print the script to the output window.
