CODEWITHSUNDEEP
sql-serverdatabase
Wow
Wow

Reputation: 305

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

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?

Upvotes: 1

Views: 658

Answers (1)

IVNSTN
IVNSTN

Reputation: 9325

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.

Upvotes: 1

Related Questions