sql-server - 通过链接服务器识别 SQL Server 数据库和 Oracle 之间的对象依赖关系？

Question

我正在使用 SQL Server 数据库引擎，它有一个包含大约 2,000 个视图的数据库。这些视图中的大多数使用 OpenQuery 语句通过链接服务器从 Oracle 数据库中选择数据。

我有一个新要求来识别 SQL Server 视图和 OpenQuery 语句中引用的 Oracle 对象之间的依赖关系。这不会是一次性的要求，所以我需要能够偶尔刷新它。Oracle 中的对象经常更改，SQL Server 中也会定期添加或修改新视图。

一些视图（大约 0.5%）的结构如下（没有 OpenQuery）：

CREATE VIEW [dbo].[SampleViewType1] AS

SELECT  ColX,
        ColY
FROM OracleLinkedServerName..OracleSchemaName.SampleOracleTable1

对于这些视图，我偶然发现了一个有用的查询，它可以获取视图和它引用的 Oracle 对象。

SELECT 
    Distinct 
    referenced_Server_name As LinkedServerName,
    referenced_schema_name AS LinkedServerSchema,
    referenced_database_name AS LinkedServerDB,
    referenced_entity_name As LinkedServerTable,
    OBJECT_NAME (referencing_id) AS ObjectUsingLinkedServer
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
And referenced_Server_name = 'OracleLinkedServerName'

它可以给我如下结果：

sys.sql_expression_dependencies 结果

但是，大多数视图都是按照下面的示例构建的，并使用 OpenQuery。不幸的是，sys.sql_expression_dependencies没有拾取这些类型的引用。

CREATE VIEW [dbo].[SampleViewType2] As

SELECT 
    ColA,
    ColB
FROM OPENQUERY 
(
    OracleLinkedServerName, 
    'SELECT *
    FROM SampleOracleTable2
    WHERE TestDate BETWEEN TRUNC(SYSDATE, ''month'') AND TRUNC(SYSDATE)'
);

有人对我如何获取这些信息有任何建议吗？目前，我不确定这是否可能。我愿意使用 SQL 之外的其他技术，例如 C#、VB 等。

标签： sql-servertsqlsearchlinked-serveropenquery

Answer 1

您可以获取视图定义并检查文本是否具有名称“OracleLinkedServerName”。请参考以下查询

SELECT *
FROM (
    SELECT OBJECT_DEFINITION(OBJECT_ID(CONCAT (
                    ss.name
                    ,'.'
                    ,so.name
                    ))) [Definition]
        ,so.name
    FROM sys.objects so
    JOIN sys.schemas ss ON so.schema_id = ss.schema_id
    WHERE type = 'V'
    ) a
WHERE a.DEFINITION LIKE '%OracleLinkedServerName%'

查询以获取视图定义中使用的所有对象

DECLARE @objects TABLE (name NVARCHAR(max))

INSERT INTO @objects
SELECT so.name
FROM sys.objects so

-- You have to do this manually for all the oracle object
-- copy all your objects in excel and use excel funtions to form this
-- Remember last select should not have UNION All
INSERT INTO @objects
SELECT 'Oracle object 1'

UNION ALL

SELECT 'Oracle object 2'

SELECT *
FROM @objects so
INNER JOIN (
    SELECT *
    FROM (
        SELECT OBJECT_DEFINITION(OBJECT_ID(CONCAT (
                        ss.name
                        ,'.'
                        ,so.name
                        ))) [Definition]
            ,so.name
        FROM sys.objects so
        JOIN sys.schemas ss ON so.schema_id = ss.schema_id
        WHERE type = 'V'
        ) a
    WHERE a.DEFINITION LIKE '%OracleLinkedServerName%'
    ) a ON a.DEFINITION LIKE '%' + so.name + '%'