April 5, 2011 at 1:24 pm
How can i re-write this query without using a UNION? I'm looking for all columns in the FKs and if those columns are also FK'd, get the table and column on the next level down.
SELECT OBJECT_NAME(f.referenced_object_id)
, COL_NAME(fc.parent_object_id, fc.parent_column_id)
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
WHERE f.parent_object_id = OBJECT_ID(@TABLENAME)
AND COL_NAME(fc.parent_object_id, fc.parent_column_id) = @COLUMNNAME
UNION
SELECT OBJECT_NAME(fd.referenced_object_id)
, COL_NAME(fd.parent_object_id, fd.parent_column_id)
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
INNER JOIN sys.foreign_key_columns AS fd
ON fc.referenced_object_id = fd.parent_object_id
and fc.referenced_column_id = fd.parent_column_id
where f.parent_object_id = OBJECT_ID(@TABLENAME)
AND COL_NAME(fc.parent_object_id, fc.parent_column_id) = @COLUMNNAME
April 5, 2011 at 1:29 pm
If you are interested, I have written a script that produces the FK Hierarchy in the given database.
You can find it here.
http://jasonbrimhall.info/2010/02/01/key-discovery-iii/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply