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!!!

Comments :

0 comments to “Get you red hot Foreign and primary keys here!!!”