get OBJECT_ID in linked server

user_3185532 0
Viewed 3005

Second select (from linked server) does not return any values.. Object_ID doesnt work. Is any workaround? 

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
linked-server tsql
1 Answers

You can also mimic OBJECT_ID with a little help from the PARSENAME function:

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)
user_107030900
Related