How to Check if table exists on a linked server?

1.5K 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

Answer (1)

Ranjana

To check if a table exists on a linked server in SQL Server, you can use a combination of system stored procedures and dynamic SQL. Here’s a step-by-step guide on how to do this:

1. Using OPENQUERY

You can use the OPENQUERY function to execute a query on the linked server. However, this method requires you to know the database name and table name in advance.

  IF EXISTS (    SELECT 1     FROM OPENQUERY([LinkedServerName], 'SELECT * FROM [DatabaseName].[SchemaName].[TableName]'))BEGIN    PRINT 'Table exists'ENDELSEBEGIN    PRINT 'Table does not exist'END

2. Using EXECUTE AT

You can also use the EXECUTE AT command to run a query on the linked server and check for the existence of the table.

  DECLARE @sql NVARCHAR(MAX)SET @sql = N'SELECT 1              FROM sys.tables              WHERE name = ''TableName''              AND SCHEMA_NAME(schema_id) = ''SchemaName'''IF EXISTS (EXEC&#40;'EXECUTE AT [LinkedServerName] ' + @sql&#41;)BEGIN    PRINT 'Table exists'ENDELSEBEGIN    PRINT 'Table does not exist'END

3. Using sp_tables_ex

  sp_tables_ex is a system stored procedure that returns a list of objects that can be queried from a linked server.EXEC sp_tables_ex @table_server = 'LinkedServerName', @table_name = 'TableName', @table_schema = 'SchemaName'

You can capture the output of this stored procedure to check if the table exists.

4. Using Dynamic SQL

You can use dynamic SQL to build and execute a query that checks for the existence of the table.

  DECLARE @sql NVARCHAR(MAX)DECLARE @result INTSET @sql = N'SELECT @result = COUNT(*)              FROM [' + QUOTENAME('LinkedServerName') + '].[' + QUOTENAME('DatabaseName') + '].sys.tables              WHERE name = ''TableName''              AND SCHEMA_NAME(schema_id) = ''SchemaName'''EXEC sp_executesql @sql, N'@result INT OUTPUT', @result OUTPUTIF @result > 0BEGIN    PRINT 'Table exists'ENDELSEBEGIN    PRINT 'Table does not exist'ENDExample with Detailed StepsAssume you have a linked server named LinkedServer1 and you want to check if a table named Employees exists in the HR schema of the CompanyDB database.Step 1: Define the QuerysqlCopy codeDECLARE @sql NVARCHAR(MAX)DECLARE @result INTSET @sql = N'SELECT @result = COUNT(*)              FROM [' + QUOTENAME('LinkedServer1') + '].[' + QUOTENAME('CompanyDB') + '].sys.tables              WHERE name = ''Employees''              AND SCHEMA_NAME(schema_id) = ''HR'''EXEC sp_executesql @sql, N'@result INT OUTPUT', @result OUTPUTIF @result > 0BEGIN    PRINT 'Table exists'ENDELSEBEGIN    PRINT 'Table does not exist'END

This script will dynamically check if the Employees table exists in the HR schema of the CompanyDB database on the LinkedServer1 linked server.

Notes:

Permissions: Ensure you have the necessary permissions to query the linked server and the relevant databases.

Linked Server Configuration: Ensure the linked server is correctly configured and accessible.

SQL Injection: Be cautious of SQL injection when using dynamic SQL. Always sanitize inputs if they are coming from user input or external sources.

These methods should help you determine the existence of a table on a linked server in SQL Server.

5 Months