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


call, however this does not work when I reference the LINKED server. e.g.


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?


  • 1
    There is an answer on SO: stackoverflow.com/a/22305213/3270427
    – McNets
    May 24, 2018 at 9: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, 2018 at 12:51

1 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'
  • 3
    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. May 24, 2018 at 10:59
  • @Tibor Karaszi Thanks, I have updated my answer.
    – MJH
    May 24, 2018 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, 2018 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 8, 2020 at 22:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.