4

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!

Md Haidar Ali Khan
  • 5,967
  • 9
  • 34
  • 59
Jon
  • 143
  • 1
  • 3
  • 1
    There is an answer on SO: https://stackoverflow.com/a/22305213/3270427 – McNets May 24 '18 at 09:55
  • Thank you; I hadn't seen that. I've gone for the below as I could get it to work, and it looked simpler. – Jon May 24 '18 at 12:51

1 Answers1

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'
MJH
  • 166
  • 1
  • 2
  • 12
  • 2
    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. – Tibor Karaszi May 24 '18 at 10:59
  • @Tibor Karaszi Thanks, I have updated my answer. – MJH May 24 '18 at 11:12
  • 1
    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. – Jon May 24 '18 at 12:48
  • 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. – it3xl Aug 08 '20 at 22:29