• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

Check if table exists in another sql server

I'm able to check if a table exists in the database I'm working in, but can't if I reference another database in another sql server. This is what I'm using:

IF OBJECT_ID(N'server.database.dbo.table', N'U') is not null
   Being
       stuff
   End

Anyone know who I can do this? Thanks.
0
Feyo
Asked:
Feyo
  • 3
  • 2
1 Solution
 
Anthony PerkinsCommented:
Are the servers linked?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can do like this

SELECT * FROM OPENQUERY(ServerName, 'SELECT OBJECT_ID(N''urdb..TableName'', N''U'')')


0
 
Aneesh RetnakaranDatabase AdministratorCommented:
In case u r dont have a Linked server , use this

SELECT DISTINCT  OBJECT_ID(N'urdb.dbo.tablename', N'U')
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=SERVERNAME;User ID=sa;Password=password'
         ).Northwind.dbo.Categories
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
FeyoAuthor Commented:
What if I the database name is a parameter? I tried this, but it doesn't work:

set @CheckTblExist = 'SELECT * FROM OPENQUERY(Production, ''SELECT OBJECT_ID(N''''' + @database + '.dbo.t_section_question_link'''', N''''U'''')'')'
print @checkTblExist
If (Exec(@CheckTblExist)) is null

Begin
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
DECLARE @ObjID int

set @CheckTblExist = 'SELECT @ObjID = ID FROM OPENQUERY(Production, ''SELECT OBJECT_ID(N''''' + @database + '.dbo.dbo.t_section_question_link'''', N''''U'''')AS ID'')'
print @checkTblExist
exec sp_executeSQL @CheckTblExist,N'@ObjID int OUTPUT',@ObjID Output

SELECT @ObjID
0
 
FeyoAuthor Commented:
Works well. Thanks!
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now