tsql - 在链接服务器中获取 OBJECT_ID

Question

第二个选择(从链接服务器)不返回任何值.. Object_ID 不工作。有什么解决方法吗？

select '', name 
FROM sys.databases
WHERE 1 = 1
    AND NAME <> db_name() -- exclude current database
    AND CASE 
        WHEN STATE = 0
            THEN CASE 
                WHEN OBJECT_ID(NAME + '.dbo.tPA_SysParam', 'U') IS NOT NULL
                        THEN 1
                    END
        END = 1
union
select '[LINKED]', name
FROM [LINKED].master.sys.databases
WHERE 1 = 1
    AND CASE 
        WHEN STATE = 0
            THEN CASE 
                    WHEN OBJECT_ID('[LINKED].'+NAME +     '.dbo.tPA_SysParam', 'U') IS NOT NULL
                        THEN 1
                    END
        END = 1

Answer 1

您还可以在 PARSENAME 的帮助下模仿 OBJECT_ID功能:

Declare @FullTableName nvarchar(max) = '[dbo].[MyTable]';
Select t.object_id
    From [LINKED].MyDatabase.sys.tables As t
    Inner Join [LINKED].MyDatabase.sys.schemas As s On t.schema_id = s.schema_id
    Where t.[name] = PARSENAME(@FullTableName, 1)
    And s.[name] = PARSENAME(@FullTableName, 2)