i服务器我有三个具有以下结构的数据库:
databasea
- sp_a01 =>是一个存储过程,定义为
INSERT INTO T_A01([Id]) SELECT [Id] FROM [dbo].[T_A04]
- sp_a02 =>是一个存储过程,定义为
INSERT INTO T_A01([Id]) SELECT [Id] FROM [DatabaseB].[dbo].[T_B02]
- sp_a03 =>是一个定义为
的存储过程INSERT INTO T_A01([Id]) SELECT [Id] FROM [DatabaseC].[dbo].[T_C02]
- T_A01 =>是表
- T_A02 =>是表
- T_A03 =>是表
- T_A04 =>是表
databaseb
- T_B01 =>是表
- T_B02 =>是表
databasec
- T_C01 =>是表
- T_C02 =>是表
- T_C03 =>是表
我想进行一个查询,该查询找到所有指向与数据库不同的数据库的数据库。在上面的示例中,查询应给出以下结果:
- sp_a02
- sp_a03
因为他们指向databaseb和databasec。
我尝试了
我有以下查询来查找数据库中的所有SP
select * from DatabaseA.information_schema.routines where routine_type = 'PROCEDURE'
您可以使用
SELECT OBJECT_SCHEMA_NAME(referencing_id),
OBJECT_NAME(referencing_id),
referenced_database_name,
referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name <> DB_NAME() /*Not NULL and not current DB*/
AND OBJECTPROPERTY(referencing_id, 'IsProcedure') = 1
创建以下proc
之后CREATE PROC [dbo].[P1]
AS
EXEC Foobar.dbo.DFG
SELECT [Id]
FROM [DatabaseB].[dbo].[T_B02]
它返回
+------------------+------------------+--------------------------+------------------------+
| (No column name) | (No column name) | referenced_database_name | referenced_entity_name |
+------------------+------------------+--------------------------+------------------------+
| dbo | P1 | Foobar | DFG |
| dbo | P1 | DatabaseB | T_B02 |
+------------------+------------------+--------------------------+------------------------+