SQL Script To Generate a Database Dictionary **With Linked Fields**

I would like to generate a Data Dictionary for a SQL Server 2008 database that has one row for each field, and the following columns:

table_name

field_name

data_type

link_table (for when the field in question is a foreign key)

link_field (for when the field in question is a foreign key)

I can get the first 3 columns with something like the SQL script below...but I don't know how 开发者_Python百科to get the last two columns of foreign key information. INFORMATION_SCHEMA.TABLE_CONSTRAINTS gets close, but doesn't have the data I'm looking for. Can someone help with this point?

 SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS

Secondarily if anyone has any suggestions on additional fields which would be helpful please post.

---

The following query will duplicate a column if it is involved in more than one relationship.

Select C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE
    , PKCol.TABLE_SCHEMA, PKCol.TABLE_NAME, PKCol.COLUMN_NAME
From INFORMATION_SCHEMA.COLUMNS As C
    Left Join (INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE As FKCol
        Join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS As FK
            On FK.CONSTRAINT_NAME = FKCol.CONSTRAINT_NAME
        Join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE As PKCol
            On PKCol.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME)
        On FKCol.TABLE_SCHEMA = C.TABLE_SCHEMA
            And FKCol.TABLE_NAME = C.TABLE_NAME
            ANd FKCol.COLUMN_NAME = C.COLUMN_NAME