How to find databases having a certain table on a linked server (SQL Server 2012)?

Question

I need to find all databases on a linked server having the same value inside a certain table like the database on the source server which calls the query.

I've build a query which works fine as long as all databases on the linked server have this table. Otherwise it fails telling me:

The OLE DB provider "SQLNCLI11" for linked server "MyServer" does not contain the table ""DbName"."dbo"."TableName"". The table either does not exist or the current user does not have permissions on that table.

This is my code:

CREATE TABLE #x(DB SYSNAME, name varchar(255));

DECLARE @guid uniqueidentifier;
SET @guid = (SELECT guid FROM TableName);

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + 'IF EXISTS (SELECT 1 FROM [LinkedServer].'
+ QUOTENAME(name) + '.sys.tables WHERE name = ''TableName'')
INSERT #x SELECT ''' + name + ''', name
    FROM [LinkedServer].' + QUOTENAME(name) + '.dbo.TableName
    WHERE guid = ''' + CONVERT(NVARCHAR(36), @guid) + ''';'
FROM [LinkedServer].master.sys.databases;

EXEC sp_executesql @sql;

SELECT * FROM #x;
DROP TABLE #x;

Is there a way to solve this?

IVNSTN · Accepted Answer · 2016-02-29 17:13:38Z

1

since you don't know if the table exists in specific database at compile time, you have to use dynamic sql:

SELECT @sql += '
  IF EXISTS (SELECT 1 FROM [LinkedServer].' + QUOTENAME(name) + '.sys.tables WHERE name = ''TableName'')
exec (''INSERT #x SELECT ''''' + name + ''''', name
    FROM [LinkedServer].' + QUOTENAME(name) + '.dbo.TableName
    WHERE guid = ''''' + CONVERT(NVARCHAR(36), @guid) + ''''''');'
FROM [LinkedServer].master.sys.databases;
where database_id > 4

so you have dynamic inside of dynamic.

edited Feb 29, 2016 at 17:13

answered Feb 29, 2016 at 15:35

IVNSTN

9,3896 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Field Over a year ago

What happens if the linked server doesn't have a master DB (i.e. isn't a SQL Server)?

Wow Over a year ago

This is no problem in my case - it's always a SQL Server with a master DB. But @Ivan, did you test your solution? I tried to adapt this but I'm getting an error: "Incorrect syntax near 'TableName'."

IVNSTN Over a year ago

@vso, check quotes. print @sql for debug.

Collectives™ on Stack Overflow

How to find databases having a certain table on a linked server (SQL Server 2012)?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related