Script to trace FK between differenct DBs, is it possible.

Question

Post reply

Script to trace FK between differenct DBs, is it possible.

mario17

SSCertifiable

Points: 6078
More actions
April 22, 2014 at 10:48 am

#284801

Hi, I have this script running to list all FK relationship but it's only for single db, anybody tried to make it work for inter DBs
DECLARE @Tname varchar(40) = 'CustAccount' -- <@>< Table Name to trace
-------------------------------------------------
SELECT DISTINCT No ,
ist1.table_catalog AS [Parent DB],
ist1.table_schema AS ParentSchema,
ParentTable ,
ReferencedColumnName ,
ConstraintName ,
link,
ForeignTable,
ForeignKeyColumn ,
ist1.table_schema AS FKSchema,
[Action on Update] ,
[Action on Delete]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(fk.referenced_object_id), clm1.name) as 'No',
OBJECT_NAME(fk.referenced_object_id) as ParentTable,
clm2.nameas ReferencedColumnName,
OBJECT_NAME(constraint_object_id)as ConstraintName,
OBJECT_NAME(fk.parent_object_id)as ForeignTable,
clm1.nameas ForeignKeyColumn,
CASE WHEN OBJECT_NAME(fk.referenced_object_id) = @Tname THEN '<<====='
ELSE '=====>>' end ,
SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.parent_object_id,N'SchemaId') AS bit)) as [ForeignSchema],
[Action on Update] = CONVERT(varchar,CASE OBJECTPROPERTY(constraint_object_id,'CnstIsUpdateCascade') WHEN 1 THEN 'CASCADE' ELSE 'NO_ACTION' END),
[Action on Delete] = CONVERT(varchar,CASE OBJECTPROPERTY(constraint_object_id,'CnstIsDeleteCascade') WHEN 1 THEN 'CASCADE' ELSE 'NO_ACTION' END)
FROM sys.foreign_key_columns fk
JOIN sys.columns clm1 ON fk.parent_column_id = clm1.column_id
AND fk.parent_object_id = clm1.object_id
JOIN sys.columns clm2 ON fk.referenced_column_id = clm2.column_id
AND fk.referenced_object_id= clm2.object_id
WHERE
OBJECT_NAME(fk.referenced_object_id) = @Tname OR --- table name which is being referenced by other tables via Foreign Keys
OBJECT_NAME(fk.parent_object_id) = @Tname --- table is referenced
-- ORDER BY OBJECT_NAME(fk.referenced_object_id)
) b
left JOIN INFORMATION_SCHEMA.TABLES ist1 ON ist1.table_name = b.ParentTable

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You could build a dynamic query and use sp_msforeachdb as described here[/url].

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mario17 SSCertifiable Points: 6078 More actions · Answer 2

Yes, as I have only 2 active db, I finished with union for 2 of them.

Thanks

M