DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

SQL Columns Metainfo : "vw_XL_TableReferences"

10.11.2011
| 2321 views |
  • submit to reddit
        CREATE VIEW dbo.vw_XL_ForeignReferences  
AS  
SELECT     TOP (100) PERCENT FK.TABLE_NAME AS TargetTable, CU.COLUMN_NAME AS TargetColumn, PK.TABLE_NAME AS SourceTable,   
                      PT.COLUMN_NAME AS SourceColumn, C.CONSTRAINT_NAME AS RelationName  
FROM         INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C INNER JOIN  
                      INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN  
                      INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN  
                      INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN  
                          (SELECT     i1.TABLE_NAME, i2.COLUMN_NAME  
                            FROM          INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS i1 INNER JOIN  
                                                   INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME  
                            WHERE      (i1.CONSTRAINT_TYPE = 'PRIMARY KEY')) AS PT ON PT.TABLE_NAME = PK.TABLE_NAME  
ORDER BY TargetTable, TargetColumn, SourceTable, SourceColumn