How to Check if table exists on a linked server?

1.1K    Asked by CameronOliver in Salesforce , Asked on Aug 28, 2021

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!

Answered by Celina Lagunas

You can query the Information_Schema views on the linked server to get rid of sql server check if table exists:

  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'

Hope this helps!



Your Answer

Interviews

Parent Categories