I've had a search, but can't find a post relating to this instance specifically.
We have a linked production database that I'm trying to reference from our 'sandbox' server. I want to check if a table exists on the LINKED production server. For any given database/table combination on the server from which I am executing the SQL I would use an
IF OBJECT_ID(etc) IS NULL etc.
call, however this does not work when I reference the LINKED server. e.g.
IF OBJECT_ID('ZPRODSERVER.DM_Database.dbo.MyTable', 'U') IS NULL PRINT 'YES' ELSE PRINT 'NO'
returns "YES", even though I know this table exists, as when I select top 1 * from it I get table results. If I use:
IF EXISTS(select top 1 * from ZPRODSERVER.DM_Database.dbo.MyTable) PRINT 'YES' ELSE PRINT 'NO'
then I get "
YES" returned, HOWEVER if the table doesn't exist, I don't get NO and instead I get an error message:
Msg 7314, Level 16, State 1, Line 90 The OLE DB provider "SQLNCLI11" for linked server "ZPRODSERVER" does not contain the table ""DM_Database"."dbo"."MyTable"". The table either does not exist or the current user does not have permissions on that table.
Is there a consistent method that I can use to determine if a table on a different server exists without incorrect results or an error message?