find out if a table exists in a linked server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

find out if a table exists in a linked server

Author

Topic

mmcnary
Starting Member

16 Posts

Posted - 2011-07-18 : 15:52:50

I am trying to find out if a given table exists in a linked server. If the table exists, then I will perform some further processing, but the problem is finding if the table exists.

declare @srv_name varchar(100)
declare @db_name nvarchar(100)
set @db_name = 'aaaaa'
declare @table_counter int
declare c1 cursor for
select name from master.sys.servers
where name not like 'xxxxx%'
order by name
OPEN c1
fetch c1 into @srv_name
while @@fetch_status >= 0
begin
--find out if the table named 'logging' exists
--something like:
select @table_counter = 1
from @srv_name.[@dbname].[sys].[objects] o
where o.name ='logging' and o.type ='U'
--if so, then do some further processing
fetch c1 into @srv_name
END
deallocate c1

Thanx,

-Mark McNary

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-18 : 16:00:22

You will need to use dynamic SQL if you are going to use variables for the linked server name or the database name.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mmcnary
Starting Member

16 Posts

Posted - 2011-07-18 : 16:14:03

OK. I was prepared for that. I am a little new to SQL server (DB2 and Oracle) and not used to getting values into variables in t-sql. Perhaps someone can point out where I am missing the boat:

declare @srv_name varchar(100)
declare @db_name nvarchar(100)
set @db_name = 'aaaaaa'
declare @table_counter int
declare @exec_stmt nvarchar(4000)
declare @MaxLogID [int]
declare @err_num [int]
declare @error_flag char(1)
declare c1 cursor for
select name from master.sys.servers
where name not like 'bbbbb%'
order by name
OPEN c1
fetch c1 into @srv_name
while @@fetch_status >= 0
begin
--find out if the table named 'logging' exists
--something like:
set @exec_stmt ='select 1 from ['
+ @srv_name + '].['
+ @db_name + '].[sys].[objects] o
where o.name =''logging'' and o.type =''U'''
print @exec_stmt
select @table_counter = exec(@exec_stmt)
--if so, then do some further processing
fetch c1 into @srv_name
END
deallocate c1

Thanx,

-Mark McNary

mmcnary
Starting Member

16 Posts

Posted - 2011-07-19 : 09:52:58

Here's what I ended up with:

/*
This code snippet will verify the existence
of a table in a given database through a looped
linked server list
*/

declare @srv_name varchar(100)
declare @db_name nvarchar(100)
declare @table_counter int
declare @exec_stmt nvarchar(4000)
declare @error_flag char(1)
IF OBJECT_ID('tempdb..#table_count') IS NOT NULL
drop table #table_count
create table #table_count(
table_counter int
)
declare c1 cursor for
select name from master.sys.servers
where name not like 'bbbb%'
order by name
OPEN c1
fetch c1 into @srv_name
set @db_name = 'aaaa'
while @@fetch_status >= 0
begin
--find out if the table named 'logging' exists
set @exec_stmt ='insert into #table_count (table_counter) select count(*) from ['
+ @srv_name + '].['
+ @db_name + '].[sys].[objects] o
where o.name =''logging'' and o.type =''U'''
print @exec_stmt
begin try
exec( @exec_stmt)
end try
begin catch
set @table_counter = 0
end catch
select @table_counter = table_counter from #table_count
print @table_counter
truncate table #table_count
--if so, then do some further processing
if @table_counter > 0
BEGIN
set @error_flag = 'n'
END
fetch c1 into @srv_name
END
deallocate c1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources