Поставьте вместо MyDB любую вашу базу
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
CREATE OR ALTER VIEW vTest765 as
SELECT 'MyDB' as DatabaseName,p.Name as ReferencedTable , col2.name
as ReferencedColumn, t.Name as dd, fk.name as ff,
fk.delete_referential_action_desc, fk.update_referential_action_desc
FROM MyDB.sys.foreign_keys fk
INNER JOIN MyDB.sys.objects t ON fk.parent_object_id = t.object_id
INNER JOIN MyDB.sys.objects p ON fk.referenced_object_id = p.object_id
join MyDB.sys.foreign_key_columns fkc on fkc.parent_object_id = t.object_id
JOIN MyDB.sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = p.object_id
UNION ALL
SELECT 'msdb',p.Name , col2.name , t.Name , fk.name,
fk.delete_referential_action_desc, fk.update_referential_action_desc
FROM msdb.sys.foreign_keys fk
INNER JOIN msdb.sys.objects t ON fk.parent_object_id = t.object_id
INNER JOIN msdb.sys.objects p ON fk.referenced_object_id = p.object_id
join msdb.sys.foreign_key_columns fkc on fkc.parent_object_id = t.object_id
JOIN msdb.sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = p.object_id;
-- Далее
SELECT * FROM vTest765 WHERE DatabaseName = 'msdb';
SELECT * FROM vTest765 WHERE DatabaseName = 'msdb' OPTION (EXPAND VIEWS);
Главное отличие первого запроса от второго, что запрос с OPTION (EXPAND VIEWS) вовсе не лезет в MyDB, даже Constant Scan не делает.
