How to check if a table exits on a different server

Hi Experts,
As a part of the stored procedure, I need to check from Server1, on which I the process will be scheduled to execute, if a given table exits on server2. Server 1 and Server 2 are linked. And to add to the complexity the table name I need to perform a lookup for is a variable.
I know, to find out if a given table exits on the same server, the syntax is this:
if exists (select * from information_schema where table_catalog = 'abc' and table_name = 'xyz')
do this..

But, I am not sure how to do the same, to check the table on a different server.

If there is no known way of doing this, is there anyway I can skip the error message. I do not wish to alarm the users.

Thanks in advance.
sarika_patalayAsked:
Who is Participating?
 
CragConnect With a Mentor Commented:
This view is database centric and you will need to specify the name of the database to get this statement to work:

select * from <server>.<database>.information_schema.tables
0
 
SQL_SERVER_DBACommented:
As a part of the stored procedure,
I need to check from Server1,
on which I the process will be scheduled to execute, if a given table exists on server2. Server 1 and Server 2 are linked. I know, to find
--If the table exists on server1 then check server2
IF EXISTS (SELECT * FROM [SERVER1].INFORMATION_SCHEMA.TABLES where table_catalog = 'abc' and table_name = 'xyz')
     BEGIN

     END
ELSE
     BEGIN

     END  
0
 
SQL_SERVER_DBACommented:
IF EXISTS (SELECT * FROM [SERVER1].INFORMATION_SCHEMA.TABLES where table_catalog = 'abc' and table_name = 'xyz')
     BEGIN

     END
ELSE
     BEGIN

     END
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
sarika_patalayAuthor Commented:
Sorry, dis not work. The syntax i used is:
SELECT * FROM [LV-SQL4].INFORMATION_SCHEMA.TABLES

i get this error:
Invalid object name 'LV-SQL4.INFORMATION_SCHEMA.TABLES'.

0
 
SQL_SERVER_DBACommented:
are you sure your using sql server 2005

select @@version
0
 
sarika_patalayAuthor Commented:
Thanks Crag,
It works !
0
All Courses

From novice to tech pro — start learning today.