Check if table exists on a linked server

Question

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?

Thanks!

• McNets: There is an answer on SO: stackoverflow.com/a/22305213/3270427
• Jon: Thank you; I hadn't seen that. I've gone for the below as I could get it to work, and it looked simpler.

Answer 1

You can query the Information_Schema views on the linked server:

if exists(
 select *
 from [Linked_Server_Name].[Database_Name].INFORMATION_SCHEMA.TABLES
 where table_name = 'Table_Name'
 and table_schema = 'Table_Schema'
)
print 'Table Found'

• Tibor Karaszi: Note that information_schema.tables should be uppercase in case the linked server is a case-sensitive SQL Server. Personally, if I know it is a SQL Server at the other end, then I use sys.tables instead of the information schema views.
• MJH: @Tibor Karaszi Thanks, I have updated my answer.
• Jon: Thank you. As it was also going through dynamic SQL (to name tables) I had to two-stage the query but this works perfectly, and is simple to read.
• it3xl: The solution doesn't work if there is no table and we have a SELECT * FROM My_LinkedServer.my_db.dbo.my_table query inside the IF BEGIN ... END. We get an error The OLE DB provider "SQLNCLI11" for linked server "My_LinkedServer" does not contain the table ""my_db"."dbo"."my_table"". Dynamic SQL is a workaround.