Reduce the human factor, auto-generate/ boiler plate / conserve your brain...
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 :
Post a Comment