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
精彩评论