Get you red hot Foreign and primary keys here!!!

Reduce the human factor, auto-generate/ boiler plate / conserve your brain...

Autogenereate base classes from solid DB design with proper contraints and relations. One of my developers did some googling and head scratching, and we got this...

WITH CTE AS (

SELECT      

OBJECT_NAME(sic.OBJECT_ID) PK_TableName

,COL_NAME(sic.OBJECT_ID,sic.column_id) PK_ColumnName

,OBJECT_NAME(sfkc.parent_object_id) FK_TableName

,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) FK_ColumnName

FROM 

INFORMATION_SCHEMA.TABLES AS T

    JOIN sys.foreign_key_columns AS sfkc ON OBJECT_NAME(sfkc.parent_object_id) = T.Table_Name

LEFT JOIN sys.index_columns sic on OBJECT_NAME(sic.OBJECT_ID) = OBJECT_NAME(sfkc.referenced_object_id) 

WHERE  

T.Table_Type = 'Base Table'  

 UNION ALL 

 SELECT      

OBJECT_NAME(sic.OBJECT_ID) PK_TableName

,COL_NAME(sic.OBJECT_ID,sic.column_id) PK_ColumnName

,NULL FK_TableName

,NULL FK_ColumnName

FROM 

INFORMATION_SCHEMA.TABLES AS T

    JOIN sys.index_columns sic ON OBJECT_NAME(sic.OBJECT_ID) =  T.Table_Name    

WHERE 

 T.Table_Type = 'Base Table')

Select 

PK_TableName

, PK_ColumnName

, FK_TableName

, FK_ColumnName

From CTE;

If you have a solid db design you can auto generate your base classes / UI / whatever from this info.  Left join this to your INFORMATION_SCHEMA.TABLE and you should see the benefits. If not send me a comment :P. 

WOOT TO BOB!!!