SQL Script To Generate a Database Dictionary **With Linked Fields**
- by Albert
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 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.